No open connections on SQL Server Management Studio, still triggering "Database Authentication Success" event on Azure SQL Database

Question

Hi,

(also posted here: https://feedback.azure.com/d365community/idea/508cedb9-5a8f-ec11-a81b-000d3a7cc2c1)

We are encountering some strange behaviour from our SSMS installations.

Our users connect to Azure SQL Databases in one of two ways:

1. by connecting Object Explorer to the logical server, and then running queries against a specific database by clicking "New Query" against that database
2. connecting straight from a query window to the logical server, and selecting the database from the "Available Databases" selector

When our users disconnect BOTH Object Explorer and all Query Windows connecting to an Azure SQL Database, we still receive "Database Authentication Success" events on our serverless Azure SQL Databases, preventing them from auto-pausing, arising from these users. These DBAS are 240s apart, so feels very programmatic in nature.
We are able to replicate this consistently.

The only solution we have found so far, is to close down SSMS completely, and start it again, thereby ensuring that any residual connections held within SSMS are therefore lost.
To reiterate, no visible connections to these databases exist, yet Azure SQL DB auditing is reporting that users are connecting from the application "SQL Server Management Studio".

Any ideas how a user can actually disconnect from an Azure SQL Database without having to restart SSMS?

Thanks,
Johan

Answer 1

Hi,

Our users connect to Azure SQL Databases in one of two ways:

The first option which you describe uses the second option behind the scenes. When you open query editor from the object explorer window while while clicking specific database then the SSMS open a new editor windows and then do the same action of connecting to the server

When our users disconnect BOTH Object Explorer and all Query Windows connecting to an Azure SQL Database, we still receive "Database Authentication Success" events on our serverless Azure SQL Databases, preventing them from auto-pausing, arising from these users.

(1) Please clarify how exactly do you get the information Database Authentication Success so we will be able to respond to the behavior (for example, do you use extended event and get this event?)

(2) You should monitor the the session logout and check if when you close SSMS if this event raised. If you close the SSMS then probably no such event is send from the SSMS and it can take several minutes until the session closed

Check this post on how to monitor LOGOUT using Extended Events in Azure SQL Database:

https://ariely.info/Blog/tabid/83/EntryId/258/Azure-SQL-Database-Audit-LOGOUT-using-Extended-Events.aspx

Any ideas how a user can actually disconnect from an Azure SQL Database without having to restart SSMS?

You can do this which is even better and clean logout

Closing the connection of your editor: Right click on the editor page -> select connection -> select disconnect

Close connection of the object explorer is done using the button Disconnect

----------

Ronen Ariely
Personal Site | Blog | Facebook | Linkedin