Encrypted Connection on Sql Server

Question

I want to make SQL server connection encrypted. I know that a certificate is needed, but I haven't been able to.

So far, I:

• created certificate in IIS.
• added certificate in MMC "Certificates" snap-in.
• in SQL Server Configuration manager for my server:
  • registered certificate
  • forced secure login
  • stopped & started "MSSQLSERVER" service

Starting SQL Server service always throws an error. Is there something wrong with the above steps? What are the correct steps?

I am using SQL Server 2012 and Windows Server 2012 R2.

• outis: When dealing with an error, please specify the error message in the question.

Answer 1

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

Answer 2

I noticed this question is a bit old, but if you (or anybody reading this) is still wondering how to implement SSL encryption here is a blog post I wrote recently describing my journey to implement and test this stuff on a cluster.

It is a pretty detailed post that described each and every step I had to do to implement SSL (together with certificates) for a request coming from my employer that I tackled very recently.

https://msurasky.wordpress.com/2017/02/27/encryption-in-transit/

Cheers!