May 12, 2010 at 8:28 am
We recently upgraded our SQL Server from Windows Standard to Enterprise. After this upgrade we noticed that unless the domain user running SQL Service is under the local Administrators group it will not start the service, nor will it load the performance stat dlls for sql server because access is denied to certain folders/files.
Folder security did not change and shows this account has proper access, the account is in the necessary group, SQLServer2005MSSQLUser$InstanceName$MSSQLSERVER. The account IS a sysadmin in SQL Server.
When trying to logon to the actual SERVER, using the domain account you get an error that the account cannot write to a Windows folder.
The OS: Windows Enterprise 32 bit 2003 R2 (upgraded from Standard) with PAE on
SQL Server: SQL 2005 Standard SP3
The other oddity is that the SQL Server executable is under a ReportingServices folder instead of MSSQL, but that shouldn't be a problem since you can access ALL of the SQL folders while logged in as the domain account.
Here's a log from the Windows Application Event under MSSQLSERVER
Event ID: 17204
FCB::Open failed: Could not open file E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MSSQL.1\MSSQL\DATA\mastlog.ldf for file number 2. OS error 5(Access is denied).
May 17, 2010 at 10:47 am
Any ideas out there? I also have the same issue on another server that was not upgraded to Windows OS Enterprise.
May 17, 2010 at 1:18 pm
Upgraded from windows standard to enterprise? I thought that was a reinstall, therefore you would have to reinstall SQL.
the install directory looks weird, MSSQL.1\MSSQL\DATA\ directories appear to be replicated? Are you sure you are checking the permissions at the right point in the tree structure,
Have you got a working instance you can compare permissions?
Free tools such as filemon and regmon may tell you exactly whats going on here.
---------------------------------------------------------------------
May 18, 2010 at 6:40 am
I too think that the OS upgrade may be a reinstall.
I would find a time when I could take the server down, then through SQL Server configuration manager, change the account SQL uses to a completely different account.
After the service restarts, change back to the original account.
See if that fixes it.
Greg E
May 18, 2010 at 9:28 am
Thanks to you both. I will check the filemon tool. I changed the service account when I re-started the server the other day and then changed it back, unfortunately no-go.
I know that the install path is strange.
I may just take it offline this weekend and to a complete uninstall / reinstall to fix it because it probably really needs it at the registry level (worst case scenario).
I will post back the results!
Thanks again!
May 18, 2010 at 11:08 am
The companion to filemon - regmon - can also be very helpful.
Hope the reinstall clears things up.
Greg E
May 18, 2010 at 4:16 pm
You may also be running afoul of a "new" Windows security feature, if your Enterprise edition is a more recent SP than the original Standard edition install. So I would check into TechNet articles associated with applying various Windows and SQL Server SP's for possible problems.
I know at one time I was suprised to find out that when running a job step under a SQL Agent proxy the xcopy's failed. Took a while to realize the "not permitted" wasn't the files and folders involved. It was because the proxy account was just a member of local "Users", which doesn't have permissions to execute xcopy.exe. Probably the "fix" to some security vulnerability.
So it might take a while to figure out what your permissions issue is. Probably a fresh install of Windows and SQL is the faster approach.
David Lathrop
DBA
WA Dept of Health
May 19, 2010 at 6:26 am
Hawkeye_DBA (5/12/2010)
We recently upgraded our SQL Server from Windows Standard to Enterprise. After this upgrade we noticed that unless the domain user running SQL Service is under the local Administrators group it will not start the service, nor will it load the performance stat dlls for sql server because access is denied to certain folders/files.When trying to logon to the actual SERVER, using the domain account you get an error that the account cannot write to a Windows folder. .
Here's a log from the Windows Application Event under MSSQLSERVER
Event ID: 17204
FCB::Open failed: Could not open file E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MSSQL.1\MSSQL\DATA\mastlog.ldf for file number 2. OS error 5(Access is denied).
One other thing to check is Local Security Policy, User Rights Assignment.
Administrators are included in many rights, including the ability to Logon Locally.
You might have more than 1 problem, and the previous post about being a memeber of the Users Group reminded me that there are some things that may have been tweaked in the previous OS that are easily forgotten.
Greg E
Greg E
August 19, 2010 at 10:57 am
Reinstalled SQL and it is all good now.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply