Encrypted Connection on Sql Server 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 &amp; 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.
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