How to Create and Install a Self-Signed SSL/TLS Certificate for SQL Server

by Philipp Stiefel, originally published May 18 2020, last updated May 18 2020

Photo by Mauro Sbicego, used here under CC0 licensing

Once again, I just wasted several hours to figure out how to create and install a self-signed SSL/TLS certificate to encrypt the connection to a Microsoft SQL Server instance for development purposes. So, I decided to write down the relevant steps, primarily for my own future reference. If I can help you to with that matter, all the better.

Preface / Warning

Please note, I’ve neither the time nor the expertise to write a full-fledged tutorial on securing SQL Server connections.

Please also note, as discussed in the GDPR Whitepaper for Software Developers I co-authored, development servers should not contain a copy of production data regarding personal information and/or other sensitive data.

You should generally know what you are doing (e.g. what an SSL/TLS is, what a certificate is and what a Certification Authority (CA) is.) before following this guide. A self-signed certificate technically provides the same cryptographic encryption strength as an expensive certificate issued by a reputable Certification Authority. However, it does not provide security regarding the identity of the server side of your connection. Either distributing and installing your self-signed certificate, or the issuing CA certificates to multiple computers or disabling client-side certificate verification may increase the risk of compromise of data while at the same time creating a false sense of security.

You should not use self-signed certificates to secure your production SQL Server.

Of course, if you purchased a real CA-issued certificate, you can also follow along the steps of this text, just omit the next paragraph and all instructions to circumvent problems with the untrusted CA.

Create the Certificate

There are several tools and methods to create a self-signed certificate. I tried a lot! In the end I frequently have to resort to OpenSSL to convert, export, or otherwise overcome some limitations of originally intended/used tool or method. Now, I think, it makes sense to start with OpenSSL right away. So, step one: Download the OpenSSL Windows binaries and install them on your computer.

Open the OpenSSL Config file (openssl.cfg) in the \bin subfolder of your OpenSSL program folder.

Add the setting:

[server_ssl]
extendedKeyUsage = serverAuth

at the end of the configuration file.

Then run OpenSSL with this command line:

openssl req -x509 -newkey rsa:4096 -keyout C:\outputdir\yourkey.pem -out C:\outputdir\yourcert.pem -days 365 -extensions server_ssl

Then extension “server_ssl” references the lines added to the config file. This creates a certificate for the purpose of Server Authentication and is required for SQL Server certificate.

You will be prompted for several attributes of the certificate. Most of them are optional and you can enter whatever suits you. However, pay attention to the common name (CN) of the certificate. This must be the computer name (the local Windows computer name, not DNS name) of the SQL Server computer. Also memorize the password / keyphrase you enter for the certificate. You are going to need that later.

When complete, OpenSSL has generated a certificate file (yourcert.pem) and a key file (yourkey.pem) in the outputdir.

Unfortunately, Windows and SQL Server do not like the PEM file format. We need to convert this to PFX (Personal Information Exchange) format.

This command line should do the trick:

openssl pkcs12 -export -out C:\outputdir\yourcert.pfx -inkey C:\outputdir\yourkey.pem -in C:\outputdir\yourcert.pem

This will export the certificate in PFX format (yourcert.pfx) in the outputdir. This contains the certificate and the key!

OpenSSL may show a warning “unable to write ‘random state’”. This does not block the export of your certificate.

You can now copy the yourcert.pfx file to the SQL Server computer.

Install the Certificate on the Server Computer

On the server computer run mmc.exe (Microsoft Management Console).

In the File menu, click “Add/Remove Snap-in”. In the dialog select the “Certificates” Snap-in and add it to selected snap-ins, select “Computer account”, click “Next”, select “Local computer”, and click “Finish”. Finally click “OK” in the “Add/Remove Snap-in” dialog.

Navigate to the “Personal” store in the tree view hierarchy. Right-click on that node and select “All Tasks\Import” from the context menu. Now select your certificate file and step through the import wizard. Except for the password all input is optional. - I recommend you leave the default values unchanged.

This certificate is potentially marked as invalid because the Certification Authority (CA) that issued that certificate is not trusted (this was you on your own computer). As a brute-force solution you can copy this certificate to the “Trusted Root Certification Authorities” folder. – This implies some risk. The development server will now trust all certificates created on your own computer!

Install the Certificate in SQL Server

Run the “SQL Server XXXX Configuration Manager” utility (XXXX is the SQL Server version number).

Expand the node “SQL Server Network Configuration”, select the entry “Protocols for YourInstanceName”. Right click and select “Properties” from the context menu.

In the “Protocols for YourInstanceName Properties”-dialog go to the tab “Certificate”.

You can now select the certificate you just imported in the previous paragraph. If the certificate is not displayed in the drop-down list, it was not imported into the correct certificate store or the certificate is not trusted (see Root CA above).

You can now switch to the “Flags” tab in the dialog and enable “Force Encryption”. This is optional, but if you don’t force encrypted connections it’s up to each client whether he connects with or without encryption. That’s usually not what you want, after you went to the lengths of enabling SSL/TLS connection security.

Now you need to restart the SQL Server service for these changes to take effect. If there is an error starting the service, there is most likely some problem with the certificate – The displayed error message is completely useless. Look into the SQL Server log files. There is usually a better error message in there. The problem I had frequently were that the common name (CN) in the certificate was not matching the computer name exactly or that the certificate did not explicitly had the Server Authentication purpose declared.

If you cannot fix the issue with the certificate right away, you can “Clear” out the certificate in the “Protocols for YourInstanceName Properties”-dialog, restart the server without encryption, and fight this fight another day.

Verify Connection Encryption

If your SQL Server restarted successfully with the certificate in place your server now supports SSL/TLS encrypted connections. But don’t just take my word for it, check for yourself.

You can now connect with SQL Server Management Studio and run this query:

SELECT session_id, net_transport, encrypt_option, auth_scheme, client_net_address FROM sys.dm_exec_connections

(The query requires “VIEW SERVER_STATE” permission usually only granted to sysadmin logins.)

The encrypt_option column will contain TRUE or FALSE indicating whether the connection is encrypted or not.

Trust the Self-Signed Certificate

Depending on the client software and settings, clients will now throw an error instead of connecting to the SQL Server. The error message will be along the lines of “The certificate chain was issued by an authority that is not trusted”. This happens because no one trusts your certificate. It does not guarantee that the counter party of the connection (your SQL Server) is actually who it claims it is.

To ignore the risk and the error message, you can add TrustServerCertificate=True to your connection string.

All Done

So, this should be it. I hope this is helpful and I did not miss any step along the way.

Share this article: