Forum Discussion

alzdba's avatar
alzdba
Copper Contributor
Sep 21, 2021

Azure Managed Instance capture Extended Events to targetfile url

Is it mandatory with an SQLMI to have a master encryption key before you perform the create credential?
If so, what database should we use to created the xevent "on server" for e.g. traces, failed login monitoring, ... ?

CREATE CREDENTIAL [https://mystorageaccount.blob.core.windows.net/sqlxevents]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '>youknowit<'

and

CREATE EVENT SESSION DBA_TrackFailedLogins
ON SERVER
ADD EVENT sqlserver.error_reported
(
ACTION
(
sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.nt_username,
sqlserver.session_nt_username,
sqlserver.username,
sqlserver.database_id
)
WHERE severity = 14
AND error_number = 18456
AND state > 1 -- removes redundant state 1 event
)
ADD TARGET package0.event_file
(SET
filename = N'https://mystorageaccount.blob.core.windows.net/sqlxevents/DBA_XE_FailedLogins_sqlmi_20210921_0705.xel',
max_file_size = 2,
max_rollover_files = 5
)
WITH (
MAX_MEMORY = 2048 KB,
EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY = 3 SECONDS,
MAX_EVENT_SIZE = 0 KB,
MEMORY_PARTITION_MODE = NONE,
TRACK_CAUSALITY = OFF,
STARTUP_STATE = OFF
);
go

 

Starting this xEvent failes with:

 

Msg 25602, Level 17, State 23, Line 38
The target, "someUniqueIdentifier.package0.event_file", encountered a configuration error during initialization. Object cannot be added to the event session. (null)

No RepliesBe the first to reply