Error “Failed to open a file. Access is denied”, when attempting to open SQL 2005 Profiler Trace

  • Article

Hello All,

Yesterday while doing the regular stuff, I was reported a strange error related to profiler traces. I broke my head for a day almost to figure out what was happening.

Issue: I have a SQL Agent Job that creates profiler trace files using sp_trace_create. The job is being owned by my ‘Domain’ A/c which is also SQL Service start-up account.

My colleague (say user-X), when attempts to open the trace files gets error >>

“Failed to open a file. Access is denied”.

This is strange, since there another SQL 2000 Instance, where he can successfully open the trace files. He has NO access on both SQL 2005 and SQL 2000 instances. After several hours of agonizing and troubleshooting, I finally got this on CSS Blog >> How It Works: Trace (.TRC) File Security

“SQL Server 2005 has a different trace file security as compared to previous build of SQL 2000. Per new security design.The owner of the trace files must explicitly grant security permissions to others in accordance with security policies and company guidelines”

So a short solution to this is to grant user-X access to SQL Server.

However, what if I don’t want to have user-X any access on SQL Server. Here’s the workaround I tried and it worked perfect for me:

  • Use NTFS Permissions Inheritance in Windows

- Create a shared-folder where both Job-owner and user-X have full access

- Job-owner copies the trace files and paste them to above shared-folder

- Now, user-X will be able to open the trace files without any issue, since the parent folder's permissions are inherited by all files of the parent.

Refer to Microsoft KB Control NTFS Permissions Inheritance in Windows for more details.

If you happen to think a better workaround or a different idea - I'm all ears.

Disclaimer: I work at Microsoft. Everything here, though, is my personal opinion and is not read or approved by Microsoft before it is posted. No warranties or other guarantees will be offered as to the quality of the opinions or anything else offered here.

Comments

  • Anonymous
    November 12, 2010
    I was having an issue while trying to create the trace file on a network resource. The test server had no issue writing to a network file but the fairly recently installed production server couldn't write to a network file, although the network file was accessible and editable from the production system. The I tried and change the user running the SQL server service, I changed it to a windows user and it worked!

  • Anonymous
    November 12, 2010
    Hi Faraz, This issue you described is lil different. You got issues while trying to create a new database trace on a "network drive/file". To have done, the SQL Server Service A/c should have read/write permissions on "network drive/file" where you intend to create the trace file, else SQL will not be able to write to trace. Here's what SQL Docs says "The SQL Server service must be started using a domain user account to access any resources on a remote computer. Verify that the MSSQLServer service is started under a domain account that has write access to both the Windows NT Server share and its underlying partition (if the partition is formatted with the Windows NT file system, or NTFS)” Hope this explains VarunD