INF: Permissions required for SQL Server Service account to use SSL certificate
In this blog, we will discuss a scenario which is common across the SQL Server environment using certificates. With the help of another security expert, we were able to find an additional permission required for SQL service account (Non Sys admin) to enable SSL encryption on Server level.
Usually, We will have dedicated service accounts for all the applications including SQL. These accounts, would be domain account and would have “minimum” permission to run SQL Server. For these scenarios, every application team have defined a minimum permissions required to run the application. They have not given “Access this computer from the network” to service account hence we were not able to login to computer using service account. Please note that service account was also NOT a part of “Administrators” group on machine.
We performed testing on below two environment to check what’s required to make certificate works.
- SQL 2005 ENT on windows 2003.
- SQL 2005 ENT on windows 2008.
The tool which came very handy in getting to the crux of the issue was ProcMon (https://technet.microsoft.com/en-us/sysinternals/bb896645.aspx ) from sysinternals. This tool helped us in getting the solution very fast and accurate.
Lets dive in to repro! We created a certificate so that we can use it for SSL encryption. You can use MakeCert (MakeCert.exe is part of the .NET Framework SDK. MakeCert.exe is also available in the Platform SDK). The other way you can create certificate for testing purpose it to use IIS 5 or IIS 6 along-with the trial version provided by third parties. For our testing, we created certificate using below command
makecert -r -pe -n "CN=<FQDN of the machine>" -b 01/01/2000 -e 01/01/2036 -eku 1.3.6.1.5.5.7.3.1 -ss my –sr currentuser -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12
Above command would create a certificate in Current User Store as shown below.
Next, we have to export the certificate to a file and then import to Local Computer Store.
Now as we had a certificate and imported the same to use with SQL server. Once the import (in Current User\Personal Store) was successful, we were able to see the certificate in drop down under the certificate tab in configuration manager. (We have masked the domain name due to internal reasons)
We gave SQL server local admin on the server and enabled SSL encryption on the server. Started the SQL Server service and Bingo! service started. So we knew that if SQL has local admin privileges, we can use SSL without any issues with the certificate. Now, we removed the local admin privilege from the SQL account and SQL Service fail to startup with below error in Error Log
2010-05-27 12:28:35.39 Server Error: 26014, Severity: 16, State: 1.
2010-05-27 12:28:35.39 Server Unable to load user-specified certificate. The server will not accept a connection. You should verify that the certificate is correctly installed. See "Configuring Certificate for Use by SSL" in Books Online.
To use this certificate for service account (which is different than current user) we have to provide proper permissions. There are two ways:
1. Follow below steps if you like UI.
a. Go to Start > Run> mmc.msc
b. Click on File > Add/Remove Snap-in
c. Choose Certificate from left list and click on Add
d. Choose Computer account
e. Choose Local Computer and click on Finish.
f. Click OK
Follow below screen show and choose “Manage Private Keys” after selecting the certificate.
g. Add new service account in the “permission for…. “ screen.
2. Run ProcMon utility in the background to check, which all registry and files SQL was trying to access during start up. We found that it was trying to read a system file that was stored on the below location :
c:\ProgramData\Microsoft\Crypto\RSA\MachineKeys
on Windows 2008 server. We gave full permission on that system file (File name you will get it from Procmon) to SQL Server service account and tried to start SQL server and it started.
We repeated the same test on Windows 2003 and found the below location :
C:\Documents and Settings\All Users\Application Data\Microsoft\Crypto\RSA\MachineKeys
Gave permission on the system file, which SQL was trying to access and was able to start SQL successfully.
Now what if we are having multiple instance on the box running under same SQL Server service account, instead of granting permission on individual files, we can grant full permission on the entire folder and inherit permission to child files (some people wants less work J).
Note:In this scenario we had ensured that rest of the permission (required to start SQL Service) was already granted for this non sys admin SQL Service account.
Reference(s):
How to enable SSL encryption for an instance of SQL Server by using Microsoft Management Console https://support.microsoft.com/kb/316898
How to enable SSL encryption for SQL Server 2000 if you have a valid Certificate Server https://support.microsoft.com/kb/276553
How SQL Server uses a certificate when the Force Protocol Encryption option is turned on https://support.microsoft.com/default.aspx?scid=kb;en-us;318605
Configuring Certificate for Use by SSL https://technet.microsoft.com/en-us/library/ms186362(SQL.90).aspx
Gaurav Srivastava.
SE, Microsoft SQL Server
Reviewed By:
Nickson Dicson, Amit Banerjee & Balmukund Lakhani.
TL, SQL Support