Today I came across an interesting problem while changing the SQL server service to run on a non-administrative domain account. Previously the service was configured to log in as localsystem, which has full rights locally, but no domain rights. With this change, the service would start and then terminate. Here is a SQL error log snippet:
2007-02-13 15:05:01.29 spid5 Clearing tempdb database.
2007-02-13 15:05:01.58 spid5 Encountered an unexpected error while checking the sector size for file k:\mssql\MSSQL\data\tempdb.mdf. Check the SQL Server error log for more information.
2007-02-13 15:05:01.62 spid11 Starting up database Student.
2007-02-13 15:05:01.72 spid10 Starting up database Diners.
2007-02-13 15:05:01.80 spid9 Starting up database distribution.
2007-02-13 15:05:02.10 spid8 Starting up database Inventory.
2007-02-13 15:05:02.72 spid11 Starting up database NorthwindReportData.
2007-02-13 15:05:02.80 spid10 Starting up database TSQLDB.
2007-02-13 15:05:02.83 spid9 Starting up database SSEMDB.
2007-02-13 15:05:03.57 spid5 CREATE DATABASE failed. Some file names listed could not be created. Check previous errors.
2007-02-13 15:05:03.57 spid5 WARNING: problem activating all tempdb files. See previous errors. Restart server with -f to correct the situation.
Making the service account a member of the administrators group, fixed the problem, but of course this is not a good idea. I decided to use the SysInternals File Monitor, filemon.exe, to see if I could find the cause of the problem. What I found was that mssql.exe was trying to access the root of the volume where the SQL data files exist and was getting an access denied error. The fix was to grant the service account list access to the root of that volume. With that change, the sqlserver.exe service came up and stayed up. I also found a Technet article that points to the same solution for a similar problem: PRB: Error 5177 May Be Raised When Creating Databases
By the way, SysInternals was recently acquired by Microsoft and Mark Russinovich is now a Microsoft Employee. It appears that the great SysInternals utilities continue to be maintained.
