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?
Best Answer
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.