How to Test Trace Flag 3625

UPDATED Jan 4, 2024 – Several people had mentioned they could not get the example code to work. This was because I chose to overflow a text column which only outputs the detailed error messages for SQL Server 2017 and above. I have since updated the code to use a numeric value which has been verified to work on SQL Server 2012 through 2022.

Recently, one of my customers considered enabling Trace Flag 3625 on one of their SQL Servers, but they wanted a way to quickly test its functionality beforehand. For those of you that are unfamiliar with that trace flag, once enabled it will mask some error messages for users that are not members of the sysadmin fixed server role. This can provide an added layer of security for potentially sensitive information.

Trace flag 3625 cannot be enabled using DBCC TRACEON. It will need to be added as a startup parameter for the SQL Server service. In SQL Server Configuration Manager, right click on the SQL Server service. In the popup window, select the Startup Parameters tab, enter -T3625, click Add and then OK. You will need to stop and restart the SQL Server service for the change to take effect.

Open a new query window and run DBCC TRACESTATUS(3625) to verify it’s enabled.

Next, we need to setup a test database and some logins with different permissions.

USE master
GO

-- Create a login with SysAdmin permission
CREATE LOGIN SysAdminUser WITH PASSWORD = '1StrongPassword'
ALTER SERVER ROLE sysadmin ADD MEMBER SysAdminUser
GO

-- Create a login with Control Server permission
CREATE LOGIN ControlServerUser WITH PASSWORD = '1StrongPassword'
GRANT CONTROL SERVER TO ControlServerUser
GO

-- Create a login with dbo permission
CREATE LOGIN DboUser WITH PASSWORD = '1StrongPassword'
GO

-- Create a login with insert permission
CREATE LOGIN LowPrivUser WITH PASSWORD = '1StrongPassword'
GO

-- Create a database
CREATE DATABASE TRACEFLAGTEST
GO

-- Switch to the test database
USE TRACEFLAGTEST
GO

-- Create a user and map it to the LowPrivuser login
CREATE USER LowPrivUser FROM LOGIN LowPrivUser
ALTER ROLE db_datawriter ADD MEMBER LowPrivUser
GO

-- Create a user and map it to the DboUser login
CREATE USER DboUser FROM LOGIN DboUser
ALTER ROLE db_owner ADD MEMBER DboUser
GO

-- Create a table
CREATE TABLE dbo.Customers(
  CustomerID TINYINT NULL,
  CustomerName VARCHAR(10) NULL
)
GO

Let’s run an insert statement that will overflow the TINYINT column of our table using the SysAdminUser login.

EXECUTE AS LOGIN = 'SysAdminUser'
INSERT INTO dbo.Customers(CustomerID,CustomerName) VALUES (500,'Patrick')
REVERT
GO

Now let’s run the same insert using the login that only has insert permission.

EXECUTE AS LOGIN = 'LowPrivUser'
INSERT INTO dbo.Customers(CustomerID,CustomerName) VALUES (500,'Patrick')
REVERT
GO

As you can see, the column name and the value have all been masked.

Finally, let’s run the same insert with our other two accounts: ControlServerUser and DboUser.

PRINT '======> Executing as ControlServerUser <======'
EXECUTE AS LOGIN = 'ControlServerUser'
INSERT INTO dbo.Customers(CustomerID,CustomerName) VALUES (500,'Patrick')
REVERT
GO

PRINT ''
PRINT '======> Executing as DboUser <======'
EXECUTE AS LOGIN = 'DboUser'
INSERT INTO dbo.Customers(CustomerID,CustomerName) VALUES (500,'Patrick')
REVERT
GO

Even with higher level permission, both logins still get masked error messages.

For SQL 2017 and higher, if we change the insert command to overflow the text column, then you will see a similar masked output.

PRINT '======> Executing as SysAdminUser <======'
EXECUTE AS LOGIN = 'SysAdminUser'
INSERT INTO dbo.Customers(CustomerID,CustomerName) VALUES (1,'Patrick Keisler')
REVERT
GO

PRINT ''
PRINT '======> Executing as LowPrivUser <======'
EXECUTE AS LOGIN = 'LowPrivUser'
INSERT INTO dbo.Customers(CustomerID,CustomerName) VALUES (1,'Patrick Keisler')
REVERT
GO

As you can see, with this trace flag enabled only members of the sysadmin fixed server role can see the full error message. This can provide some extra security by not sharing sensitive information for low privileged users. Even if your application already provides this type of error masking, this will help protect against users with direct database access.

Reference:
https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-ver16#trace-flags