Trace Flag 3625 Follow Up

Several months ago, I discussed my customer’s intention to enable trace flag 3625. Since that time, we have observed an intriguing phenomenon when encountering a login failure.

To demonstrate this, our initial step is to ensure we are capturing failed login attempts. In Server Properties, select the Security tab, click on Failed Logins Only, and then click OK.

Next, we need to test the login failure.

We can also verify the failure in the SQL Server error log.

SQL Error Log verifying the failed login attempt — SQL Error Log

Now we need to enable Trace Flag 3625. Refer to my previous article for detailed instructions.

We know that failed logins will be captured in the Error Log, but we also need to setup some other tools to capture the same information.

First, we need to create an extended events session to capture any errors.

Next, we can create a SQL Server audit to capture failed logins.

Now that we have those items configured, let us attempt to login with the incorrect password.

Login failed error with user name masked

As anticipated, the login name has been masked by the trace flag. Let us check the SQL Server error log.

The login name has also been masked here. What about the extended events session?

Extended events session showing masked user name

All the fields are either masked or blank.

Finally, let’s look at the SQL Server audit.

This is where we obtain additional information. The error message still contains the masked login name, but at least the Server Principal Name field displays the login name.

So why can’t we view the login name in error log or the extended events session? One might assume that an administrator should have the ability to the unmasked the login name in these logs, but that is not true. Is this a bug or an intentional design choice? It turns out that this trace flag has been in place for a long time and is functioning as intended. SQL does not trust external items, and the file system lacks knowledge of permissions granted by the SysAdmin role. However, the SQL Audit provides more detail because it is not considered an error messaging system and thus is not governed by the trace flag.

The lesson here is that using trace flags can have unintended consequences, so they should be used with caution.

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