MS SQL Server using SSL Connection over the Web?

Question

I'm looking to have an external SQL Server 2008R2 Express instance and be able to connect to this via SSL using a 'sql.my-domain.com' address. So far, I've looked at a few Microsoft links on the subject and also a blog found on here (links towards the bottom), but I'm not having a great amount of luck with this.

My main issue appears to be getting the actual certificate to be recognised by the server. I have installed this into the MMC> Certificates location, applied permissions for the account which is running the SQL Express instance and I am still unable to even select the account when I am in SQL Configuration Manager and trying to apply the certificate to the instance.

I'm running SQL Express as this is needed as a proof-of-concept that we can get it working, but we'll be moving onto SQL Standard for the live environment.

Does anyone have an explanation in everyday terms, any knowledge of the pitfalls or any other web resources I can check out to try and get this service running?

EDIT - I need more than 10 rep to post all the links. Here's two I've used.

http://technet.microsoft.com/en-us/library/ms189067%28v=sql.105%29.aspx

http://thedataspecialist.wordpress.com/2013/03/12/using-a-self-signed-ssl-certificate-with-sql-server/

• Adam W.: I've made progress with the getting the certificate 'seen' by SQL Server. The basis on this appears to be having the server mentioned in the certificate somehow. Where I've previously planned on using a *.my-domain.com account to match the certificate, this doesn't appear to be accepted. Turning this into a self-signed certificate yields a result, which I then install this certificate onto the SQL Server and client. I'm currently facing an issue whereby the CN name is not recognised when I try to connect through SSMS. I'll update with progress on this...

Answer 1

According to Books Online, the certificate used for encrypting connections must meet the following requirements:

1. The certificate must be issued for Server Authentication
2. The name (common name or CN) on the certificate must match the full-qualified domain name used by clients to connect.

If those two conditions are not met, the certificate will not show in the drop-down listing. Additionally, the service account for SQL Server must have read access to the private key of the certificate. Without that right, you will be able to select the certificate, but the service will not start.

Since this is a test system you intend to use as proof-of-concept, a self-signed certificate should be adequate. I am not sure what language you are using for your client application, but this link provides options available for connection strings in .Net. You should review at least the following two options.

• Encrypt - Tells the client application to force encryption.
• TrustServerCertificate - Tells the client application to skip validating the certificate chain. This is important with a self-signed certificate as the chain likely will not be trusted.