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