CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

How It Works: Trace (.TRC) File Security

How It Works: Trace (.TRC) File Security

  • Comments 6

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

Leave a Comment
  • Please add 4 and 2 and type the answer here:
  • Post
  • PingBack from http://wordnew.acne-reveiw.info/?p=3377

  • 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

  • [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.

  • Whay is the neame of the SQL service that need to be granted write permisions?

  • Scenario: The traces are created during daytime. At the night, a batch-user comes along and wants to copy the trace-files. But he can't, because the traces to not inherit the rights of the directory.

    What is the solution for that?

    BTW: I don't understand the necessity for this: An authorizes SQL-User decides, where the traces a written. If this is an unprotected directory, then let it be this. The way you guys are doing is very confusing.

  • Extended Events seem to bypass this permission issue.

Page 1 of 1 (6 items)