question

DavidBeavon-2754 avatar image
0 Votes"
DavidBeavon-2754 asked TungDang-6199 published

Is SSMS Fully Supported for Connecting to Azure SQL Database?

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
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·
DavidBeavon-2754 avatar image
0 Votes"
DavidBeavon-2754 answered Mike-Ubezzi edited
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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


11312-screenshot-107.png






1 Vote 1 ·
screenshot-107.png (48.4 KiB)
LeonLaude avatar image
1 Vote"
LeonLaude answered LeonLaude edited

Hi,

SQL Server Management Studio is fully supported for Azure SQL Database or even Azure SQL Managed Instances.
https://docs.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://docs.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://docs.microsoft.com/en-us/azure/azure-sql/database/connect-query-ssms


Best regards,
Leon






applies-to.png (4.8 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JohnAherne-0132 avatar image
0 Votes"
JohnAherne-0132 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

pituach avatar image
0 Votes"
pituach answered pituach edited

As a result of a bug in the QnA system, I cannot publish my answer directly, so I post it in my blog temporarily. In addition I attahc a file with the original content which I tried to publish here. I HIGHLY recommend to read it fully since there are some confusions and inaccuracy in part of the information in this thread. In addition, I already took recording of tracing of ADS and SSMS, and I will add few images and the recording to my post, as soon as I will have time. In the meantime, this is a response for the question in this thread.

https://ariely.info/Blog/tabid/83/EntryId/263/compatibility-of-SSMS-to-Azure-Database-and-Differences-between-SSMS-and-ADS-regarding-the-performance.aspx



Original content which I tried to post ion the forum: 10007-qna-q36069.txt





qna-q36069.txt (4.6 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DavidBeavon-2754 avatar image
1 Vote"
DavidBeavon-2754 answered DavidBeavon-2754 commented

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


· 9
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.



1 Vote 1 ·

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







0 Votes 0 ·

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.

0 Votes 0 ·
Show more comments

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.

0 Votes 0 ·

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 :-)

0 Votes 0 ·

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)

0 Votes 0 ·

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

1 Vote 1 ·
pituach avatar image
1 Vote"
pituach answered pituach edited

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.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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 :-)

0 Votes 0 ·
DavidBeavon-9445 avatar image
0 Votes"
DavidBeavon-9445 answered Mike-Ubezzi commented

@pituach
When I said "Azure ADF appears to be affected" by slow connectivity I was referring to Azure Data Factory. It has slow interaction with SQL for unrelated reasons. (initially I was hoping that if I was able to discover the reason for the SSMS delays, then I would know how to fix data factory as well. That didn't turn out the way I had hoped.)


Azure Data Factory uses an "integration runtime" that introduces it own long delays, to make it appear as if it is SQL that is slow. In actuality SQL is not slow and I have the xevents to prove it.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

In the case where the IR is Azure hosted, the VM provisioned to host the IR needs to be adequately sized and there is a document that discusses this in greater detail: Configure the Azure-SSIS Integration Runtime for high performance

It is better to error on the side of too much VM as the amount of time it takes to troubleshoot and figure out the IR VM is undersized, you would be done with the project if you selected a more powerful VM to begin with.


0 Votes 0 ·
DavidBeavon-2754 avatar image
0 Votes"
DavidBeavon-2754 answered

@Zagato36


We've already explored using beefier VM's that are both azure-hosted and self-hosted. This doesn't seem to be the solution. Whenever a pipeline has a lot of small activities that interact with SQL (eg. running a sproc that should take only 5 ms) then it gets bogged down for some inexplicable reason. I have another question here in the forums about that. (here: https://docs.microsoft.com/en-us/answers/questions/36323/adf-performance-troubleshooting.html )


For example, ADF will introduce a substantial overhead on a sproc that, according to SQL xevents, should be taking 5 ms. That ADF overhead can run for 3 seconds long or up to 20 seconds long after it is wrapped with an ADF/IR "activity".


I would guess that this type of behavior is generally well-known by people who use ADF . However the explanation for this doesn't seem to come up in my searches. My next step is just to create a repro with a simple loop, and then demonstrate to Microsoft tech support that ADF is introducing some delays that are inconsistent and extremely long. I suspect this is because of the way the IR is designed to use an async/polling mechanism to query the ADF instance for work (...ie. the IR asks ADF for work to do, rather than the other way around).


In general my primary complaint about ADF is the lack of a large community and the inability to find much in google searches about this. I suspect the tool is still in its infancy and probably has a ways to go before people start demanding more performance out of it. The current users of ADF may not know or care why an activity that is supposed to take 5 ms is being wrapped with an additional 20 second delay within the IR itself.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TungDang-6199 avatar image
0 Votes"
TungDang-6199 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.