I am attempting to setup SSL with SQL Server 2005. I do not wish to have the domain account that is running the SQL Server Service be an administrator. What rights/permissions does this account require in order to start sql server with an SSL certificate?
I have tried putting the certificate in the local store but you need to be an admin to see it there. If I put the certificate in SQL Server Service account's personal store, I can't start sql server. "The server could not load the certificate it needs to initiate an SSL connection. It returned the following error: 0x8009030d. Check certificates to make sure they are valid." Is the error I get.
Thank you for your help.
Hi,
Try putting the cert in the SQL Server service account's local store. Upon start, SQL Server will search the machine store and then the user's store. Also, make sure that you chose a certificate through the SQL Server Configuration Manager. If it doesn't work, please provide the complete error message from the error log.
Il-Sung.
|||Sorry not to be clear, the cert is in the SQL Server service account's local store.
2007-03-13 14:52:25.12 Server Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
Feb 9 2007 22:47:07
Copyright (c) 1988-2005 Microsoft Corporation
Express Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
2007-03-13 14:52:25.12 Server (c) 2005 Microsoft Corporation.
2007-03-13 14:52:25.12 Server All rights reserved.
2007-03-13 14:52:25.12 Server Server process ID is 1304.
2007-03-13 14:52:25.12 Server Authentication mode is MIXED.
2007-03-13 14:52:25.12 Server Logging SQL Server messages in file 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2007-03-13 14:52:25.12 Server This instance of SQL Server last reported using a process ID of 2000 at 3/13/2007 2:52:18 PM (local) 3/13/2007 7:52:18 PM (UTC). This is an informational message only; no user action is required.
2007-03-13 14:52:25.12 Server Registry startup parameters:
2007-03-13 14:52:25.12 Server -d c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2007-03-13 14:52:25.12 Server -e c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2007-03-13 14:52:25.12 Server -l c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2007-03-13 14:52:25.13 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2007-03-13 14:52:25.13 Server Detected 1 CPUs. This is an informational message; no user action is required.
2007-03-13 14:52:25.23 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2007-03-13 14:52:25.26 Server Database mirroring has been enabled on this instance of SQL Server.
2007-03-13 14:52:25.26 spid5s Starting up database 'master'.
2007-03-13 14:52:25.67 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2007-03-13 14:52:25.84 spid5s SQL Trace ID 1 was started by login "sa".
2007-03-13 14:52:25.88 spid5s Starting up database 'mssqlsystemresource'.
2007-03-13 14:52:25.93 spid5s The resource database build version is 9.00.3042. This is an informational message only. No user action is required.
2007-03-13 14:52:26.46 spid5s Server name is 'VMLWDAW02APP02\SQLEXPRESS'. This is an informational message only. No user action is required.
2007-03-13 14:52:26.46 spid8s Starting up database 'model'.
2007-03-13 14:52:26.46 spid5s Starting up database 'msdb'.
2007-03-13 14:52:26.88 spid8s Clearing tempdb database.
2007-03-13 14:52:27.40 spid8s Starting up database 'tempdb'.
2007-03-13 14:52:27.70 spid5s Recovery is complete. This is an informational message only. No user action is required.
2007-03-13 14:52:27.70 spid11s The Service Broker protocol transport is disabled or not configured.
2007-03-13 14:52:27.70 spid11s The Database Mirroring protocol transport is disabled or not configured.
2007-03-13 14:52:27.73 spid11s Service Broker manager has started.
2007-03-13 14:52:33.10 Server The server could not load the certificate it needs to initiate an SSL connection. It returned the following error: 0x8009030d. Check certificates to make sure they are valid.
2007-03-13 14:52:33.10 Server Error: 26014, Severity: 16, State: 1.
2007-03-13 14:52:33.10 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.
2007-03-13 14:52:33.10 Server Error: 17182, Severity: 16, State: 1.
2007-03-13 14:52:33.10 Server TDSSNIClient initialization failed with error 0x80092004, status code 0x80.
2007-03-13 14:52:33.10 Server Error: 17182, Severity: 16, State: 1.
2007-03-13 14:52:33.10 Server TDSSNIClient initialization failed with error 0x80092004, status code 0x1.
2007-03-13 14:52:33.10 Server Error: 17826, Severity: 18, State: 3.
2007-03-13 14:52:33.10 Server 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.
2007-03-13 14:52:33.10 Server Error: 17120, Severity: 16, State: 1.
2007-03-13 14:52:33.10 Server SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
I am also having the same problem. I have followed every documentation source mentioned in these forums, MSDN, KB articles and BOL. Is SQL Server not compatible with commercial certificates, and requires MS Certificate Services? VeriSign support has assured me they have no knowledge of using their certificates with SQL Server. By chance, is there something missing in documentation regarding "master service key"? Or administrator permissions for the service account? Please don't limit responses to just these questions- I'm insterested in getting SSL to work no matter the reason. Just trying to get creative juices flowing.
I am using a VeriSign trial certificate. It has not expired. I have installed the CA certificates to trusted store, and they all verify properly using certutil.exe -verify. They are below, but you can download them from their site.
Root trial certificate:
--BEGIN CERTIFICATE--
MIICmDCCAgECECCol67bggLewTagTia9h3MwDQYJKoZIhvcNAQECBQAwgYwxCzAJ
BgNVBAYTAlVTMRcwFQYDVQQKEw5WZXJpU2lnbiwgSW5jLjEwMC4GA1UECxMnRm9y
IFRlc3QgUHVycG9zZXMgT25seS4gIE5vIGFzc3VyYW5jZXMuMTIwMAYDVQQDEylW
ZXJpU2lnbiBUcmlhbCBTZWN1cmUgU2VydmVyIFRlc3QgUm9vdCBDQTAeFw0wNTAy
MDkwMDAwMDBaFw0yNTAyMDgyMzU5NTlaMIGMMQswCQYDVQQGEwJVUzEXMBUGA1UE
ChMOVmVyaVNpZ24sIEluYy4xMDAuBgNVBAsTJ0ZvciBUZXN0IFB1cnBvc2VzIE9u
bHkuICBObyBhc3N1cmFuY2VzLjEyMDAGA1UEAxMpVmVyaVNpZ24gVHJpYWwgU2Vj
dXJlIFNlcnZlciBUZXN0IFJvb3QgQ0EwgZ8wDQYJKoZIhvcNAQEBBQADgY0AMIGJ
AoGBAJ8h98U7klaZH5cEn6CSEKmGWVBsTwHIaMAAVqGqCUn7Q9C10sEOIHBznyLy
eSDjMs5M1nC/iAA7KCASf/yHz0AdlU+1IRSijwHTF/2dYSoTTxP2GCmtL1Ga4i7+
zDDo086V7+NiFAGJj+CYey47ue4Xa33o/4YOA9PGL87oqFe7AgMBAAEwDQYJKoZI
hvcNAQECBQADgYEAOq447rP5EDqFEl3vhLhgTbnyaskNYwPvxk+0grnQyDA4sF/q
gK8nFlnvLmAOF3DmfuqW6WSr4zqTYzpwmJlsn48Om/yWirL8GuWRftit2POxTfHS
B8VmR+PZx2k24UgWUZyojDGxJtiHd3tjCdqFgTit4NK429cWOcZrh47xeOI=
--END CERTIFICATE--Intermediate certificate:
--BEGIN CERTIFICATE--
MIIEnDCCBAWgAwIBAgIQdTN9mrDhIzuuLX3kRpFi1DANBgkqhkiG9w0BAQUFADBf
MQswCQYDVQQGEwJVUzEXMBUGA1UEChMOVmVyaVNpZ24sIEluYy4xNzA1BgNVBAsT
LkNsYXNzIDMgUHVibGljIFByaW1hcnkgQ2VydGlmaWNhdGlvbiBBdXRob3JpdHkw
HhcNMDUwMTE5MDAwMDAwWhcNMTUwMTE4MjM1OTU5WjCBsDELMAkGA1UEBhMCVVMx
FzAVBgNVBAoTDlZlcmlTaWduLCBJbmMuMR8wHQYDVQQLExZWZXJpU2lnbiBUcnVz
dCBOZXR3b3JrMTswOQYDVQQLEzJUZXJtcyBvZiB1c2UgYXQgaHR0cHM6Ly93d3cu
dmVyaXNpZ24uY29tL3JwYSAoYykwNTEqMCgGA1UEAxMhVmVyaVNpZ24gQ2xhc3Mg
MyBTZWN1cmUgU2VydmVyIENBMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKC
AQEAlcMhEo5AxQ0BX3ZeZpTZcyxYGSK4yfx6OZAqd3J8HT732FXjr0LLhzAC3Fus
cOa4RLQrNeuT0hcFfstG1lxToDJRnXRkWPkMmgDqXkRJZHL0zRDihQr5NO6ziGap
paRa0A6Yf1gNK1K7hql+LvqySHyN2y1fAXWijQY7i7RhB8m+Ipn4G9G1V2YETTX0
kXGWtZkIJZuXyDrzILHdnpgMSmO3ps6wAc74k2rzDG6fsemEe4GYQeaB3D0s57Rr
4578CBbXs9W5ZhKZfG1xyE2+xw/j+zet1XWHIWuG0EQUWlR5OZZpVsm5Mc2JYVjh
2XYFBa33uQKvp/1HkaIiNFox0QIDAQABo4IBgTCCAX0wEgYDVR0TAQH/BAgwBgEB
/wIBADBEBgNVHSAEPTA7MDkGC2CGSAGG+EUBBxcDMCowKAYIKwYBBQUHAgEWHGh0
dHBzOi8vd3d3LnZlcmlzaWduLmNvbS9ycGEwMQYDVR0fBCowKDAmoCSgIoYgaHR0
cDovL2NybC52ZXJpc2lnbi5jb20vcGNhMy5jcmwwDgYDVR0PAQH/BAQDAgEGMBEG
CWCGSAGG+EIBAQQEAwIBBjApBgNVHREEIjAgpB4wHDEaMBgGA1UEAxMRQ2xhc3Mz
Q0EyMDQ4LTEtNDUwHQYDVR0OBBYEFG/sr6DdiqTv9SoQZy0/VYK81+8lMIGABgNV
HSMEeTB3oWOkYTBfMQswCQYDVQQGEwJVUzEXMBUGA1UEChMOVmVyaVNpZ24sIElu
Yy4xNzA1BgNVBAsTLkNsYXNzIDMgUHVibGljIFByaW1hcnkgQ2VydGlmaWNhdGlv
biBBdXRob3JpdHmCEHC65B0Q2Sk0tjjKewPMur8wDQYJKoZIhvcNAQEFBQADgYEA
w34IRl2RNs9n3Nenr6+4IsOLBHTTsWC85v63RBKBWzFzFGNWxnIu0RoDQ1w4ClBK
Tc3athmo9JkNr+P32PF1KGX2av6b9L1S2T/L2hbLpZ4ujmZSeD0m+v6UNohKlV4q
TBnvbvqCPy0D79YoszcYz0KyNCFkR9MgazpM3OYDkAw=
--END CERTIFICATE--
I generated the certificate requests using "certreq.exe -new config.inf", and have tried in both the service account personal store (by loggin in as the service account) and the computer store (MachineKeySet = TRUE in the .inf file). I used certreq.exe -accept and the issued certificate installs, and appears under proper store in each case (certificates mmc snap-in). Below is the inf file contents (commented-out machinekeyset for this particular instance, which was intended for the service account store):
[NewRequest]
Subject = "CN=name1.internaldomain.int;OU=SSL_Test1;O=Some Company Name;L=Eden Prairie;S=Minnesota;C=US"
KeySpec = 1
KeyUsage = 0x20
;MachineKeySet = TRUE
[EnhancedKeyUsageExtension]
OID = 1.3.6.1.5.5.7.3.1
I configured the server certificate using the configuration manager. Upon restart, I get the exact same message in SQL error logs.
2007-07-02 13:08:54.72 Server The server could not load the certificate it needs to initiate an SSL connection. It returned the following error: 0x8009030d. Check certificates to make sure they are valid.
2007-07-02 13:08:54.72 Server Error: 26014, Severity: 16, State: 1.
2007-07-02 13:08:54.72 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.
2007-07-02 13:08:54.74 Server Error: 17182, Severity: 16, State: 1.
2007-07-02 13:08:54.74 Server TDSSNIClient initialization failed with error 0x80092004, status code 0x80.
2007-07-02 13:08:54.74 Server Error: 17182, Severity: 16, State: 1.
2007-07-02 13:08:54.74 Server TDSSNIClient initialization failed with error 0x80092004, status code 0x1.
2007-07-02 13:08:54.74 Server Error: 17826, Severity: 18, State: 3.
2007-07-02 13:08:54.74 Server 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.
2007-07-02 13:08:54.74 Server Error: 17120, Severity: 16, State: 1.
2007-07-02 13:08:54.74 Server SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
I have also tried to use certutil.exe -verify on the server certificate issued by VeriSign. I get the following error ::
402.203.0: 0x80070057 (WIN32: 87): ..CertCli Version
LoadCert(Cert) returned ASN1 bad tag value met. 0x8009310b (ASN: 267)
CertUtil: -verify command FAILED: 0x8009310b (ASN: 267)
CertUtil: ASN1 bad tag value met.
301.3128.0: 0x8009310b (ASN: 267)
PLEASE PLEASE HELP.
|||As far as I understand, error code 0x8009030d should be related to an access error to the certificate store, typically caused by using a Windows account that doesn’t have privileges to access the certificate.
Just to double check, make sure you imported the certificate using the same account used for SQL Server Service. Also consider that you need to use a domain or local Windows account instead of system accounts such as local system or network service.
Regarding error 0x8009310b, this one seems to be a parsing error, “ASN.1 bad tag value met”, unfortunately I am not sure about this particular one. I would guess that if Verisign is using this tag and the certificate is valid, it is probably an optional tag that is restricted by default on Windows and probably there is a work around to lift the tag value restriction, but I am not sure about it. I would suggest consulting this one on the Windows (or probably a crypto) forum.
Regarding the question on SSL and the service master key (SMK); the SMK and SSL configuration are completely orthogonal. The SSL certificate will only be used for the communication channel (data in transit protection), while the SMK is used for data at rest protection. Please, if you have any question on the SMK that is not in BOL or in the forums let us know; the information could either be misplaced, be a bug in our documentation, or simply a specific area that we didn’t think about documenting. We will really appreciate your feedback on our documentation.
I hope this information helps.
-Raul Garcia
SDE/T
SQL Server Engine
|||I have it working!!! Thank you! I hope the following helps someone else out there!
This is meant to outline HOW TO request, install, and configure an SSL certificate without IIS or MS Certficate Services. If you have IIS installed, or Certificate Services - not sure if this How To will help you. There are certificate requirements such as key usage, key spec, and enhanced key usage mentioned in BOL - I'm not sure if IIS/CS request mechanisms address these requirements.
Useful utilities to have before you get started. These are found in three places, Windows Server 2003 Resource Kit, Windows Server 2003 admin pak, and Windows Server 2003 support utilities. All these are available for download, and I highly suggest downloading and installing the most recent versions.
certreq.exe - used to generate a certificate request without IIS or MS Certificate Services.
certutil.exe - used to obtain critical certificate information (hash) and verify installed certificates (i.e., root and intermediate CA certs).httpcfg.exe - obscure mention in BOL/MSDN2; bind SSL certficate to IP
ort via http.sys mechanism -- BOL should clearly state this is required, I missed it completely thinking it dealt only with HTTP endpoints. If it applies to HTTP(S) endpoints as well (as I suspect), this should be clearly documented under "CREATE ENDPOINT" topic in BOL.
winhttpcertcfg.exe - used to assign permissions to certificate store private keys if needed.
Let's get started!
Path 1. Service account certificate store.
I. a. Create the policy input file for certreq.exe. Paste the following into a text file, and save as ANSI encoding. Be sure to edit the subject line, and make sure that the CN= part is the fully qualified domain name found in "my computer | properties | name tab"; detailed syntax for certreq.exe is available on TechNet. Note that OU= and beyond is required by commercial CAs, and the syntax is burried in MSDN/Technet online somewhere. Search for "SSL"
[NewRequest]
Subject = "CN=name1.internaldomain.int;OU=SSL_SQL_001;O=Some Company Name;L=City Name;S=Minnesota;C=US"
KeySpec = 1
KeyUsage = 0x20[EnhancedKeyUsageExtension]
OID = 1.3.6.1.5.5.7.3.1
b. Log in to the server using the service account credentials.
c. From command line, execute with your file names:
certreq.exe -new policyfile outputfile
d. Open outputfile in notepad; copy-paste the exact contents into the CA application form. I understand that some vendors require the request file be emailed to them.
II. Install any root and/or intermediary certificates from the CA, if not already present (most commercial CAs are already there). You can use the certificates mmc snap-in to view and import certificates. These are readily available from the CA web site, and should be saved to a text file (.cer extension is customary). Important: do not add any spaces or extra lines. Paste only the part from --BEGIN CERTIFICATE-- through --END CERTIFICATE-- inclusive (this was not obvious to me, so I hope this helps someone else out there). If you are using VeriSign, at least their trial certificate, I know you will need the "test root ca" and "test intermediate ca" certificates. Note, I installed these root certs into the machine store, logged into the server as an administrator, since SQL service account is not an admin.
III. You will receive a text file or block of text in the body of an email from the CA. Save as text file to the server. Log in as the service account, and execute certreq.exe again:
certreq.exe -accept certfile
This installs the certficate and binds the certificate private key - public key pair. According to the certreq.exe documentation, you must install the certificate using this executable not the certificates mmc snap-in.
IV. Back to BOL documentation, "Configuring Certificate for Use by SSL". Still logged in as the service account, execute certutil.exe to obtain the newly installed certificate's hash (certutil.exe -store -user "MY"); note that you can also obtain the hash using the mmc snap-in. The hash will display with spaces, remove the spaces for the purposes of the next command, httpcfg.exe. You will also need a guid value for the SQL Server instance; personally, I use 'zero' for the default instance and increment by 1 for each named instance. [00000000-0000-0000-0000-000000000000 ... 00000000-0000-0000-0000-000000000001 ... etc.] Note "best practices" may be to truely generate a unique guid for each and every instance, or there may in fact be a real GUID for the instance, however, any GUID seems to work. Finally, you need the IP address and port the instance uses - not any special https port, but the actual IPort used. Execute, substituting your actual values:
httpcfg.exe set ssl /i 10.0.0.1:1433 /h 892ce19ec79e9d1108f2f370a2aac581f7de807b /g "{00000000-0000-0000-0000-000000000000}"
V. Run SQL Server Configuration Manager, per BOL instructions. The Protocols for <instance name> properties | certificate tab; the certificate should appear in the drop down box. The certificate name will be the CN name, unless you assign a friendly name using the mmc snap-in.
VI. Restart the SQL Server service(s). If starts, you are most likely successful. To confirm encrypted connection is working, connect via management studio specifying "encrypt connection" in the options. To double check, query encrypt_option, and auth_scheme from sys.dm_exec_connections. You should get "TRUE" and "KERBEROS" values.
Part 2. Machine Certificate Store.
Same as above, with following changes. This is useful if your SQL instance runs under builtin accounts, or if you prefer to keep the certificate stored service account independent.
Login as an administrator where above mentions "as service account" credentials.
The policy input file should consist of:
[NewRequest]
Subject = "CN=name1.internaldomain.int;OU=SSL_SQL_001;O=Some Company Name;L=City Name;S=Minnesota;C=US"
KeySpec = 1
KeyUsage = 0x20MachineKeySet = TRUE
[EnhancedKeyUsageExtension]
OID = 1.3.6.1.5.5.7.3.1
To obtain the hash of the accepted/installed certificate, the certutil.exe syntax is slightly different:
certutil.exe -store "MY"
Before restarting the SQL Server service(s), you will need to use winhttpcertcfg.exe to grant permissions to the service account for the certificate's private key in the machine store location. Of course, this assumes that the account is not a system administrator, which probably would not require this step. The complete syntax is available on MSDN2, and this utility is located in the windows resource kit. Replace the -a and -s argument values with appropriate account and subject substring values:
winhttpcertcfg.exe -g -c LOCAL_MACHINE\MY -a "LocalHostName\NetworkService" -s name1
Now restart SQL, and that should do the trick.
***************************
Further helpful documentation from Microsoft... would be (1) how to implement a "selfssl" certificate, and configure clients to trust that certificate. This would be very helpful to those of us that do not have the infrastructure resources for certificate services, and may just want to encrypt limited internal database traffic (like an internal accounting/payroll department). (2) detailed instructions on how to renew the certificate.
Dear Microsoft: It would also be nice to add information regarding IIS and Certificate Services, and offer guidance for Windows 2000. Then, please have a developer "wizard-ize" all this. It certainly couldn't take more than a week to have a utility posted under the feature pack heading- perhaps something that simply wrappers calls to these utilities. Of course, please don't hold up any development effort on SIMILAR TO or OVERLAPS predicates.
***************************
|||Additional information -- how to get around certficiate FQDN subject being different from the "public persona"... ran into this since the internal domain/FQND is not the same as what we needed to register with the certificate authority. Note thumbprint = hash.
Open the registry editor and add the thumbprint WITHOUT SPACE of the certificate into the following string key “Certificate” under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer\SuperSocketNetLib”
(mssql.x is your instance)
If you don't do this, SQL Server will look in the certificate stores for something matching the FQDN. Since it won't match the FQDN, it won't find anything and won't work.
mpls_mike/MichaelSmith
No comments:
Post a Comment