locked
SSL sql server permission RRS feed

  • Question

  • 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.

    Wednesday, March 14, 2007 11:21 PM

Answers

  • 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 IPStick out tongueort 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

     
     
      bLog 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 IPStick out tongueort 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 = 0x20
    MachineKeySet = 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.
     
     
    ***************************
    Friday, July 6, 2007 3:03 PM

All replies

  • 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.

    Thursday, March 15, 2007 1:54 AM
  • 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.

    Saturday, March 17, 2007 5:23 PM
  • 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.

    Tuesday, July 3, 2007 3:23 PM
  •   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

    Tuesday, July 3, 2007 5:59 PM
  • 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 IPStick out tongueort 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

     
     
      bLog 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 IPStick out tongueort 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 = 0x20
    MachineKeySet = 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.
     
     
    ***************************
    Friday, July 6, 2007 3:03 PM
  • 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

    Monday, July 16, 2007 2:51 PM
  • Michael,

     

    Thank you so much for these instructions. This is great!  I've been strugggling with this for days!  I ran into the same exact error message you did, which lead me to this post.  Prior to getting the error message and googling it, it seemed impossible to find any instruction on how to make this work.

     

    I have a question regarding your instructions.  You have a  Path 1. Service Account certificate store and a Part 2. Machine Certificate Store.  I'm assuming I only do one of these?  My SQL Service Account is not a Local Administrator so would I want to do Part 2?  Did you set this up with a local admin service account?

     

    Thank you for doing this.  This is such a great help!!!

     

    Ann

     

     

    Tuesday, February 26, 2008 4:46 PM
  • Hi,
    When i run any application in windows mobile 6.1 it throws "The File cannot be opened. Either it is not signed with a trusted certificate, or one of its components cannot be found...:". How to solve this issue. I can not test my c# project in device.

    Also is there is any way to implement TLS/SSL in windows mobile 5. My server will send me certificate when i connect.
    I am new to c#  so if anybody give me ideas in steps that will be much helpful.

    Thanks,
    Sunday, November 9, 2008 3:08 AM
  • Another solution to this problem could be to just allow the service account running SQL Server to have permission to read the private key of the SSL certificate.  This problem can be resolved by following the instructions outlined <a href="http://nickstips.wordpress.com/2010/09/08/sql-ssl-and-sql-server-2008-service-doesnt-start-error-code-2146885628/">here</a>.
    Nick's Programming Tips
    Wednesday, September 8, 2010 10:45 PM
  • Hi All,

    I realise this is quite an old thread, but it's still the top result in google for the OP's quoted error message. All the posts have been really useful and they put me on the right track so I thought I would add the extra info that I found if you're having this problem on Server 2008.

    My scenario was the same - a SQL service account with admin rights on the server failing to start when trying to use SSL. Turns out that with Server 2008 you don't have to use winhttpcfg.exe, you can do it through the GUI. Just run MMC, add the Certificates snap-in for the local machine account and navigate the tree to 'Personal - Certificates'. Right click on the certificate you're trying to use and select 'All Tasks - Manage Private Keys'. A standard Windows permissions editing window will pop up. Use this to grant read rights to your SQL service account, and hey presto - you should now be able to configure your SQL instance to use the certificate.

    Friday, February 15, 2013 9:45 PM
  • Hello,

    Thank you very much for your post.This saved me a lot of trouble.

    I had a problem with SCCM 2012 self-signed certificate which was not recognized by SQL Server 2012 service after restart.The workaround was based on the configuration steps specified by your post with some differences:

    1.The certificate was issued using the server certificate mmc snap-in for the computer store by requesting it(based on the Computer certificate template) to the Enterprise internal Windows Server 2012 CA.

    2.For Windows 2008/20012 operating system the service certificate binding is activated by using netsh through the  syntax:

    netsh http add sslcert ipport=0.0.0.0:1433 certhash=0000000000003ed9cd0c315bbb6dc1c08da5e6 appid={00112233-4455-6677-8899-AABBCCDDEEFF} 

    The certificate thumbprint will be used and a random appid just like in your post.

    3.There was no need to to grant the rights for the sql service account for the certificate container as any member of the domain users/local computer users groups have read rights to the certificates in the computer container.

    Hope it helps!

    Corvin

    Friday, May 24, 2013 10:26 AM
  • Server 2012 with Local Server 2012 and my issues started after installing SCCM 2012 (SCVMM was already installed) and rebooting..

    I am not using any 3rd party certs.. the only cert in Personal store are the local ones. Regardless, I am not having much luck.. I have three personal certs for Computer Account.

    servername.domain.local
    localhost
    WMSvc-servername

    SQL is attempting to use servername.domain.local according to the hash in the errorlog.

    If I try the netsh command Corvin posted and I get:

    SSL Certificate add failed, Error: 183
    Cannot create a file when that file already exists.

    If I try the netsh command on the hash associated with the localhost cert the command runs fine, but SQL still attempts to bind to servername.domain.local.

    I did notice there is no private key on the servername.domain.local cert which might explain my issues.. not sure why that would be though.

    I also added the servername.domain.local cert to the Personal store when logged in as the service account.. no luck.

    Wednesday, July 17, 2013 3:34 PM