Speed plummets after joining SQL Server Express host machine to domain

Auntiejack 201 Reputation points
2020-12-31T06:52:00.11+00:00

Hi,

I've added a new VM to my network, installed SQL Server Express on it, loaded a database, opened 1433 and connected an app to it from another machine on the network.
So far, so easy. The app is a Microsoft Access app, and runs fast when I'm logged in with my admin account.

Next step is to add windows authenticated users to the SQL Express database, so to do that I have to add the new VM to the domain. No problem there either, new entry appears in the DNS, bobs your uncle. And I create my new user in AD, and add him to SQL Server express user list.

Then I log back in as my new user, and open the app. Speed plummets: instead of 2 seconds to open the edit page, now it's 60-75 seconds to open the same page.

Try as I might, I couldn't fix this, so I did the whole process again - new machine, install SQL Express, load database, open the port, connect across network with Access app using admin account, and all transactions are fast.

Join new machine to domain, add new user, connect across network with Access app using new user account, transactions are almost to slow for the human eye to follow. Everything works ok, but something is slowing it all down. I setup an Audit log on SQL Express - 5 successful selects (only 1 of which had more than a few records), and no other traffic that I could see.

Is AD doing a whole lot of searching for something, or is the SQL driver (ODBC Driver 17 for SQL Server) incompatible - anybody have any ideas? Oh yes, and happy new year ;-)

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,021 questions
{count} votes

Accepted answer
  1. Auntiejack 201 Reputation points
    2020-12-31T23:57:50.507+00:00

    Hi and thanks for your reply and Happy New Year!

    With 10 minutes of the old year left last night, I had an inspiration. This is it, and herein lay the solution which I tested this morning (and as you say, it is with the connection):

    To do the initial database setup when the database host machine is not joined to the domain, the only way to get the connection to work is to directly address the server in the connection string (in this case DATABASE=192.168.0.4\SQLEXPRESS,1433).

    After the database host is added to the domain, the direct address won't be accepted as a valid connection to the database host machine, but it doesn't throw an error - I don't know how this piece works exactly, but I suppose that the AD has to be consulted by the database host machine to see if the requester has permission to connect. For some reason, this is time-consuming - it takes about 8-10 seconds.

    The trick is to change the connection string to DATABASE=[MachineName]\SQLEXPRESS,1433. This must force the requesting machine to do a DNS lookup which I'm guessing also validates the user in AD. So the connection is instant - the machine hosting the database no longer seems to do anything complicated to accept the connection.

    So as I originally noted, there only seems to be 5 SELECT requests, but if each is taking 8-10 seconds before SQLExpress even sees them, then that matches fairly closely with the delay experienced.

    Testing this change to the connection resulted in the new user having the same response time as the original administrator. That is, < 2 seconds!!

    If you can add anything to the above, particularly around my guesswork, please do so.

    Thanks again,


1 additional answer

Sort by: Most helpful
  1. Auntiejack 201 Reputation points
    2021-01-01T10:23:08.613+00:00

    Hi Amelia,

    I've accepted my answer, but while it describes 'how to fix' the problem, the 'why it happens' is missing. I feel that an expert in AD and networking could give a much better answer. Hopefully someone will add some more information on what is really going on.

    Thanks,

    Jack

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.