Enable Encryption for Microsoft SQL Server Connections

It’s overdue within the afternoon and I’ve simply gained a maximum
fascinating activity. A consumer of mine desires to hook up with his Microsoft SQL Server
over the general public community, and not using a VPN, and he’s asking me how are we able to absolute best protected
this connection. First factor that popped up in my thoughts is encryption!

So I advised him let’s power encryption for visitors passing on
port 1433, thus we will mitigate up to imaginable this massive loophole which
is getting access to Microsoft SQL Server over the general public community without delay.

Now so that you could even get started the encryption configuration procedure, we first want a very powerful issues out of the entire others: a certificates that has the next Enhanced Key Usage Property: Server Authentication 1.3.6.1.5.5.7.3.1.

So when deploying a self signed certificates or purchasing a valid one, we want to make further positive the Key Usage string is precisely because the one said above. Now 99% of the time a self-signed certificates is sufficient for encryption, however you’ll be able to get a sound one from one of the depended on suppliers.

Ok, now that I’ve made positive I’ve the right kind certificates, let’s pass in and inform SQL which certificates to make use of. For that we want to replica the Certificate Thumbprint and put within the following registry key:

HKLMSOFTWAREMicrosoftMicrosoft SQL ServerMSSQL14.MSSQLSERVERMSSQLSERVERSuperSocketNetLib

In that exact key there’s a price referred to as Certificate. We want to replica and paste the certificates thumbprint into that price.

In my case it’s going to seem one thing like this:

Now let’s pass into the SQL Server Configuration Manager then pass to SQL Server Network Configuration, the underneath Protocols for MSSQLSERVER pass to Properties after which the Certificate tab. That’s the place we’ll see the certificates we simply deployed within the registry. All that’s left is to select the certificates after which restart the SQL Server Service.

Next we will be able to pass and power encryption at the protocol by way of proper clicking at the SQL Client Native Configuration and ensure Force Encryption is ready to Yes.

Now that we’ve got configured the certificates, let’s return to SQL Server Services and restart the carrier by way of proper clicking the SQL Server (MSSQLSERVER) carrier and make a choice Restart.

Voila, now we have now configured port 1433 to encrypt verbal exchange!

The subsequent step is to configure encrypted connections on our
utility or if we attach from SQL Server Management Studio.

To do this underneath SSMS, we want to undergo the next steps:

  1. Click on Connect to Database Engine
  2. In the Connect to Server conversation field pass to Options
  3. On the Connection Properties tab click on Encrypt Connection

That’s it, beautiful easy and simple. I’m hoping you
loved this newsletter and hope you come for extra cool articles.