Saturday, February 09, 2008

SQL Server 2005 Troubleshooting

Yesterday I noticed that the SQL Server 2005 installed on my dev box was not working, so like a good sys admin I rolled up my sleeve and started to gather WTF was going on.

Recently I installed Visual Studio 2008 and with all the default options it installs the SQL Server 2005 Express Edition, which is good when you don't have it already installed.

After that (I guess) the previous instance of SQL Server stopped working.

Before I dwell on what was wrong, let me tell you what's my current setup.

I have the SQL Server running on a specially created account. Using this account I can have the SQL Server process to communicate with other computers on the network (after all it does not present itself as an anonymous account).

The SQL Express edition, installed with the defaults, run on the NETWORK SERVICES account, which is good, once I don't have plans for this instance.

Anyway, when I detected that the SQL Server was not working that was the message present at the Event Viewer:

FCB::Open: Operating system error 5 (Access is denied.) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf'. Diagnose and correct the operating system error, and retry the operation.
I scratched my head... "WTF?! How the heck the Access Denied on master db?"

I checked the permission for the master.mdf and LO & BEHOLD, the account used for SQL Server does not have permission on the file.

So, I granted MODIFY permission on the folder containing the master.mdf file and...

TA-DA!! Nothing happened!!

The freaking SQL Server still refused to start, giving the same error message.

Just to make sure it was still an Access Denied problem I changed the SQL Server service account to NETWORK SERVICES and it started nicely.

OK, it was still a question of permissions, so I granted FULL CONTROL permission on the SQL Server install path to the SQL Server account I use, and it worked.

Now I need to thinker a bit with the permissions to know where the permission need to be set in order for it to work properly.


Post a Comment