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.
To register a certificate, use the following command:
httpcfg set ssl /i IP:Port /h Hash /g Guid
Arguments
- IP:Port
IP address and port combination for which you are registering the certificate.
- Hash
Certificate hash.
NoteThe 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}"
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 -n CN="MySQLServer" -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localmachine
-sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12
Command-line Switches
Additional Command-line Switches
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.
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).
