locked
Kerberos and Native Windows connection RRS feed

  • Question

  • Hi,
    I've got a question about connect to sql
    My applicayion uses kerberos for the connection
    but i also want to make a native windows connection to sql with the aplication pool identity account (domain account), just for a specific function
    My Connection string
    <add name="MyDBConnectionString" connectionString="Data Source=XXXXXXX;Initial Catalog=XXXXXX;Trusted_Connection=SSPI" providerName="System.Data.SqlClient" />
    <add name="MyDBConnectionString2" connectionString="Data Source=XXXXXXX;Initial Catalog=XXXXXX;Trusted_Connection=SSPI" providerName="System.Data.SqlClient" />
    <authentication mode="Windows" />
    <identity impersonate="true" />
    SPN's are set
    Kerberos works fine
    but how to set MyDBConnectionString2 so it will use native authentication instead of kerberos
    It's no option to set  MyDBConnectionString2  as sql connection, it's no option to set a password in connection

    thanks

    Wednesday, June 3, 2020 9:32 AM

All replies

  • Hi,

    1. Set AllowTgtSessionKey to 1 in the registry for Windows. For more information, see Kerberos protocol registry entries and KDC configuration keys in Windows Server 2003.
    2. Make sure that the Kerberos configuration (krb5.conf in UNIX environments), points to the correct realm and KDC for your environment.
    3. Initialize the TGT cache by using kinit or logging into the domain.
    4. When an application that uses authenticationScheme=JavaKerberos runs on the Windows  operating systems, you should use a standard user account. However if you run the application under an administrator's account, the application must run with administrator privileges.


    SD

    Wednesday, June 3, 2020 9:49 AM
  • There are 2 Windows authentication, old NTLM (what you call "native") and Kerberos; the system decides which to use for SQL Server connections.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, June 3, 2020 10:41 AM
  • Olaf, so once Kerberos is configured, you cannot use ntlm any more ?

    Wednesday, June 3, 2020 10:55 AM
  • Hi firend,

    There are two Windows authentication methods for domain accounts, NTLM and Kerberos.

    For SQL Server 2008 and above, NTLM will be used when a local client using domain account to connect to the local SQL Server. If the correct SPN is successfully registered, NTLM is used for local connections and Kerberos is used for remote connections. When the SPN of SQL Server is not found, the connection will use NTLM. When there is an incorrect SPN in the domain, authentication fails.

    Run the following command to check the authentication method used for the current connection:

    select auth_scheme from sys.dm_exec_connections where session_id=@@spid
    
    ----execute below query to check client program name, client login time, login user and more
    
    select * from sys.dm_exec_sessions where session_id=@@spid


    Best Regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, June 4, 2020 3:17 AM
  • I know how to test my connection, the question is, if everything is correct configured Kerberos works, but for 1 query in my asp I want to fallback to ntlm connection instead of Kerberos , so the query is performed by the account defined in application pool identity.
    In web.config

    My Connection string
    < add name="MyDBConnectionString" connectionString="Data Source=server1;Initial Catalog=database1;Trusted_Connection=SSPI" providerName="System.Data.SqlClient" />
    < add name="MyDBConnectionString2" connectionString="Data Source=server1;Initial Catalog=database1;Trusted_Connection=SSPI" providerName="System.Data.SqlClient" />
    < authentication mode="Windows" />
    < identity impersonate="true" />

    I want to change the second connection string, but don't know how to
    tried server spn=falsespn  but this wasn't the solution
    I believe in jdbc you can add something like setAuthenticationScheme NTLM


    Friday, June 5, 2020 1:50 PM
  • Hi friend,

    Use a domain account to connect to SQL Server. You cannot specify to use NTLM authentication in the connection string.

    The client and SQL Server use negotiation(SPNEGO) to finally decide whether to use Kerberos or NTLM. Neither the client nor the SQL Server can decide which method to use.

    When the SPN is configured correctly, Kerberos is always the preferred authentication method. 

    Best Regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 8, 2020 1:47 AM
  • Hi friend,

    I am writing to follow up this thread with you. Is there any update on this case?Was your issue resolved?

    If you have resolved your issue, please mark the useful reply as answer. This can be beneficial to other community members reading the thread.

    In addition, if you have another questions, please feel free to ask.
    Thanks for your contribution.

    Best regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, June 10, 2020 1:19 AM
  • Hi friend,

    Is there any update on this case?
    Please feel free to drop us a note if there is any update.
    Have a nice day!

    Best Regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 15, 2020 1:31 AM