4

In SSMS, when connecting to any of our Azure SQL Databases using service accounts and SQL server authentication, the connecting itself can take between 10 to 15 seconds to establish.

Once connected, right click on any table and click on "select top 1000 rows" this also takes between 10 and 15 seconds to respond and present the rows in SSMS.

None of this delay/lag occurs when using "Active Directory - Universal with MFA Supprt" authentication - however, this isn't using the same service accounts.

How can the SQL Server Auth lag be resolved/investigated?

3
  • @SqlRyan - dropping and recreating the SQL user associated with the service account resolved it for us
    – Geezer
    Sep 30 '19 at 8:40
  • @Geezer, we're getting the exact opposite. SSMS keeps locking up when I'm using "Active Directory - Universal with MFA Supprt" but its fine with SQL authentication. ?
    – PTansey
    Jan 13 '20 at 23:46
  • This is an issue for us as well. It happens in SSMS for sql authentication users. But it doesn't happen for AD/MFA. One new thing I discovered, however, is that if I connect with Azure Data Studio then it is fast for both types of users. I don't particularly like Azure Data Studio but I thought this may be a clue of some kind. (Perhaps the SSMS folks can compare their connection code to the stuff in ADS. I suspect ADS uses a shortcut or two.) Jun 13 '20 at 21:26
1

Here is the Microsoft explanation of this issue:

https://techcommunity.microsoft.com/t5/azure-database-support-blog/lesson-learned-132-delays-connecting-to-azure-sql-database-from/ba-p/1502030

They describe two types of authenticated users. Ones with instance LOGIN's and ones that have only access to a single database ("contained" database users).

The problem with the slow SSMS connectivity only affects the first type of authentication (instance LOGIN's when they have access to a database but have no rights to the "master" database). If you are able to switch to "contained" database users then you may avoid the problem altogether.

1
  • David, you're probably better off editing your original answer since this is more of an update as opposed to a different answer. Jul 1 '20 at 19:37
0

I think I figured it out. SSMS appears to be trying to interact with the "master" database, even if that isn't the database which is being connected. I suspect it is quietly failing/retrying - and the final result is the slowness that we experience (... even though no error messages are actually being reported to us).

There are official docs that explain how to change the selected database when connecting via SSMS. You can do this in order to connect to something other than "master". See here: https://docs.microsoft.com/en-us/azure/azure-sql/database/troubleshoot-common-errors-issues

While the changing of the selected database does technically fix the connection problem, the slowness will remain. It appears to be related to the the fact that your database user still doesn't have full-fledged access to the "master" database .

To fix: Connect to "master" database with sql server administrator. Then run the following.

CREATE USER [sql-problem-user]
    FOR LOGIN [sql-problem-user]
    WITH DEFAULT_SCHEMA = dbo
GO

After doing this, your sql-problem-user will be able to connect more quickly to the original database that you wanted to connect to in the first place. (It will now be able to connect to "master" as well). Hope this is clear. Once I discovered this, I was able to find others that had used this approach long ago as well. See https://stackoverflow.com/questions/29561876/azure-sql-database-slow

I'm using SSMS v18.5 (15.0.18330.0).

I had also noticed that Azure Data Studio never experienced this connection delay. So maybe some folks can think about migrating over to that tool instead. I suspect that is the tool that Microsoft believes will replace SSMS.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.