Is SSMS Fully Supported for Connecting to Azure SQL Database?

David Beavon 891 Reputation points
2020-06-15T13:44:36.047+00:00

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.

Azure SQL Database
{count} votes

Accepted answer
  1. David Beavon 891 Reputation points
    2020-07-01T19:16:58.397+00:00

8 additional answers

Sort by: Most helpful
  1. Leon Laude 85,041 Reputation points
    2020-06-15T14:13:27.227+00:00

    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

    10053-applies-to.png

    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.

    Reference:
    https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15#supported-sql-offerings

    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

    No comments

  2. David Beavon 891 Reputation points
    2020-06-15T18:52:56.427+00:00

    @pituach

    ...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.


  3. Ronen Ariely 13,786 Reputation points Microsoft MVP
    2020-07-01T22:16:46.327+00:00

    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.


  4. John Aherne 511 Reputation points
    2020-06-15T14:15:11.907+00:00

    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.

    No comments