How to tell what is accessing my Azure SQL Server?

Tessaract

10/17/23, 7:28 PM

I have a legacy Azure SQL Server that I want to decommission but we have a LOT of legacy systems that no one knows about.

I want to confirm what accesses this server and/or its databases.

It doesn't have Log Analytics or App Insights turned on, no logs of any kind. I was thinking of turning them on but I don't know which type of log I should set it up to collect and, subsequently, which table in the logs I should look for any requests.

274

1 + 3

logging

sql-server

azure

azure-sql

diya

10/18/23, 12:53 PM

Simply switching off unidentified systems/services has always been a successful strategy for me. Either an owner or user starts whining or one less item to migrate.

Tessaract

10/18/23, 1:49 PM

@diya, unfortunately Azure recently changed their SQL databases to be unpausable. At the moment I am not aware how I can switch off an Azure SQL Server without deleting it.

Larry Silverman

10/18/23, 6:24 PM

You could just rename the SQL databases on the server. That should have the desired effect of "disconnecting" any users from it. However, Alberto's answer is a good way to go, and Azure SQL Auditing is how we keep track of things in our shop.

Score:3

Server

Alberto Morillo

10/18/23, 12:44 PM

Here you will find all the steps required to configure Azure SQL Auditing and Log Analytics, and then how to query log analytics to know who access the Azure SQL Database. Below you will find a query you can use against log analytics.

 AzureDiagnostics 
  | where ResourceId == '/SUBSCRIPTIONS/<your subs ID>/RESOURCEGROUPS/<your rsrc grp>/PROVIDERS/MICROSOFT.SQL/SERVERS/<YOURSERVERNAME>/DATABASES/<YOURDBNAME>' 
  | project event_time_t, statement_s, succeeded_s, affected_rows_d, server_principal_name_s, client_ip_s, application_name_s, additional_information_s, data_sensitivity_information_s
  | order by event_time_t desc
  | take 100

You can also see logins with active sessions on the Azure SQL Database with below query. You can schedule execution of the query and save the results to a table.

 SELECT login_name, session_id, total_elapsed_time,
 FROM sys.dm_exec_sessions

+ 0

Elon Musk

I sit in a Tesla and translated this thread with Ai:

EN: How to tell what is accessing my Azure SQL Server?

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.