Enable TLS on sql server: the opennssl way

To do so you have to get a certificate meeting this requirements. The most important of them being:

• The Common Name (CN) in the Subject or a DNS name in the Subject Alternate Name (SAN) of the certificate exactly matches the serverName value specified in the connection string or, if specified, the hostNameInCertificate property value.

The exact name depends on being on a domain or not.

For our example one uses the following configuration file (named san.cnf):

[req]
distinguished_name = req_distinguished_name
x509_extensions = v3_req
prompt = no
[req_distinguished_name]
CN = MYCOMPUTERNAME
[v3_req]
keyUsage = keyEncipherment,dataEncipherment
extendedKeyUsage = serverAuth
subjectAltName = @alt_names
[alt_names]
DNS.1 = MYCOMPUTERNAME
DNS.2 = MYCOMPUTERNAME.MYBUSINESSDOMAIN.MYSBUSINESSDOMAINTLD

Then one issues the following commands:

openssl req -x509 -newkey rsa:4096 -sha256 -keyout tlssql.key -out tlssql.crt -days 600 -config san.cnf

Then:

openssl pkcs12 -export -name “tls for sql by openssl” -out tlssql.pfx -inkey tlssql.key -in tlssql.crt

Then you can import the pfx file in your favorite hive. In my case I use IIS Manager to import the pfx file in the personnal hive.

Your "tls for sql by openssl" named certificcate is now available in the Sql Server Configuration Manager.

Et voilà...

A little further:

You may want to use an mmc console with the certificats component to browse among the known certificates. In such a case you will have the choice between:

• User,
• Service, or
• Computer

In my experience the computer store is available for sql server running as a user account member of the users group.