locked
SQL Server Window Authentication Slow RRS feed

  • Question

  • Hi

       We are using SQL Server 2012. When we run our applications with window authentication it runs really slow. But if we use same application with database user authentication then it runs really fast.

        Any Suggestion?

    thanks


    Drew

    Tuesday, February 4, 2014 6:01 PM

Answers

  • Also for Kerberos it's the client that connects to the domain controller, not SQL Server.  So connecting to the SQL Server using an IP address and integrated security instead of a host name, which will prevent Kerberos authentication.

    If NTLM is slow, troubleshoot the SQL Server's communication to the DC.  If Kerberos is slow, troubleshoot the client's connection to the DC.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by drew_p Thursday, February 6, 2014 10:14 PM
    Tuesday, February 4, 2014 6:58 PM
  • Hi Drew,

    When you use Windows Authentication I assume your are using domain accounts (no local account). Your database server has probably some issue connecting to Active Directory. You might have similar delay logging on to the server using a domain account and or rebooting the server might also be slow. Most likely your DNS server settings on that server or on your client are incorrect or out of date.

    You can check this from the Command Prompt with the IPCONFIG.EXE /ALL command. Make a note of configured DNS servers. The configured DNS servers should ONLY be the DNS servers from the Active Directory domain (typically those are the same servers as the domain controllers). You should remove any DNS servers from the Internet.

    Why does this matter? You may ask. SQL Server needs to connect to the domain controller to verify your credentials. to that it first needs to know the IP address of the domain controller. This information is stored on a DNS server in your Active Directory domain, not on public DNS servers on the Internet. If your server is configured with public DNS servers from the Internet, requesting this information will fail and cause an delay until either a correct DNS server is contacted or the domain controller is contacted using legacy (NTLM) methods.

    You may have a more complicated situation if more Active Directory domains are involved (e.g. domain trusts, forest trusts, complex nested group memberships.), or if the configured Domain Controller is located at another site and is only connected by a slow link, or there is actually a performance issue with your domain controllers. In that case your Active Directory Administrator may supply with more information.

    In short: check your DNS server settings.

    Hope this helps.

    • Marked as answer by drew_p Thursday, February 6, 2014 10:14 PM
    Tuesday, February 4, 2014 6:48 PM

All replies

  • Have a look at this link please:

    Performance Issues Caused By SQL Server Windows Authentication


    sqldevelop.wordpress.com

    Tuesday, February 4, 2014 6:38 PM
  • this should be made on client machine from where application is connecting or on the machine where sql server running

    thanks


    Drew

    Tuesday, February 4, 2014 6:44 PM
  • Hi Drew,

    When you use Windows Authentication I assume your are using domain accounts (no local account). Your database server has probably some issue connecting to Active Directory. You might have similar delay logging on to the server using a domain account and or rebooting the server might also be slow. Most likely your DNS server settings on that server or on your client are incorrect or out of date.

    You can check this from the Command Prompt with the IPCONFIG.EXE /ALL command. Make a note of configured DNS servers. The configured DNS servers should ONLY be the DNS servers from the Active Directory domain (typically those are the same servers as the domain controllers). You should remove any DNS servers from the Internet.

    Why does this matter? You may ask. SQL Server needs to connect to the domain controller to verify your credentials. to that it first needs to know the IP address of the domain controller. This information is stored on a DNS server in your Active Directory domain, not on public DNS servers on the Internet. If your server is configured with public DNS servers from the Internet, requesting this information will fail and cause an delay until either a correct DNS server is contacted or the domain controller is contacted using legacy (NTLM) methods.

    You may have a more complicated situation if more Active Directory domains are involved (e.g. domain trusts, forest trusts, complex nested group memberships.), or if the configured Domain Controller is located at another site and is only connected by a slow link, or there is actually a performance issue with your domain controllers. In that case your Active Directory Administrator may supply with more information.

    In short: check your DNS server settings.

    Hope this helps.

    • Marked as answer by drew_p Thursday, February 6, 2014 10:14 PM
    Tuesday, February 4, 2014 6:48 PM
  • also link suggested by Saeid Hasani is for Commerce Server. We are using desktop application

    thanks


    Drew

    Tuesday, February 4, 2014 6:48 PM
  • Also for Kerberos it's the client that connects to the domain controller, not SQL Server.  So connecting to the SQL Server using an IP address and integrated security instead of a host name, which will prevent Kerberos authentication.

    If NTLM is slow, troubleshoot the SQL Server's communication to the DC.  If Kerberos is slow, troubleshoot the client's connection to the DC.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by drew_p Thursday, February 6, 2014 10:14 PM
    Tuesday, February 4, 2014 6:58 PM