Sql-server – Auditing – parameter values not being written to log

auditsql serversql-server-2008sql-server-2008-r2

I am using auditing to track usage of some procs in the database. In the log I can see a record for the Procedure being run and, immediately afterwards,the SQL statement that is being run. The statement shows the parameter name rather then the value i.e. WHERE CaseId=@P_CaseId rather than WHERE CaseId =100

Now I know there was a hotfix for SQL 2008 (not R2) that made this happen: http://support.microsoft.com/kb/967552.

Did this not get rolled into R2? Is there a different fix I can apply? Is there a workaround anyone knows?

Best Answer

I can confirm in SQL Server 2008 R2 Service Pack 3 (v10.50.6000.34), simple parameterized queries do result in the parameterized values being recorded in the audit log.

I setup a simple test rig, as follows.

Create a server audit:

USE master;
IF NOT EXISTS (SELECT 1 FROM sys.server_audits sa WHERE sa.name = 'AuditTest')
BEGIN
    CREATE SERVER AUDIT AuditTest 
    TO FILE (FILEPATH = 'D:\SQLServer\MV\Audits', MAXSIZE = 2MB, MAX_ROLLOVER_FILES = 1, RESERVE_DISK_SPACE = ON)
    WITH (
        QUEUE_DELAY = 0
        , ON_FAILURE = CONTINUE
        , AUDIT_GUID = N'B126B4DD-3973-4993-9ADF-4D324A15A8A4'
    );
END
ALTER SERVER AUDIT AuditTest WITH (STATE = ON);
GO

Create a database where we can play with auditing:

IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = N'AuditTest') 
BEGIN
    ALTER DATABASE AuditTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE AuditTest;
END
CREATE DATABASE AuditTest;
ALTER DATABASE AuditTest SET RECOVERY SIMPLE;
BACKUP DATABASE AuditTest TO DISK = 'NUL:';
ALTER DATABASE AuditTest SET PARAMETERIZATION SIMPLE;
GO

Create a table in the database:

USE AuditTest;
CREATE TABLE dbo.TestTable
(
    id int NOT NULL
        CONSTRAINT FK_TestTable
        PRIMARY KEY
        CLUSTERED
        IDENTITY(1,1)
    , someVal varchar(30) NOT NULL
);

Create a database audit spec:

CREATE DATABASE AUDIT SPECIFICATION AuditTestSpec
FOR SERVER AUDIT AuditTest
ADD (SELECT, INSERT, UPDATE, DELETE ON dbo.TestTable BY dbo)
WITH (STATE = ON);

Create a stored procedure and trigger an audit action:

CREATE PROCEDURE dbo.AuditTest
(
    @t varchar(50)
)
AS
BEGIN
    INSERT INTO dbo.TestTable (someVal) --stored procedure
    VALUES (@t);
END
GO
EXEC dbo.AuditTest @t = 'runtime value not audited - procedure';
GO

Perform a direct parameterized test:

DECLARE @t varchar(50);
SET @t = 'runtime value not audited - direct insert';
INSERT INTO dbo.TestTable (someVal) --direct insert
VALUES (@t);

SELECT *
FROM dbo.TestTable tt
WHERE tt.someVal = @t;

Finally, run a simple-parameterization test:

INSERT INTO dbo.TestTable (someVal)
VALUES ('runtime value audited - direct stmt1');
GO
INSERT INTO dbo.TestTable (someVal)
VALUES ('runtime value audited - direct stmt2');
GO

The execution plan for the above two simple-parameterized statements shows simple parameterization is working as expected:

enter image description here

Read the audit log, using a cursor and PRINT to preserve formatting:

USE master;
DECLARE @stmt nvarchar(max);
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT af.statement
FROM fn_get_audit_file('D:\SQLServer\MV\Audits\AuditTest_B126B4DD-3973-4993-9ADF-4D324A15A8A4_*', default, default) af;
OPEN cur;
FETCH NEXT FROM cur INTO @stmt;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @stmt;
    FETCH NEXT FROM cur INTO @stmt;
END
CLOSE cur;
DEALLOCATE cur;

Results:

INSERT INTO dbo.TestTable (someVal) --stored procedure
    VALUES (@t);
INSERT INTO dbo.TestTable (someVal) --direct insert
VALUES (@t);

SELECT *
FROM dbo.TestTable tt
WHERE tt.someVal = @t;

INSERT INTO dbo.TestTable (someVal)
VALUES ('runtime value audited - direct stmt1');
INSERT INTO dbo.TestTable (someVal)
VALUES ('runtime value audited - direct stmt2');

As you can see in the above output, the simple-parameterized statement run-time values were captured in the audit log.

Related Question