locked
Slow performance using Windows Authentication on SQL Server RRS feed

  • Question

  • I am trying to connect to Database on remote server (connected to different network) via SSMS using client VPN and windows authentication and noticed that each query is taking really long time as it authenticates domain login for each transaction.
    However, this delay is not noticed when I use SQL server authentication.
    Can anyone please let me know how could I reduce this delay for windows authentication.
    Monday, February 18, 2019 7:56 AM

Answers

  • Thanks for your solution Vivek!

    It worked when I included ipaddress in the hostsfile.

    • Marked as answer by asthajn1009 Tuesday, March 26, 2019 7:58 AM
    Tuesday, March 26, 2019 7:58 AM

All replies

  • Hello Asthanj,

    SQL Server will basically perform validations with Domain controller to ensure the validity of the login. Possible reasons for your issue below,

    1) Network connectivity between your server and DC.
    2) Performance Issues in DC server.
    3) Try creating an entry in your host file and check further. Host file on remote server should be updated with your DB server hostname and Ipaddress. Remove it if there are no benefits.
    3) Ensure proper SPN's are created and connection uses Kerberos auth.


    Regards;
    Vivek Janakiraman
    ----------------------------
    My Blog
    My Linkedin Page
    ----------------------------

    • Proposed as answer by Teige Gao Tuesday, February 19, 2019 5:40 AM
    Tuesday, February 19, 2019 1:43 AM
  • Hi,

    Thanks for your answer. But I do not have permission to modify or check anything on domain server. Also, to be more precise, we have 2 network domains here. One on which there is DB Server. Other we have App Server. Both domain connected through AD. So, do you have any idea how could I check to which DC it is authenticating?

    Or is is possible that it authenticates only single time while login and not on each line of query?


    • Edited by asthajn1009 Tuesday, February 26, 2019 6:50 AM
    Tuesday, February 26, 2019 6:49 AM
  • You can get that details from your login with which you are connecting to SQL Server. Your login should be like <Domain>\<Login>. Speak to your team which support DC and they will be able to provide help.

    1) Network connectivity between your server and DC.
    2) Performance Issues in DC server.

    4) Ensure proper SPN's are created and connection uses Kerberos auth.

    Above 3 things can be worked upon by your DC, Network/WINTEL team.

    3) Try creating an entry in your host file and check further. Host file on remote server should be updated with your DB server hostname and Ipaddress. Remove it if there are no benefits.

    Above things can be done by you , if you have access to your app server.


    Regards;
    Vivek Janakiraman
    ----------------------------
    My Blog
    My Linkedin Page
    ----------------------------

    Tuesday, February 26, 2019 10:28 PM
  • Thanks for your solution Vivek!

    It worked when I included ipaddress in the hostsfile.

    • Marked as answer by asthajn1009 Tuesday, March 26, 2019 7:58 AM
    Tuesday, March 26, 2019 7:58 AM
  • Glad it worked Asthan.

    Regards;
    Vivek Janakiraman
    ----------------------------
    My Blog
    My Linkedin Page
    ----------------------------

    Tuesday, March 26, 2019 11:11 PM
  • I had same issue and I tried including IP address in the hosts file and it worked. Thanks !

    Thursday, January 16, 2020 3:20 PM