Question

willtwc on Mon, 06 Nov 2017 21:58:25


I'm having issues trying to get more information through extended events on failed logins.  Tried the information suggested in the link but 

https://social.msdn.microsoft.com/Forums/en-US/655c23a6-9d58-40a1-8410-565fbf1d0bf4/how-can-i-check-the-detail-of-failed-connection-for-azure-sql-database-?forum=ssdsgetstarted

Tried the below method too and no results were returned for failed logins too.  

select * from sys.dm_xe_database_sessions

DECLARE @Shredit XML;
SELECT @Shredit = CAST(target_data AS XML)
FROM sys.dm_xe_database_sessions AS s
JOIN sys.dm_xe_database_session_targets AS t
ON t.event_session_address = s.address
--WHERE s.name = N'azure_monitor';
 
SELECT
qp.query('.') AS event_data
FROM @Shredit.nodes('RingBufferTarget/event') AS q(qp);

DECLARE @ShredMe XML;
SELECT @ShredMe = CAST(target_data AS XML)
FROM sys.dm_xe_database_sessions  AS s
JOIN sys.dm_xe_database_session_targets AS t
ON t.event_session_address  = s.address
WHERE s.name = N'azure_monitor';
 
SELECT
QP.value('(data[@name="statement"]/value)[1]', 'varchar(max)') as [SQL CODE],
QP.value('(action[@name="database_name"]/value)[1]', 'varchar(max)') as [Database],
QP.value('(@timestamp)[1]', 'datetime2') AS [timestamp]
 
FROM @ShredMe.nodes('RingBufferTarget/event[@name=''sql_statement_completed'']') AS q(QP);
GO


Sponsored



Replies

Alberto Morillo on Mon, 06 Nov 2017 22:43:15


Hello,

Please use SQL Database Auditing. Enable de action group FAILED_DATABASE_AUTHENTICATION_GROUP.

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-auditing

Hope this helps.


Regards,

Alberto Morillo
SQLCoffee.com


VidhyaSagar on Fri, 10 Nov 2017 08:59:40


If you are looking for basic details then you can grab it from ring buffer. Try the script below.

;WITH RingBufferConnectivity as
(   SELECT
        records.record.value('(/Record/@id)[1]', 'int') AS [RecordID],
        records.record.value('(/Record/ConnectivityTraceRecord/RecordType)[1]', 'varchar(max)') AS [RecordType],
        records.record.value('(/Record/ConnectivityTraceRecord/RecordTime)[1]', 'datetime') AS [RecordTime],
        records.record.value('(/Record/ConnectivityTraceRecord/SniConsumerError)[1]', 'int') AS [Error],
        records.record.value('(/Record/ConnectivityTraceRecord/State)[1]', 'int') AS [State],
        records.record.value('(/Record/ConnectivityTraceRecord/Spid)[1]', 'int') AS [Spid],
        records.record.value('(/Record/ConnectivityTraceRecord/RemoteHost)[1]', 'varchar(max)') AS [RemoteHost],
        records.record.value('(/Record/ConnectivityTraceRecord/RemotePort)[1]', 'varchar(max)') AS [RemotePort],
        records.record.value('(/Record/ConnectivityTraceRecord/LocalHost)[1]', 'varchar(max)') AS [LocalHost]
    FROM
    (   SELECT CAST(record as xml) AS record_data
        FROM sys.dm_os_ring_buffers
        WHERE ring_buffer_type= 'RING_BUFFER_CONNECTIVITY'
    ) TabA
    CROSS APPLY record_data.nodes('//Record') AS records (record)
)
SELECT RBC.*, m.text
FROM RingBufferConnectivity RBC
LEFT JOIN sys.messages M ON
    RBC.Error = M.message_id AND M.language_id = 1033
WHERE RBC.RecordType='Error' --Comment Out to see all RecordTypes
ORDER BY RBC.RecordTime DESC