Lesson Learned #80: Monitoring Login-Logout events in Azure SQL Database using Extended Events

This post has been republished via RSS; it originally appeared at: MSDN Blogs.

Hello Team,

Today, I created an extended event for monitoring the Login and Logout events for a Azure SQL Database. I did it, because I missed the logout in SQL Auditing Log.

 

  • 1.- Please, try to add this credential is your database. If you need instrucctions please review this URL.

 

CREATE DATABASE SCOPED CREDENTIAL [https://xxxxxx.blob.core.windows.net/extended]
WITH IDENTITY='SHARED ACCESS SIGNATURE',
SECRET = 'sv=2018-03-28&ss=bfqt&srt=sco&sp=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx%3D'

 

  • 2.- Add this extended event in your database:
CREATE EVENT SESSION [LoginAudit]
ON DATABASE
ADD EVENT sqlserver.login(   ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.session_id,sqlserver.username)),
ADD EVENT sqlserver.logout(    ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.session_id,sqlserver.username))
ADD TARGET package0.asynchronous_file_target(
SET filename='https://xxxxxxxx.blob.core.windows.net/extended/DemoPersistedError.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO

 

  • 3.- Run this ALTER command to start this extended event.
ALTER EVENT SESSION [LoginAudit] ON DATABASE STATE = START;
  • 4.- Leave this process running and test your application multiple times or use SQL Server Management Studio, etc..
  • 5.- Stop this event running this command:
 ALTER EVENT SESSION [LoginAudit] ON DATABASE STATE = STOP

 

  • 6.- Open the file DemoPersistedError.xel from the storage account that you choose, you will be able to see all this information that I share with you as an example using SQL Server Management Studio. Using the column client_connection_id you are able to identify the event per connection attempt.

Enjoy!

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.