locked
Azure Sql Database Audit Log. RRS feed

  • Question

  • Hi I have enabled sql azure database audit log on one of my database .

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

    my question in case of parameterized query I am not able to see the actual parameter value I am only able to see the parameter name.

    Tuesday, November 6, 2018 10:23 AM

All replies

  • Hello,

    You can use extended events for that purpose:


    -- Create Master Key first

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'abc123!@#$(*&)'

    GO


    -- Create database credential

    CREATE DATABASE SCOPED CREDENTIAL [https://yourblob.blob.core.windows.net/sqldbxedemo] WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'sr=c&si=RWL&sig=Upp2zasdfasdfasdfg6KTcHL3452345fdasdfAa2VRstEyj6Q%3D';

    GO


    -- Create WaitStats XE session that writes to both ring buffer and target file.


    -- The ring buffer is a good way of quickly testing if the even sesion created as per expected.

    CREATE EVENT SESSION [WaitStats] ON DATABASE

    ADD EVENT sqlos.wait_info(

        ACTION(sqlserver.client_app_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)),

    ADD EVENT sqlos.wait_info_external(

        ACTION(sqlserver.client_app_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.username))

    ADD TARGET package0.ring_buffer(SET max_memory=(1024)),

    ADD TARGET package0.event_file(SET filename=N'https://your blog.blob.core.windows.net/sqldbxedemo/WaitStats.xel',max_file_size=(10))

    WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)

    GO


    Hope this helps.


    Regards,

    Alberto Morillo
    SQLCoffee.com


    Tuesday, November 6, 2018 11:49 AM
  • thanks I am checking.
    Thursday, November 8, 2018 4:37 AM
  • Hi Alberto Morillo thanks for your answer.

    I am able to log but I need more guidance.

    My requirement:

    I have database with large no of tables.

    I have enabled audit like  for few tables only.

    AuditAction        : {UPDATE on dbo.tabl1 by public, INSERT on dbo.table1 by public....}
    AuditActionGroup   : {SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP, FAILED_DATABASE_AUTHENTICATION_GROUP} 

    after creating wait session  as suggested by you.

    Result:

    1. I am still getting parameterized statement in first log.

    2.  I started getting log in another location for extended events.

    3. These logs are for every operation on every table which I don't want.

    4. I see the sql text splitting in more than 1 event log in some cases.

    5. Can I combine both the log in one file?

    Friday, November 9, 2018 6:50 AM