How to create certificate to use with encrypted connection?

Question

There are numerous articles on MSDN (for example Encrypting Connections), which explain how to make a SQL connection encrypted by using a certificate. Unfortunately they do not provide enough information on how to create the certificate itself.

So the question is - how to create a self signed certificate in Windows 7, which can be used by SQL encrypted connection? What tools to use for this, is it makecert? And what parameters to supply?

So far I tried to create a certificate within SQL Server itself by using CREATE CERTIFICATE statement and then saving this certificate to a file with BACKUP CERTIFICATE statement. It did not work though. I was able to install this certificate using MMC on the server, but later on if I go to SQL Configuartion Manager -> SQL Server Network Configuration -> Properties I can't add this certificate as it does not show up in the list. Probably the certificate created this way does not match requirements for the certificate, which can be used to encrypt the SQL connection.

So how to make a right one?

Answer 1

Having just gone through this process with a certificate from a CA on a SQL 2012 Enterprise server I totally agree this is one of the most POORLY documented processes in this product. 

Now given that my advice is for a certificate from a CA here's the help I can offer:

1) If the certificate isn't showing up in the list, then there's something wrong along the creation process chain.  Set the SQL server to force encription (even though you don't see the cert in the list) and reboot the server.  Once the server comes back up look in the logs.  The error messages thrown will help you localize the problem.  Take the error message generated and look it up in Google. 

2) With the cert creation, I found that I had to create a domain level SQL service account, set all the SQL services to this account, and then use this account to create the CSR and then install the certificate (both via the MMC) locally on the server.

3) The certificate has to be one designed for server authentication only. 

I hope this helps somewhat.

Answer 2

.... and then use this account to create the CSR ....

Thank you Joe.

But can you please provide some details on how exactly you created it? What tools did you use? What steps you completed?

Answer 3

I think there are 3 issues here.

1. Self-signed certificate. SQL Server already uses one of these if you don't set up your own certificate. You don't have to do anything to use it. A self-signed certificate is vulnerable to a man-in-the-middle attack. It's is better than nothing, but not robust protection. I don't see any advantage to creating your own self-signed certificate.

2. To create a certificate, start here Setting Up a Certificate Authority http://msdn.microsoft.com/en-us/library/windows/desktop/ms755466(v=vs.85).aspx I know that's an old topic, but the general idea, is that you install the optional Certificate Authority component on a Windows Server, and then you can start creating certificates. This is really a Windows subject, so questions about this might get a better response on a Windows forum.

3. Installing the certificate on the SQL Server computer and then configuring SQL Server to use it is described at Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager) http://msdn.microsoft.com/en-us/library/ms191192.aspx The usual problem is that the certificates are stored in the Windows certificate store for each login, so you have to be using the correct account when you do this. Excerpt from the topic:

Certificates are stored locally for the users on the computer. To install a certificate for use by SQL Server, you must be running SQL Server Configuration Manager under the same user account as the SQL Server service unless the service is running as LocalSystem, NetworkService, or LocalService, in which case you may use an administrative account.

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 4

.... and then use this account to create the CSR ....

Thank you Joe.

But can you please provide some details on how exactly you created it? What tools did you use? What steps you completed?

I used the MMC to create the CSR. 

Login to your server under the SQL Server service account credentials.  I temporarily added the account to the local admins group until I completed and verified the certificate was installed correctly and then removed the certificate from the local admins group.

Click Start -> Run

In the space provided type 'MMC'.

Click File - Add/Remove Snap-In

Select 'Certificates' then 'Computer Account' then Local Computer

As for the specific Certificate creation process, you'll have to contact your internal IT support desk for their policies on cert creation. When asking make sure you say you need a certificate for Server Authentication and not an IIS server. They can help you from there.

Here are some articles I leveraged for my troubleshooting direction.  There's a bunch more on Google.  Search on any error codes and it should help you along.

http://thesqldude.com/2012/04/21/setting-up-ssl-encryption-for-sql-server-using-certificates-issues-tips-tricks/

http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/19c10862-7b1e-4dda-9bf8-0cf7aa95ed24

http://henrikonsharepoint.blogspot.com/2011/01/quick-guide-to-enable-sql-for-ssl.html

http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/9e5a780b-94ae-42d0-a295-9e239b4ba534/

http://social.msdn.microsoft.com/Forums/en/sqlgetstarted/thread/9b29f5e8-67fc-40d1-9bde-1521cbdfe07e

http://social.msdn.microsoft.com/Forums/en-GB/sqlsecurity/thread/f195f583-fe8b-4171-97e1-32f3e7ba780f

Answer 5

2. To create a certificate, start here Setting Up a Certificate Authority http://msdn.microsoft.com/en-us/library/windows/desktop/ms755466(v=vs.85).aspx

Thank you so much Rick. This is probably really what should be done.

But it brings up another question. This approach works on Windows Server. What about Windows 7 ? So far I was not able to find any "Certificate Services" there and set up a "Certificate Authority" as suggested in the referenced article. 

So if I am running SQL 2012 Developer's edition on Windows 7 do I have any option of creating a self signed security certificate (just for development purposes) or is this functionality only available in Windows Servers operating systems?

Answer 6

Creating certificates is a server thing. A certificate is only as good as the reputation of the creator. For example if I create a certificate, why would you trust it. But if Microsoft or VeriSign creates a certificate, you trust them, and the reason you trust them, is that you can check with them and they will tell you if the certificate is the one they created, or if it's fake. That's why the SQL Server Configuration Manager for the clients has a checkbox "Trust Server Certificate". From help:

• When set to No, the client process attempts to validate the server certificate. The client and server must have each have a certificate issues from a public certification authority. If the certificate is not present on the client computer, or if the validation of the certificate fails, the connection is terminated.
• When set to Yes, the client does not validate the server certificate, thereby enabling the use of a self-signed certificate

An important part of being a Certificate Authority (CA) is the service that will stand ready to confirm or deny the validity of the certificate. That's why it's a server thing. Normally a company "XYZ company" will have a CA that can be accessed from any client on their network. It might or might not be available for the general public. If you need a public CA, there are several companies that can issue publicly verifiable certificates.

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 7

Creating certificates is a server thing. A certificate is only as good as the reputation of the creator. For example if I create a certificate, why would you trust it. But if Microsoft or VeriSign creates a certificate, you trust them...

Thank you. I understand about the importance of trust to certificates.

But how do we create them in a development environment? This is where it is not having a trust to this particular certificate what is most important but more an ability to check that system is built properly and that code is working.

So once again, is there any way to create a security certificate for development purposes in Windows 7, SQL 2012 Developer's edition environment (without purchasing it from VeriSign, etc.)?

Answer 8

You might be interested in http://social.msdn.microsoft.com/Forums/en-US/wcf/thread/4f9d19f0-b0e4-4e88-80f5-5a27ce9abd14 I don't have an environment available to test that however. I don't know how the trust cert can be confirmed. Sorry.

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty