מאי28
Written by:
ronen ariely
28/05/2020 02:32 
This is a simple step-by-step tutorial to demonstrate how we can create auditing of logout event using Extended events, on Azure SQL Database. It basically present an answer to a question in the MSDN forum, which where asked here:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/442b16a5-b1f6-4da0-81b4-40c758212cb9/
Step 1: Creating a storage container to store the auditing files
Skip this step if you already have a storage container
1.1 Open the Azure portal and Create new resource group
In this tutorial I will use the name: eers
You should use a unique name
https://portal.azure.com/#blade/HubsExtension/BrowseResourceGroups
1.2 Create new storage account inside the resource group
In this demo I will create storage named: eest
https://portal.azure.com/#blade/HubsExtension/BrowseResource/resourceType/Microsoft.Storage%2FStorageAccounts
Continue with default parameters in the next options and create the storage account
1.3 navigate to the storage account which we created in step 1.2 and create a container named eecn
2. Get shared Access Signature for existing container
2.1 navigate to the Storage accounts
2.2 On the left menu click on "Storage Explorer" -> Right Click on the container which we created -> select "Get Shared Access Signature"
2.3 Enable full permissions on the shared signature
mark all the security permissions:
Click on "Create" and copy the URI
This string include the URL to our container and the metadata of the shared key. It should have the format:
https://eest.blob.core.windows.net/eecn?sp=&st=&se=&sv=&sr=&sig=
Note: When we will create the DATABASE SCOPED CREDENTIAL in step 4.3, then the first part of the URI before the question Mark (https://eest.blob.core.windows.net/eecn) will be used as our DATABASE SCOPED CREDENTIAL name, and the the entire part after the question Mark (sp=&st=&se=&sv=&sr=&sig=) will be used as our SECRET.
3. Create new Azure SQL Logical Server & a new Database
3.1 Create new Azure SQL Logical Server
In this demo I create logical server named: eesql
https://portal.azure.com/#blade/HubsExtension/BrowseResource/resourceType/Microsoft.Sql%2Fservers
3.2 Create New Database
3.2.1 Open SQL Server Management Studio (SSMS) or Azure Data Studio (ADS).
3.2.2 Connect to the new Azure SQL Server
Note: For this you will need to confirm tyhat the Azure Firewall is well configure to allow you connect from your current IP address
3.2.3 Open new query editor and CREATE DATABASE
CREATE DATABASE [eedb] (
EDITION = 'Basic', SERVICE_OBJECTIVE = 'Basic', MAXSIZE = 500 MB
)
GO
4. Create Extended Events to monitor LOGOUT events
4.1 Connect to Azure SQL Database and open query editor
4.2 Create Master if you do not have one already
Note: If you configured audit for the database then you already have a MASTER KEY
CREATE MASTER KEY ENCRYPTION BY PASSWORD='';
GO
4.3 CREATE DATABASE SCOPED CREDENTIAL
Using the URI from step 2.3 create new DATABASE SCOPED CREDENTIAL
Note: The first part of the URI before the question Mark (https://eest.blob.core.windows.net/eecn) will be used as our DATABASE SCOPED CREDENTIAL name, and the the entire part after the question Mark (sp=&st=&se=&sv=&sr=&sig=) will be used as our SECRET.
Note: replace the SECRET in the following query according to the information you got in step 2.3
CREATE DATABASE SCOPED CREDENTIAL [https://eest.blob.core.windows.net/eecn]
WITH
IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sp=racwdl&st=2020-05-28T06:58:22Z&se=2020-05-29T06:58:22Z&sv=2019-10-10&sr=c&sig=here_we_have_the_signature'
GO
You can confirm that the DATABASE SCOPED CREDENTIAL created well
select * from sys.database_scoped_credentials
GO
4.4 Create new Extended Events SESSION & add the event sqlserver.logout
Note: You can add other events which you want to monitor as well separated with comma
CREATE EVENT SESSION [Monitor_LOGOUT] ON DATABASE
ADD EVENT sqlserver.logout(
ACTION(mdmtargetpkg.mdmget_TimeStampUTC,package0.event_sequence,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.compile_plan_guid,sqlserver.context_info,sqlserver.database_id,sqlserver.database_name,sqlserver.execution_plan_guid,sqlserver.num_response_rows,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_hash_signed,sqlserver.query_plan_hash,sqlserver.query_plan_hash_signed,sqlserver.request_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.transaction_sequence,sqlserver.tsql_stack,sqlserver.username))
ADD TARGET package0.event_file(
-- Change the size in production according to your need
max_file_size=(25)
)
WITH (TRACK_CAUSALITY=ON,STARTUP_STATE=ON)
GO
determine if the session is started or stopped and get more details on existing events
-- Get list of DATABASE EVENT SESSION
SELECT * FROM sys.database_event_sessions
GO
-- Get details on the actions on each event
SELECT * FROM sys.database_event_session_actions
GO
-- We can join the above two queries on event_session_id columns
4.5 Start the Extended Events SESSION to start monitoring
ALTER EVENT SESSION Monitor_LOGOUT ON DATABASE STATE = START;
GO
5. Watch Extended Events log files using T-SQL
Unfortunately the real name of the file which created for us is not exactly the one we used in step 4.4. In fact We might have multiple files according to the configuration like the max size of the files.
In my demo I configured to use the file:
https://eest.blob.core.windows.net/eecn/LogoutEvents.xel
This will lead to creation of files in the same location with dynamic names like:
https://eest.blob.core.windows.net/eecn/LogoutEvents_0_132351293652250000.xel
5.1 Find the Extended Event log files
You can find the name of the file(s) using the portal or any explorer tool which work with Azure Storage.
A better option is simply to use my following query (You can add filter in order to get only the files related to specific Extended Event by name)
-- Returns the path to all the EE log targets
;With MyCTE as (
select
name, session_source, MyXML = CONVERT(XML, target_data)
FROM sys.dm_xe_database_sessions AS s
JOIN sys.dm_xe_database_session_targets AS t
ON t.event_session_address = s.address
)
SELECT name, session_source, MyXML, m.c.value('@name', 'nvarchar(max)') as FilePath
FROM MyCTE
outer apply MyCTE.MyXML.nodes('EventFileTarget/File') as m(c)
5.2 Read the Extended Event log file
Once we have the location of the file we can use the following query to read the content
SELECT event_data = convert(xml, event_data)
FROM sys.fn_xe_file_target_read_file(
,null, null, null
);
GO
and if you really have time and you want to read the information from all the log files then you can use the following query
;With MyCTE as (
select
Session_Name = name, session_source, MyXML = CONVERT(XML, target_data)
FROM sys.dm_xe_database_sessions AS s
JOIN sys.dm_xe_database_session_targets AS t
ON t.event_session_address = s.address
),
MyCTE2 as (
SELECT Session_Name, session_source, MyXML, m.c.value('@name', 'nvarchar(max)') as FilePath
FROM MyCTE
outer apply MyCTE.MyXML.nodes('EventFileTarget/File') as m(c)
)
SELECT Session_Name, session_source, FilePath
,event_data = convert(xml, event_data)
FROM MyCTE2
CROSS APPLY sys.fn_xe_file_target_read_file(FilePath,null, null, null
)
where Session_Name = 'Monitor_LOGOUT' and session_source = 'eedb'
order by FilePath
GO
That's all...
I hope it was useful and I'll see in my next post