none
Failed login information

    Question

  • 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

    Monday, November 06, 2017 9:58 PM

All replies

  • 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


    Monday, November 06, 2017 10:43 PM
  • 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


    Mark as ANSWER if I helped you today :-) www.sql-articles.com

    Friday, November 10, 2017 8:59 AM