en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP

 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize

Recent Entries

Minimize
מאי28

Written by: ronen ariely
28/05/2020 02:32 RssIcon

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