1

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.

Alberto Morillo
  • 351
  • 1
  • 6
Tessaract
  • 155
  • 4
  • 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. – diya Oct 18 '22 at 12:53
  • @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. – Tessaract Oct 18 '22 at 13:49
  • 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. – Larry Silverman Oct 18 '22 at 18:24

1 Answers1

3

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
Alberto Morillo
  • 351
  • 1
  • 6