Azure Sql数据库审核日志. [英] Azure Sql Database Audit Log.

问题描述

您好，我已在我的一个数据库上启用了SQL Azure数据库审计日志.

https://docs.microsoft.com/zh-cn/azure/sql-database/sql-database-auditing

关于参数化查询的问题，我看不到实际的参数值，我只能看到参数名称.

解决方案

SQLCoffee.com

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.

解决方案

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

这篇关于Azure Sql数据库审核日志.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！