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!