Lesson Learned #132: Delays connecting to Azure SQL Database from SQL Server Management Studio.

by | Jul 1, 2020 | Azure, Microsoft, Technology, Uncategorized | 0 comments

This article is contributed. See the original author and article here.

Today, I worked on a service request that our customer reported some delays when they are connecting to Azure SQL Database using SQL Server Management Studio. In this article I would like to share with you an explanation about it.

 

We know that in Azure SQL Database we have two different user types: Login and Contained Users and following I would like to explain what is the impact using both of them connecting to Azure SQL Database from SQL Server Management Studio. 

 

Before doing anything, I’m going to enable SQL Auditing that allow us to understand the different delays and what is hapenning behind the scenes when we are connecting using SQL SERVER Management Studio:

 

1) Login:

 

  • I created a login using the following TSQL command: CREATE LOGIN LoginExample with Password=’Password123%’
  • I’m going to give the permissions to a specific database that I created, let’s give the name DotNetExample. 
  • Connected to this database I run the following TSQL command to create the user and provide the permission. 
    • CREATE USER LoginExample FOR LOGIN LoginExample
    • exec sp_addrolemember ‘db_owner’,’LoginExample’
  • Using SQL Server Management Studio with the this user and specify the database in the connection string, the login process took around 15/20 seconds. Why?
    • In order to explain it, let’s try to review the SQL Auditing file and see what is happening. 
    • All points to a normal login time and connection if we review the SQL Auditing of the user database. 
  • But, what is happening in the master database?.
    • In this situation, I saw many DATABASE AUTHENTITICATION FAILED, several times, with this error message: additional_information <login_information><error_code>18456</error_code><error_state>38</error_state></login_information> “Cannot open a specified database master”
    • Why? because as we defined as a Login, SQL Server Management Studio is trying to obtain information, most probably, about the databases list, information of the server, … and in every retry is waiting some seconds.
 

Capture.PNG

 

2) Contained User:

 

  • I created an user the following TSQL command: CREATE USER LoginExampleC with Password=’Password123%’ and gave the db_owner permissions. 
  • But, Is the connection time the same?…In this situation, not, because as there is a contained user of this database, there is not needed to review any parameter of master database. 

 

So, based on this situation and as Azure SQL Database is oriented to Database engine the best approach to reduce this time is to use a Contained User. However, if you want to reduce the time using login, you could do the following under the master database, CREATE USER LoginExample FOR LOGIN LoginExample, to allow the permission to connect to this master database.

 

Enjoy!

 

 

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.

Submit a Comment