Recently I added a second LUN to a clustered instance of SQL Server to isolate the logs files (and another for the TempDB) and I mounted them to an empty NTFS folder as I’ve done before, but when I started SQL Server, I recieved the following error:
CREATE FILE encountered operating system error 5 (Access is denied.) while attempting to open or create the physical file 'X:\MSSQL10_50.INSTANCENAME\TempDB\tempdb.mdf
The error means that SQL Server doesn’t have NTFS rights to the location of the TempDB, but when I tried to add those rights, the permissions weren’t granted to the domain proxy account as I’d expected, but were instead granted to the service SID account, MSSQL$InstanceName. I attempted to grant the permissions to this account at the new location, but couldn’t get it to resolve to an actual account. What finally worked was:
- In the permissions change dialog box, change the “Location” from the domain to the local machine (even though it’s a cluster and your using a domain account to run the service)
- In the text box, type “NT Service\MSSQL$INSTANCENAME” and click “Check Names”
Even though that appears to be a local account, it will resolve properly on all the cluster nodes involved. This step, as opposed to using the domain proxy account the service is running as, was necessary because (during the initial SQL Server setup process) I’d selected to use the proxy account SID to host permissions rather than a domain group. The better choice permissions-wise, but the source of some confusion!