question

AnushaSRaj-9409 avatar image
0 Votes"
AnushaSRaj-9409 asked AnushaSRaj-9409 commented

ssms audit for azure db

Hi,

I have an azure Database which did not have audit enabled. Is there any way I can get the user login history or audits from system views or tables?

azure-database-mysqlazure-ad-audit-logs
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

GeethaThatipatri-MSFT avatar image
1 Vote"
GeethaThatipatri-MSFT answered AnushaSRaj-9409 commented

Hi, @AnushaSRaj-9409 Welcome to Microsoft Q&A, Thanks for posting your query.
Yes, you should be able to identify the specific query by providing the contains of the query in the 'statement' of Audit log fields by querying in log analytics.
For more information on querying log analytics on where you can find sample codes, please go through the link below:

AZURE SQL DB AND LOG ANALYTICS BETTER TOGETHER – PART #1 - Microsoft Tech Community

To find more information regarding the Audit log fields please go through the link below:

SQL Database audit log format - Azure SQL Database | Microsoft Docs

Hope the above information helps.

Please let me know if you are facing any issues,

you can enable the option from the portal in server blade under security.


201178-image.png

Regards
Geetha





image.png (67.4 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @GeethaThatipatri-MSFT . Thanks for responding.

The above process that u have shared only works if audit and diagnostic settings are enabled. Unfortunately, I had not enabled both and hence wanted to see if there is any other way to get the user login history.

0 Votes 0 ·

@AnushaSRaj-9409 I am sorry without auditing it is not possible. But you can look into active open connections by using sp_who2. in order to check with connection's history, it is not possible without auditing.
sp_who2 is basically a command you need to execute on Azure SQL DB to identify active open connections on DB. You can go through the link for reference:


Regards
Geetha

1 Vote 1 ·
AnushaSRaj-9409 avatar image AnushaSRaj-9409 GeethaThatipatri-MSFT ·

Thanks @GeethaThatipatri-MSFT for your response.

0 Votes 0 ·