It’s late afternoon and I just got a very interesting assignment. A client of mine wants to connect to their Microsoft SQL Server over a public network without a VPN, and he asks me how we can best secure this connection. The first thing that crossed my mind was encryption!
So I told him that let’s enable encryption of traffic going through port 1433 so that we can minimize this giant loophole, which is directly related to accessing Microsoft SQL Server over the public network.
Now, to be able to even start the encryption setup process, we first need the most important of all: a certificate that has the following extended key usage property: Server Authentication 1.3.6.1.5.5.7.3.1.
Therefore, when deploying a self-signed certificate or buying a legitimate one, we need to additionally make sure that the key usage string is exactly the same as above. Now, 99% of the time, a self-signed certificate is sufficient for encryption, but you can get a valid one from a trusted vendor.
Ok, now that I’ve made sure I have the correct certificate, let’s go in and tell SQL which certificate to use. To do this, we need to copy the certificate fingerprint and enter the following registry key:
HKLM SOFTWARE Microsoft Microsoft SQL Server MSSQL14.MSSQLSERVER MSSQLSERVER SuperSocketNetLib
This particular key has a value called Certificate. We need to copy and paste the thumbprint of the certificate to this value.
In my case it will look something like this:
Now let’s go into SQL Server Configuration Manager, then go to SQL Server Network Configuration, under Protocols for MSSQLSERVER, go to Properties and then to Certificate tab. Here we will see the newly deployed certificate in the registry. All that remains is to select the certificate and restart the SQL Server service.
We will then move on to enforce protocol encryption by right-clicking on the native SQL client configuration and making sure the Forced Encryption option is set to Yes.
Now that we have configured the certificate, let’s go back to SQL Server services and restart the service by right-clicking on the SQL Server service (MSSQLSERVER) and choosing Restart.
Voila, we have now configured port 1433 to encrypt communications!
The next step is to configure encrypted connections in our application or in case of connecting from SQL Server Management Studio.
To do this in SSMS, we need to follow these steps:
- Click Connect to Database Engine.
- In the Connect to Server dialog box, go to Options
- On the Connection Properties tab, click Encrypt Connection.
That’s it, pretty simple and straightforward. I hope you enjoyed this article and hope you come back for more interesting articles.