Finding the originator of a database query using SQL Azure Data Tools Profiler or related

Question

We've got a rogue process running somewhere that executes queries against a test database we have hosted on Azure SQL. I'm trying find this process so I can kill it. There are a number of app servers and development PCs where it could be hiding, and I haven't been able to track it down by looking at processes running on these machines by hand.

I can use The Azure Data Studio Profiler extension to get some Extended Event logging from the database. From there, I can see the text of queries being run, the Application Name and the ClientProcessID.

Sample data from profiler

I can't seem to use any of this data to find the host name or ip address of the server where these queries originate. Can I determine this using the data available in Azure Data Tools Profiler? Or is there some other way to work backward to find it? Since this is hosted on Azure, I can't use the Sql Management Studio Profiler, which I think would give me the Hostname right away.

Alberto Morillo Alberto Morillo · Accepted Answer · 2020-02-25T02:32:13

Azure SQL Auditing should provide you the application name, login name and client IP address that executed the query. Please read this article to enable Auditing and look for the event type BATCH_COMPLETED.

Set-AzureRmSqlDatabaseAuditing ` 
    -State Enabled ` 
    -ResourceGroupName "rgYourResourceGroup" ` 
    -ServerName "yourservername" ` 
    -StorageAccountName "StorageAccountForAuditing" ` 
    -DatabaseName "YourDatabaseName"

Finding the originator of a database query using SQL Azure Data Tools Profiler or related

1 Answers