3

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
  • 416
Tessaract
  • 175

1 Answers1

4

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
  • 416