Slow performance using Windows Authentication on SQL Server
Question
Monday, February 18, 2019 7:56 AM
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.
All replies (6)
Tuesday, March 26, 2019 7:58 AM âś…Answered
Thanks for your solution Vivek!
It worked when I included ipaddress in the hostsfile.
Tuesday, February 19, 2019 1:43 AM
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
Tuesday, February 26, 2019 6:49 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?
Tuesday, February 26, 2019 10:28 PM
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
Tuesday, March 26, 2019 11:11 PM
Glad it worked Asthan.
Regards;
Vivek Janakiraman
Thursday, January 16, 2020 3:20 PM
I had same issue and I tried including IP address in the hosts file and it worked. Thanks !