To create a certificate for SSL encryption for SQL Server you need a certificate for Server authentciation. That is [EnhancedKeyUsageExtension]
OID = 1.3.6.1.5.5.7.3.1
If you want to manually create a certificate for this using Windows Certificate services you need to do it like this.
(http://blogs.technet.com/b/pki/archive/2009/08/05/how-to-create-a-web-server-ssl-certificate-manually.aspx)
Create a file named cert.inf
[NewRequest]
Subject = "CN=*FQDN*"
HashAlgorithm = SHA256
KeyLength = 2048
Exportable = TRUE
KeySpec = 1
KeyUsage = 0x20
MachineKeySet = TRUE
ProviderName = "Microsoft RSA SChannel Cryptographic Provider"
ProviderType = 12
[EnhancedKeyUsageExtension]
OID = 1.3.6.1.5.5.7.3.1
[RequestAttributes]
CertificateTemplate= WebServer
And then generate the certificate using CERTREG.EXE
Certreq –new cert.inf cert.req
Certreq –submit cert.reg
To do the same using OpenSSL
# generate key
openssl genrsa -des3 -out server.key 2048
# remove pass
openssl rsa -in server.key -out server.key
# generate sign request, be sure to include the correct FQDN
# (host name followed by primary dns suffix)
openssl req -new -key server.key -out server.csr
# generate self signed certificate
openssl x509 -req -in server.csr -signkey server.key -out server.crt
# include both the certificate and the private key in a PKCS12 keystore
# (leave the export key empty)
openssl pkcs12 -export -out server.p12 -inkey server.key -in server.crt
In both cases you end up with a CRT that you can import to the machine store of the SQL Server and then use the SQL server configuration manager to encrypt the connections. You will have to trust the root certificate in the latter case though and in the former case, if you are not running on a domain you will have to install the root certificate for the windows CSA on both the machines
