Sql-server – SQL Server 2008 encrypted connection using multiple server names

Note that when you force encryption on the server side, the connection will be encrypted by using ssl. You can use any certficate for this. Your connection will be encrypted, However the client will not check if the identity of the SQL Server is in fact valid. Without this check you could be open for a man in the middle attack.

If you choose force encryption on the client side, a full validity check will be performed. That way not only is the connection encrypted, also the client knows that the server it is connection to, is in fact the server mentioned in the certificate because it can trust that the certificate has not been tampered with.

For this to work, the certificate used on the SQL Server must be issued by a certificate authority that the client trust. This can be any well known certificate authority or for example when you have a windows domain and a CA in that domain, you can have that CA issue one. You then have to import the root certificate of that CA into all clients and that will work too.

to check if your connection is encrypted use the following:

SELECT session_id,encrypt_option
FROM sys.dm_exec_connections

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