Using Self Signed Certificate with SqlClient

Self-Signed Certificate

Before we start let's have a brief look at Self-Signed Certificate definition:

In cryptography and computer security, self-signed certificates are public key certificates that are not issued by a certificate authority (CA). These self-signed certificates are easy to make and do not cost money. However, they do not provide any trust value. Read more

Creating a self-Certificate

Remember that the subject of a self-signed certificate must be the very same name as the FQDN.

1. Change <FQDN> in -Subject property to your Fully Qualified Domain Name (FQDN) int the following PowerShell command and run it:

New-SelfSignedCertificate -Subject "CN=<FQDN>" -KeyAlgorithm RSA -KeyLength 2048 -CertStoreLocation "cert:\LocalMachine\My" -TextExtension @("2.5.29.17={text}DNS=localhost&IPAddress=127.0.0.1&IPAddress=::1") -HashAlgorithm "SHA256" -Type SSLServerAuthentication -Provider "Microsoft RSA SChannel Cryptographic Provider"

2. type in search area the keyword run. When the Run application is opened type in mmc.
3. Click on Add/Remove Snap-in
4. In the left column click on Certificates and then click on Add > button.
5. Select Computer account and click Next > and click Finish. You should be seeing something similar to the below picture:

6. Press OK.

7. Now in the left panel, under Personal select Certificate.
8. On the right panel you should be able to see the newly created certificate. The name will be your FQDN.
9. Right click on the certificate > All Tasks > Manage Private Key

10. Add required permission for SQL server instance user (NT Service\MSSQLSERVER).

Now you should be able to import this certificate to SQL server using SQL Server Configuration Manager.