I am attempting to configure my SQL Server instance to use a service account with the minimum privileges. I thought I had everything configured correctly, when I realized that having the service account as a member of the "SQLServer2005MSSQLUser" Windows Group meant that the service account was now in the "sysadmin" fixed server role. This was not the configuration I wanted.
I went through the Books Online article "Setting Up Windows Service Accounts" and made sure the login had access to the appropriate folders used by SQL Server. Then I stopped the SQL Server service and tried to restart it, without success. These are the error messages:
Code Snippet
SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
FCB::Open failed: Could not open file E:\MSSQL$STAGING\Data\model.mdf for file number 1. OS error: 5(Access is denied.).
TDSSNIClient initialization failed with error 0x5, status code 0x1.
TDSSNIClient initialization failed with error 0x5, status code 0x90.
I checked some other posts on this board, and they suggested the problem might be that the "VIA" protocol was enabled. I checked for this protocol in the Configuration Manager, and it is DISABLED in both the SQL Server 2005 Network Configuration and the SQL Native Client Configuration. What else could be causing this error?
The errors do not occur when I add the service account back to the "SQLServer2005MSSQLUser" Windows Group. The SQL Server service starts successfully when the account is back in this group.
Here are my answers to the questions posted at the top of this board:
What is the MS SQL version? - SQL Server 2005 SP2 (9.00.3054.00)
What is the SKU of MS SQL? - Enterprise Edition (SKU ID: 1804890536)
What is the SQL Server Protocol enabled? - TCPIP, Named Pipes
Does the server start successfully? - NO
If not what is the error messages in the SQL server ERRORLOG? - See above.
If SQL Server is a named instance, is the SQL browser enabled? - YES
What is the account that the SQL Server is running under? - Domain Account
Do you make firewall exception for your SQL server TCP port if you want connect remotely through TCP provider? Not applicable, Windows Firewall is not used
Do you make firewall exception for SQL Browser UDP port 1434?
Not Applicable, Windows Firewall is not used
Hi Ken,
You seem to be facing multiple problems with the account setup. First of all - make sure you have granted it read/write access to the folders where databases are located (especially the E:\MSSQL$STAGING\Data).
The next thing to do is grant the account access to the registry, in particular [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer] and subfolders + [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server] and subfolders.
The status code of 0x90 above probably indicates the inability of the service to access a registry entry called ProtocolList, it is usually under [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\SuperSocketNetLib], it is for SQL 2k compatibility purposes. What you can do is to capture a RegMon from a service startup attempt (http://www.microsoft.com/technet/sysinternals/utilities/regmon.mspx) and look for access denied entries in it. Based on that you can grant access and go from there. Similar for files - you could use FileMon (http://www.microsoft.com/technet/sysinternals/FileAndDisk/Filemon.mspx).
More information about the initialization failure status codes is available here: http://blogs.msdn.com/sql_protocols/archive/2006/04/28/585835.aspx.
HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Thank you, I was looking for a way to see the Registry access.
As far as the database files, here are the folders the account has "Full Control" over:
E:\MSSQL$STAGING\MSSQL.4\MSSQL (Stores the files for the system databases)
E:\MSSQL$STAGING\Data (Stores the files for all other databases)
I orginally granted the account "Full Control" at the parent folder (E:\MSSQL$STAGING), but this did not work.
I'll try out your suggestions and post back later.
|||I'll need some more time to learn how to use regmon. It was returning far too much data to be useful.
I did a search for "SuperSocketNetLib" within the Registry Editor. I found it in the following keys:
Code Snippet
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.4\MSSQLServer\SuperSocketNetLib
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\STAGING\MSSQLServer\SuperSocketNetLib
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib
I granted the service account "Full Control" on the first four keys I listed. The account already had "Read" permissions on "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib" due to its membership in the "SQLServerBrowserUser" Windows group.
After making these changes, I was able to start the SQL Server service. Everything appeared to be working until I checked EventViewer. The following error was rapidly filling the Application Log:
Code Snippet
UpdateUptimeRegKey: Operating system error 5(Access is denied.) encountered.I searched the Registry for both "UpdateUptime" and "UpdateUptimeRegKey" and found nothing. Is this a Registry key or is this something else?
My next step is to grant the service account "Full Control" over the following keys:
Code Snippet
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server (and subfolders)
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer (and subfolders)
You had told me to do this but I hadn't done so.
I'll post back later with additional results.
|||Granting the service account "Full Control" over "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server (and subfolders)" and "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer (and subfolders)" resolved the errors for "UpdateUptimeRegKey".
I was now able to start the service and keep it running without any further Windows errors. Unfortunately, I now started receiving errors within SQL Server. Two developers were trying to execute a stored procedure. They received the following error:
"An error occurred during decryption"
I did some research, and found out the error refers to the Service Master Key. Once again, the error disappears when I add the service account back to "SQLServer2005MSSQLUser".
Am I going to need to rebuild the master database in order to accomplish this change?
|||Hi Ken!
In the last few days I've tried to do exactly the same as you. I become a task to audit an MSSQL 2005 Standard server. I realised that the SQL server was originaly intalled to run the service under the Administrator account. So as in many reference, I try to change also the account to use a service account with the minimum privileges. Which is the 'NT AUTHORITY\NetworkService'. After it, I try to start the service and my problems began. I resolved step-by-step the same problems as you. Registry keys permission change, checking folders permissions and so on. Even I must change some settings with the Component Service Manager to let Local Activation for Microsoft Search FTE Admin. Anyway, at the end I still have this error message in the Event Viewer just as you.
So my question is that have you find out how can this error resolved? Or somebody else?
Many thanks!
|||Actually, I discovered I misunderstood the new security model for SQL Server. My misunderstanding was that I thought that the SQL Server service account should NOT be a member of the "sysadmin" fixed server role. It turns out that the account IS supposed to be a member of "sysadmin". Therefore, the is no reason to remove the service account from the SQLServer2005MSSQLUser" group.
The documentation in SQL Server Books Online needs to be revised to make this clear. When you read the Books Online article "Setting Up Windows Service Accounts", this is what it says in the "Reviewing Windows Permissions for SQL Server Services", in the "Required permissions" column:
Local user is recommended.
Under "Minimum permissions", it says the following:
"The account must be in the list of accounts that have "List Folder" permissions on the root drive where SQL Server is installed, and on the root of any other drive where SQL Server files are stored." and "The account must have "Full Control" permissions over any folders where data or log files (.mdf, .ndf, .ldf) will reside."
There is not one word about the requirement that the account be a sysadmin. By contrast, here is what the same article says about the Minimum permissions for the SQL Server Agent service account:
"The account must be a member of the sysadmin fixed server role."
Maybe Microsoft just assumed that we would know that the service account needed to be a sysadmin.
Microsoft could save us all a lot of headaches if they would include this simple statement with the information for the SQL Server service account.
No comments:
Post a Comment