Configuring Certificate for Use by SSL

Native XML Web Services is deprecated. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

For Secure Sockets Layer (SSL) communications, an HTTP server must have a certificate registered for each socket (IP address/port combination) that it is enabling for use with SSL. Certificates must be authorized for server authentication. You can do this by either obtaining an SSL certificate from a certificate-issuing authority, such as Verisign, or if for testing purposes, by using tools to issue and create a certificate yourself.

Regardless of whether a certificate is obtained or self-issued, it must be registered with the server. We recommend using the HTTP Configuration Utility (HttpCfg.exe) to register, query, and delete SSL certificates.

Registering SSL Certificates

To register a certificate, use the following command:

httpcfg set ssl /iIP:Port**/hHash/g**Guid

Arguments

• IP:Port
  IP address and port combination for which you are registering the certificate.

• Hash
  Certificate hash.

  Note

  The certificate hash can be obtained from the certificate store. This can be viewed by using the Certificates snap-in or a command line tool such as CertUtil.

• Guid
  GUID string identifying the entity registering the certificate. As a best practice, create one GUID for each instance of SQL Server and use that same GUID for all certificate registrations made by that instance.

Registering an SSL certificate for an IP:Port affects all applications that listen on that IP:Port. For example, if both the instance of SQL Server and another application, such as IIS, are listening on the same IP:Port (10.0.0.1:80), the instance of SQL Server that is registering a SSL certificate for 10.0.0.1:80 will affect IIS; and IIS and the instance of SQL Server will have to share use of the same common certificate. This is a limitation of the kernel-mode HTTP driver (Http.sys). When Http.sys receives a request on the IP:Port 10.0.0.1:80, because the request is encrypted, it cannot examine the URL to determine whether the request belongs to SQL Server or IIS. Https.sys can only route the request after it has decrypted it. Therefore, it cannot use a different SSL certificate for different applications listening on the same IP:Port.

If SQL Server is running on Windows Vista (or an equivalent Windows Server edition), you might have to specifically register the certificate for local IP address (IPv4: 127.0.0.1 or IPv6: [::1]), as well as the computer's external IP address, to enable "locahost" connectivity over SSL. Alternatively, you can specify the unspecified address (IPv4: 0.0.0.0 or IPv6: [::]) when registering the SSL certificate. Also, Windows Vista now supplies a built-in tool to help manage SSL certificate registrations instead of using httpcfg.exe tool; the new command is as follows:

netsh http add sslcert ipport=IP:Port certhash=Hash appid=Guid

For example:

netsh http add sslcert ipport=[::]:443 certhash=<hash> appid=<guid>Examples

The following example registers a certificate:

httpcfg set ssl /i 10.0.0.1:80 /h 2c8bfddf59a4a51a2a5b6186c22473108295624d 
/g "{2bb50d9c-7f6a-4d6f-873d-5aee7fb43290}"

Querying Certificates

To view all registered certificates, use the following command:

httpcfg query ssl

To view a single certificate, use the /i option:

httpcfg query ssl /iIP:Port

Arguments

• IP:Port
  IP address and port combination for which you are querying the certificate.

Example

httpcfg query ssl 
httpcfg query ssl /I 10.0.0.1:80

Deleting SSL Certificates

To delete a certificate, use the /i option as follows:

httpcfg delete ssl /iIP:Port

Arguments

• IP:Port
  IP address and port combination for which you are deleting the certificate.

Example

httpcfg delete ssl /i 10.0.0.1:80

Obtaining Certificates

You can obtain certificates that are required by SQL Server for SSL communications with Internet-based clients from a certificate authority such as Verisign.

However, for testing purposes, you can create a test certificate by using a tool named MakeCert.exe. MakeCert.exe is part of the .NET Framework SDK. MakeCert.ext is also available in the Platform SDK. To download the SDKs, go to the Microsoft .NET Framework Developer Center and the Microsoft Download Center, respectively. MakeCert.exe creates an X.509 certificate. It creates a public and private key pair for digital signatures and stores it in a certificate file. This tool also associates the key pair with a specified publisher and creates an X.509 certificate that binds a user-specified name to the public part of the key pair.

To create a SSL certificate for a server that responds to a host name (MySQLServer), you can execute MakeCert by using the following options:

makecert -r -pe -nCN="MySQLServer**"-eku1.3.6.1.5.5.7.3.1-ssmy-srlocalmachine**

-skyexchange-sp"Microsoft RSA SChannel Cryptographic Provider"-sy12

Command-line Switches

• -r
  Creates a self-signed certificate. A self-signed certificate is a certificate that is not signed by a certificate authority. Because it is not signed by a certificate authority, it can be used for encryption required in SSL but cannot be used for server authentication.

• -n
  Specifies the server name. This name must comply with the X.500 standard. The simplest method is to specify the name in double quotation marks, preceded by CN=.

• -eku
  Specifies a list of comma-separated, enhanced key usage object identifiers (OIDs) into the certificate. For SQL Server, an SSL certificate that is valid for server authentication that has an OID of 1.3.6.1.5.5.7.3.1 (szOID_PKIX_KP_SERVER_AUTH) is required.

• -ss
  Specifies the certificate store where the created certificate is saved. We recommend saving this in the my store, although it can be saved anywhere in the certificate store.

• -sr
  Specifies the certificate store where the certificate is located. Location can be either: currentuser (the default), or localmachine. Because this certificate is being created for a service, it should be placed in the local computer.

• -sky
  Specifies the certificate key type. This must be signature, exchange, or an integer, such as 4. For RSA public key exchange algorithm, exchange is required here. This is the type of key used to encrypt and decrypt session keys.

• -sp
  Specifies the CryptoAPI provider name. For certificates created for SQL Server, this can be set to Microsoft RSA SChannel Cryptographic Provider.

• -sy
  Specifies the CryptoAPI provider type. When the provider is Microsoft RSA SChannel Cryptographic Provider, this is 12.

Additional Command-line Switches

• -b
  Date value in mm/dd/yyyy format that specifies the start of the validity period for the certificate. The default for this is the creation date of the certificate.

• -e
  Date value in mm/dd/yyyy format that specifies the end of the validity period for the certificate. If not otherwise set, the default for this is 12/31/2039 11:59:59 GMT.

Examples

The following example shows creating a certificate by using MakeCert with the additional options.

makecert -r -pe -n "CN= MySQLServerName" -b 01/01/2000 -e 01/01/2036 
    -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine -sky exchange 
    -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12

After the certificate has been created, you can obtain its hash from the MMC of the certificate or by using a tool such as CertUtil. By using CertUtil, you can list the certificate that was just created.

C:\>certutil -store "my" "MySQLServerName"

================ Certificate 2 ================
Serial Number: e302d3a7a831c9884c0dd736f24825e6
Issuer: CN=MySQLServerName
Subject: CN=MySQLServerName
Signature matches Public Key
Root Certificate: Subject matches Issuer
Cert Hash(sha1): d2 2f 9a 7f 18 cb ed 13 a1 3e be e5 32 69 6c 4b ad ba b9 30
  Key Container = 956cbc46-f005-4aeb-b521-7c313f2ccd10
  Provider = Microsoft RSA SChannel Cryptographic Provider
Encryption test passed
CertUtil: -store command completed successfully.

The hash obtained from the results by running CertUtil can be passed to the Httpcfg.exe tool to register the self-signed certificate.

Before using MakeCert to create a self-signed SSL certificate and registering it with Http.sys, check to see whether the computer has IIS already installed. IIS provides a wizard that makes registration of SSL certificates easy. Therefore, if IIS is already installed on the computer, an SSL certificate may have already been registered with Http.sys. If not, you can use the IIS wizard to create and register the certificate.

Because SSL certificates have a computer-wide effect, it is not important that IIS is being used to register a certificate to be used by SQL Server.

Considerations

• When you uninstall an instance of SQL Server, any SSL certificate bindings created by using Httpcfg.exe will remain unless they are manually deleted. Because these settings were not created by using SQL Server Setup, they are not removed by it. Therefore, if an instance of SQL Server is uninstalled, such bindings should also be removed.

• Although using SSL over HTTP with SQL Server does not require IIS, after IIS is installed alongside an instance of SQL Server, the HTTP SSL service becomes linked to IIS usage. For example, when you stop IIS, such as by using net stop iisadmin at a command prompt, IIS also stops the HTTP SSL service. Additionally, after IIS is installed under Windows, you cannot start the HTTP SSL service without also starting IIS (Inetinfo.exe).

See Also

Reference

Registering Kerberos Service Principal Names by Using Http.sys