Welcome to MSDN Blogs Sign in | Join | Help

How It Works: Trace (.TRC) File Security

SQL Server 2000 shipped with different trace file security than later builds of SQL Server 2000, SQL Server 2005 and SQL Server 2008.

The SQL Server process (sqlservr.exe) writes the trace files to the specified destination.   The SQL Server 2000 release did not explicitly set the ACLs on the file when it was created.  The behavior was to allow the file to inherit the permissions of the destination directory. 

Closer inspection quickly indicates that for security reasons this is not the best design.  The trace files (.trc) can contain sensitive data (names, addresses, account numbers, ....).   These files need to be treated in a sensitive manor just like the database files (.mdf, .ldf).

SQL Server now ACLs the trace files with the same user permissions as that of the database files.   This is usually the service account SQL Server executes under and the Computer\Administrators group.   The permissions of the directory are no longer inherited.

The owner of the trace files must explicitly grant security permissions to others in accordance with security policies and company guidelines.

Bob Dorr
SQL Server Senior Escalation Engineer

Published Wednesday, June 25, 2008 3:01 PM by psssql

Comments

# Pregnant Man » How It Works: Trace (.TRC) File Security

Thursday, June 26, 2008 2:32 AM by Pregnant Man » How It Works: Trace (.TRC) File Security

PingBack from http://wordnew.acne-reveiw.info/?p=3377

# re: How It Works: Trace (.TRC) File Security

Thursday, June 26, 2008 11:15 PM by devlinse

I've been struggling for a couple of weeks to understand why this was happening so it's great to finally have an answer.

I don't disagree with the principal but shouldn't this be a decision left to the data owner?  The behaviour that's imposed goes against the file system inheritence model and based on my own experiences and those of others, is very confusing.  We were about to initiate a complete review of AD group policy in an effort to troubleshoot.

One thing that's not clear.  You say that

> SQL Server now ACLs the trace files with the same user permissions as that of the database files

Which database files?  What are the precise rules?  What if a machine has multiple databases - which permission set is used?

I'm glad I understand what's going on now but the post has raised almost as many questions!

Thanks, Simon

# re: How It Works: Trace (.TRC) File Security

Wednesday, July 23, 2008 2:53 PM by psssql

[RDORR]

To help clarify the ACLs uses are that of the SQL Server service and apply to the files created by the process.

It does not matter which database because the owner of the database could be a standard SQL Server user.

The SQL Server Service account owns the files and the ACLs are established according to the account.

Anonymous comments are disabled
 
Page view tracker