Standard

Posted by

Posted on

February 13, 2017

Posted under

Core

Comments

Leave a comment

Using certificates with SQL server

-> Install SDK from https://msdn.microsoft.com/en-us/windows/desktop/bg162891.aspx to get makecert.exe.

-> Makecert.exe will be present in location C:\Program Files (x86)\Windows Kits\8.1\bin\x64\ for X64 and C:\Program Files\Windows Kits\8.1\bin\x86\ for X86.

-> When using makecert.exe, make sure you use the correct server FQDN. Any mistakes in the FQDN while creating the certificate will result in the certificate not being used by SQL server.

-> Run the below query from command prompt.
makecert -r -pe -n “CN=<Server_Name_FQDN>” -b 02/13/2017 -e 12/01/2040 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine  -sky exchange -sp “Microsoft RSA SChannel Cryptographic Provider” -sy 12

-> Click on Run -> MMC -> File -> Add/remove snap in -> Click on Certificates -> Add it.

-> Select “Computer account” -> Finish.

-> Select Personal -> Certificates -> Look at the certificate you just created -> Double click it.

-> Click on Details -> Go to thumbprint. You will see something like below,
‎e0 fd 68 2a af 72 74 fb 24 24 ea 77 99 60 85 58 e1 61 79 1e

-> Remove the spaces and place it in a notepad session.

-> Open registry and check the certificate key that resides in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxx.<InstanceName>\MSSQLServer\SuperSocketNetLib and see if the values in “certificate key” is same as the one placed in the notepad session. If the “certificate” key in the registry is blank or it has a different value, then copy the thumbprint value from the notepad session and put it in the “Certificate” key in registry.

-> Open the SQL server configuration manager and get to certificate tab as shown below. For a Standalone SQL server instance, if everything is fine with the certificate configuration. You should see it in the certificate as shown below.

blog11_5

-> In case if it is a clustered SQL server instance, you wont see the certificate in the configuration manager as the certificate will be created using virtual host name and not with the physical node name.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Leave a Reply