This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(70.4, 78%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EDB%3C/text%3E%3C/svg%3E)
Is SSMS supposed to be fully supported as a tool for managing an Azure SQL Database? Or does Microsoft expect us all to start using Azure Data Studio?
I'm using SSMS v18.5 (15.0.18330.0).
I had noticed that SSMS can take about 20 or 30 seconds to an Azure SQL database. After digging into this for far too long, I discovered that the underlying problem is fairly silly. It appears that the user who is connecting to a given database "MyAbc" needs to also have database-level access to the "master" database. If a user has access to "MyAbc" but not the master database, then SSMS seems to struggle while making a connection and (I'm guessing) it sends a number of failed queries to the master database, but displays no indication in the U/I to show these problems, other than to slow everything down.
The problem doesn't happen when connecting with ADS.
I'm new to Azure and would rather not struggle with an unsupported client tool. I'm not sure if SSMS is supposed to be formally used for Azure databases. It would be good to hear one way or another.
This connection problem with SSMS seems to be something that others have experienced over the years as well. See:
Any information about the SSMS support would be appreciated.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(35.2, 96%, 13%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETD%3C/text%3E%3C/svg%3E)
I had the same issue when using any version of SSMS and connecting to an azure sql database.windows.net
Solution - Go to Options>> and select the Connection Properties tab...
Network protocol: change from default to TCP/IP
Network packet size: change from 4096 to 8192
Trust server certificate: checked
The problems with slow logins in SSMS is documented here:
https://techcommunity.microsoft.com/t5/azure-database-support-blog/lesson-learned-132-delays-connecting-to-azure-sql-database-from/ba-p/1502030
@DavidBeavon-2754 - Thank you for following up on your issue with the solution. More information can be found here: Contained Database Users - Making Your Database Portable which explains the Traditional Login and User Model and the Contained Database User Model scenarios. Syntax Differences exist for each and in the case where the user is a contained database user, the default database property must reflect the database value the user is authenticating against.
In SSMS, this is done via the Options button on the Login tab. Set Connect to database.
Hi,
SQL Server Management Studio is fully supported for Azure SQL Database or even Azure SQL Managed Instances.
https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15
Supported SQL offerings
This version of SSMS works with all supported versions of SQL Server 2008 - SQL Server 2019 (15.x) and provides the greatest level of support for working with the latest cloud features in Azure SQL Database and Azure SQL Data Warehouse.
Here's a guide on how to use SSMS to connect to your Azure SQL Database:
Quickstart: Use SSMS to connect to and query Azure SQL Database or Azure SQL Managed Instance
https://learn.microsoft.com/en-us/azure/azure-sql/database/connect-query-ssms
Best regards,
Leon
...not by attempting to connect and collecting feeling.
Let me collect my feelings before I reply...
Did you click the links that I referenced in the original question? This problem is not specific to me. But maybe I'm just less patient than most. I connect to SSMS databases quite a lot and don't have 20 seconds to wait for this every time. My latency to the azure datacenter is only about 40 ms and there is no reason for it to take 20 seconds to make a connection.
The solution for me was to add the user to the "master" database. I suspect there are several factors involved, and possibly more than one solution. I also understand the difference between a SQL login and a database user. (Although the difference seems a lot more of a technical/implementation detail in the case of Azure SQL DB than it was on-prem).
I did start by trying to use xevents to troubleshoot. However that profiling feature appears to be specific to a database. And the database I'm connecting to ("MyAbc") has no interesting events since all the interactions with it are very snappy. I tried setting up xevents on the "master" database to try to detect failed queries. But Microsoft doesn't seem to allow the profiling of system databases. So it eventually came down to a lot of trial-and-error. I have a repro and a support case open with Microsoft to confirm my findings. I don't care quite as much about SSMS itself ... but my Azure ADF appears to be affected by slow connectivity as well and I hope to solve those problems indirectly as well.
You had mentioned that I can "monitor on-premises error log file of the server" for my Azure database... I'm confused. If there was any type of "server" -oriented logging for azure sql db, I have never heard of it. As far as I know there is nothing database -related that would appear in my client-side event log or anything like that.
As far as the ADS client goes, I guess I probably haven't tried it long enough to give it a chance. These hybrid apps are not normally as useful as a full desktop application when it comes to productivity. You'd be amazed how many web developers overlook the keyboard, which is much more efficient for command shortcuts, and navigation, and menus. I think there is a substantially different audience for ADS than for SSMS. I'm certainly glad to hear that SSMS will be sticking around for a while.
I split the comment since there is 1000 char limitation...
This problem is not specific to me.
True, this is simply the way the SSSM works. I explain it in my post fully. It is the same for anyone anywhere if you are using a LOGIN which does not have a related USER in the master with the full permissions to execute the extra queries that the SSMS execute (again as explained in my post)
there is no reason for it to take 20 seconds
There are reasons as I explained in the post :-)
Better said, that there should not be any reason but in the way the SSMS works today this is expected in some cases.
The solution for me was to add the user to the "master" database.
This fit exactly what I wrote in the post.
ADS is faster for some reasons including since the SSMS execute queries behind the scenes against the master database, without any need for our direct work, and as I explained this is something that the ADS do not do the same. Adding the USER which based on the same LOGIN allow the SSMS to execute these queries using that USER.
In my opinion, THIS SOLUTION IS HIGHLY NOT RECOMMENDED FROM THE SECURITY POINT OF VIEW!
You should not grand any extra permission if not must. The SSMS is usually our tool to manage the database which is used by the application. The goal is usually the application and not the SSMS
You had mentioned that I can "monitor on-premises error log file of the server" for my Azure database...
Did you notice that I said on-premises?
As you quoted, I explicitly said "if you will monitor on-premises error log file...."
This is way to learn on what happen behind the scenes. It is not something you can do in the Azure Database, and I did not said "for Azure database" !
This is a step you must do in the learning procedure. Since Azure Database does not gives us control on the server level, I asked you to test how on-premises behave :-)
As far as the ADS client goes, I guess I probably haven't tried it long enough to give it a chance.
I recommend to take the time and do so. It is awesome tool. It does not fully replacement for the SSMS today but for 99% of the daily work it is a better tool in my opinion.
These hybrid apps are not normally as useful as a full desktop application
Not clear what you mean. This is a simple desktop application with a nice GUI. DO you think that Visual Studio is a desktop App or not? ADS is based on visual studio code in many aspects by the way...
I think there is a substantially different audience for ADS than for SSMS.
I totally agree (at least at this time - meaning at the current versions of SSMS and ADS)
I'm certainly glad to hear that SSMS will be sticking around for a while.
Awesome :-)
I am glad to see that you got the answer.
Welcome to the forums of QnA and you are welcome to check if you can help others on the way. Since this is a new system, there is a new points system for reputations. This means that we all start from zero now so this is your opportunity to join the core of the supporters and be in the top of the reputation points
==== this was hard work to cplit the message so many time, but finally it is published ====
True, this is simply the way the SSSM works. I explain it in my post fully. It is the same for anyone anywhere if you are using a LOGIN which does not have a related USER in the master with the full permissions to execute the extra queries that the SSMS execute (again as explained in my post)
I didn't see you identifying the extra queries that require access to "master". I had already figured out a fix for the 20 second connection delay, but I had not yet identified the queries because the xevents don't seem to be allowed against the azure "master". If you have found these queries then please post one of them here so I can relay it to tech support. Microsoft is still under the impression that database users shoud NOT need any access to the "master" database.
"There is no reason" is an expression meaning no good (or acceptable) reason. Of course there are many bad reasons for people to write buggy software. ;-)
because the xevents don't seem to be allowed against the azure "master".
And this why I gave you a small trick : check the communication on local server and learn from it a bit.
It is not the exactly the same attempt that the SSMS will do when it connect Azure maybe, but it will give you a good understanding about what the SSMS might try to do.
Using fiddler you can get the real information when you connect to the Azure Database, but this require more understanding then simply check the Extended Event in local server.
I had already figured out a fix for the 20 second connection delay
I think you can close the thread in the case that your solution fit you :-)
If you have found these queries
Sorry, I did not drill to this point in the Azure Database. I can upload the recording of what happen on premises, but I am not sure how many of these request relevant to the Azure physical server.
If someone really want to drill to this then fiddler is probably the answer
I think it is overkill for us to troubleshoot with fiddler, but I would be interested to hear what you find if you do. The only thing that really matters is whether or not Microsoft is willing to fix the bug. They are more than capable of diving into the implementation details at the level you are describing.
I think the main goal is to find some incentive for Microsoft to fix a problem in SSMS that doesn't affect ADS. They may even consider that 20 second delay to be a good thing - if it encourages users to migrate to their newer ADS out of exasperation.
Hi David - @DavidBeavon-2754
Did you click the links that I referenced in the original question?
Do you mean the link which you provided few hours ago?
If so, then I clicked it now :-)
It explains the issue well and provide a solution great
Jose speak about "SQL Server Management Studio is trying to obtain information, most probably, about the databases list, information of the server" - these are part of the hidden executions which I spook about that SSMS do behind the scenes (execution against the master database as I explicitly mentioned). These queries seems to be solved by adding a USER as Jose explain.
Thank you for sharing the link 👍 +5
The solution for me was to add the user to the "master" database. I suspect there are several factors involved, and possibly more than one solution.
Like always 😀
This is so annoying, right? The world could be so simple if we had only one source of issue for each issue we get :-)
The solution for me was to add the user to the "master" database.
I am glad to hear that you solve your issue. Well done on coming back and publishing the information.
but my Azure ADF appears to be affected by slow connectivity as well and I hope to solve those problems indirectly as well.
I did not notice the same issue in ADS. I will try to check it in the future.
You had mentioned that I can "monitor on-premises error log file of the server" for my Azure database
NO! NO! No! Sorry if I was not clear, but "monitor on-premises error log file of the server" is not related to the Azure Database. I spoke about on-premises as I explicitly said SQL Server and not Azure Logical SQL Server. Probably I did not explained it clear if this make someone confuse
The reason I spoke about on-premises is only as example on what can be done on premises in order to get some ideas about hidden executions that SSMS run sometimes (for example ON PREMISES you can see that SSMS try to execute queries against the master). From this behavior on premises I thought that you can get the conclusion about what happens in the Azure DB in general. But you do not monitor on premises for the Azure DB.
I think there is a substantially different audience for ADS than for SSMS. I'm certainly glad to hear that SSMS will be sticking around for a while.
I agree with both sentences at this time. I personally hope it will be changed in the future, but for this to happen, more work is needed on the ADS.
At this time (current version of the SSMS vs current version of ADS), you are totally right. ADS does not fit to all, and even people like me that prefer the ADS, sometimes use the SSMS since the ADS does not cover all the features yet.
But there are many feature which ADS have that SSMS does not, so I prefer it as main tool.
By the way, I think the issue in the forum which prevented me to publish the message in one clean post is solved.
There is announcement about the issue on the top of the forum page "[Announcement] Access Denied error when posting content". Hopefully, this is not needed anymore. I'll give some more days to see if I get the error :-)
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(220.8, 45%, 31%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EJA%3C/text%3E%3C/svg%3E)
I use SSMS all the time to connect to and work with Azure SQL without issue. I have also recently started using Visual Studio 2019 and SSDT to develop code for Azure SQL. Again no issues to report.
For the access issue you are describing, set the database name in the connection properties.
Or set the user up in the master database if you have a number of databases on a server - This will enable them to connect to the server and see all the databases attached to it. Note, that you will have to still give the users access to each database.