About Me

My photo
I am an MCSE in Data Management and Analytics, specializing in MS SQL Server, and an MCP in Azure. With over 19+ years of experience in the IT industry, I bring expertise in data management, Azure Cloud, Data Center Migration, Infrastructure Architecture planning, as well as Virtualization and automation. I have a deep passion for driving innovation through infrastructure automation, particularly using Terraform for efficient provisioning. If you're looking for guidance on automating your infrastructure or have questions about Azure, SQL Server, or cloud migration, feel free to reach out. I often write to capture my own experiences and insights for future reference, but I hope that sharing these experiences through my blog will help others on their journey as well. Thank you for reading!

SSL With Microsoft SQL Server 2008 Cluster Box & How to implement SSL in SQL Server 2008


 Overview of Using SSL with Microsoft SQL Server


1. To Install  a certificate in the Windows certificate store of the server computer.
2        Configure the Database Engine to use custom certificate from Certificate Authority company.
         So the data will be encrypted between Application/Web Tier to Database Tier.  

Important information about the Force Encryption option

·         When the Force Encryption option for the Database Engine is set to Yes, all client/server communication is encrypted and clients that cannot support encryption are denied access.

·         When the Force Encryption option for the Database Engine is set to No, encryption can be requested by the client application but is not required.

SQL Server must be restarted after you change the Force Encryption setting.

SSL Certificate Requirements

For SQL Server to load an SSL certificate, the certificate must meet the following conditions:-
·         The certificate must be in either the local computer certificate store or the current user certificate   store.
·         The current system time must be after the Valid from property of the certificate and before the Valid to property of the certificate.
 
·         The certificate must be meant for server authentication. This requires the Enhanced Key Usage property of the certificate to specify Server Authentication (1.3.6.1.5.5.7.3.1).
 
·         The certificate must be created by using the KeySpec option of AT_KEYEXCHANGE. Usually, the certificate's key usage property (KEY_USAGE) will also include key encipherment (CERT_KEY_ENCIPHERMENT_KEY_USAGE).
 
·         The Subject property of the certificate must indicate that the common name (CN) is the same as the host name or fully qualified domain name (FQDN) of the server computer. If SQL Server is running on a failover cluster, the common name must match the host name or FQDN of the virtual server and the certificates must be provisioned on all nodes in the failover cluster.

Implementing SSL with SQL Server

How to create a certificate using Makecert.exe utility

1.       Makecert.exe is available as part of the Windows SDK that comes with the .NET Framework SDK

Download the .NET Framework SDK

·         Version 2.0
o   http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=19988

on windows server 2012 
https://software-download.microsoft.com/download/sg/17763.132.181022-1834.rs5_release_svc_prod1_WindowsSDK.iso
download iso from above location mount and execute setup.exe.
you will find makecert.exe  command under

C:\Program Files (x86)\Windows Kits\10\bin\10.0.17763.0\x64>

Create a self-signed certificate

1.       Login to the server using the MSSQL service account

2              From a command prompt, execute the following command:
      makecert -r -pe -n "CN=Replace_With_FQDN_of_the_VirtualSqlServerName" -b 01/01/2000 -e 01/01/2036 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr currentuser  -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12

Example:-

makecert -r -pe -n "CN=sqlserver2008.stl.net" -b 01/01/2011 -e 01/01/2038 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr currentuser -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12


 or another command to store certificate directly to localMachine.

 makecert -r -pe -n "CN=sqlserver2008.stl.net" -b 01/01/2011 -e 01/01/2036 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12


   or

you can also execute

makecert -r -pe -n "CN=Contoso-listener.contoso.com" -b 01/01/2018 -e 01/01/2036 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12


Then execute below command to check created certificate meets the certificate prerequisite.

  C:\windows\system32>certutil -v -store "my" "sqlserver2008.stl.net" >abcd.txt

  C:\windows\system32> notepad abcd.txt

5 prerequisites are :-

 1.The certificate must be in either the local computer certificate store or the current user certificate store.
2.The current system time must be after the Valid from property of the certificate and before the Valid to property of the certificate.
3.The certificate must be meant for server authentication. This requires the Enhanced Key Usage property of the certificate to specify Server Authentication (1.3.6.1.5.5.7.3.1).
4.The certificate must be created by using the KeySpec option of AT_KEYEXCHANGE
5.The Subject property of the certificate must indicate that the common name (CN) is the same as the host name or fully qualified domain name (FQDN) of the server computer. If SQL Server is running on a failover cluster, the common name must match the host name or FQDN of the virtual server and the certificates must be provisioned on all nodes in the failover cluster.



Requirement Number 2#


Requirement Number 3#
3.The certificate must be meant for server authentication. This requires the Enhanced Key Usage property of the certificate to specify Server Authentication (1.3.6.1.5.5.7.3.1).



4.The certificate must be created by using the KeySpec option of AT_KEYEXCHANGE
Requirement Number 4#


5. The Subject property of the certificate must indicate that the common name (CN) is the same as the host name or fully qualified domain name (FQDN) of the server computer. If SQL Server is running on a failover cluster, the common name must match the host name or FQDN of the virtual server and the certificates must be provisioned on all nodes in the failover cluster.

Requirement Number 5#



Please do not proceed ahead without meeting these prerequisites,

Register a certificate with SQL Server

Setting up the MMC console Certificates snap-in

1.       Start à Run à MMC
2.       On the Console menu, click Add/Remove Snap-in
3.       Click Add, and then click Certificates. Click Add again
4.       Select the  My User Account to open the snap-in
5.       Click Finish
6.       Click Add, and then click Certificates. Click Add again
7.       Select Computer account.
8.       Select the  My Computer Account  to open the snap-in
9.       Click Finish
10.   Click Add, and then click Certificates. Click Add again
11.   Click Close in the Add Standalone Snap-in dialog box.
12.   Click OK in the Add/Remove Snap-in dialog box.

Configure a trusted certificate on the server

1.       Your installed certificates are located in the Certificates folder in the Current User container.
2.       Select the Personal folder and verify that the certificate you just created appears in the left-hand pane
3.       Export  the certificate from the Current User Personal Folder to  Trusted Root Certification Authorities  folder with the password and extended properties options enabled.
  1. In The same way  Export  and import the certificates into Personal and trusted Root Certification Authorities of Certificates(Local Computer).
PS: this is because  while creating certificate through makecert we have opted a option (-sr currentuser).
So first it  appears in Certificates  - Current User MMC console.
  1. If everything looks okay.
  2. In the same way  copy the certificate into other node of cluster. And  export into  Trusted Root Certificate Authorities of Certificates (Local Computer) of other node.
  3. Now go to Cluadmin.msc à  Take the affected  Resource group offline and then take it online.
  4. Now check Errorlog file of sql server instead of showing “self generated Certificate is successfuuly loaded”  it will show

The certificate [Cert Hash(sha1) "0B3B8EAF69FE7D7C9EC516EDB99EFF94BB67DF85"] was successfully loaded for encryption.
An example entry may look similar to the following:-

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib]
"Certificate"="0B3B8EAF69FE7D7C9EC516EDB99EFF94BB67DF85"
  1. Now  restart Affected sql server resource group.
Check from Cluadmin.msc -- > sql server resource group would be up and running fine and in error log A message similar to the following entry will appear in the SQL Server log if the certificate has been successfully configured:


The certificate [Cert Hash(sha1) "0B3B8EAF69FE7D7C9EC516EDB99EFF94BB67DF85"] was successfully loaded for encryption.

Configure the certificate for SQL Server

  1. In the SQL Server Configuration Manager, expand SQL Server Network Configuration, right-click Protocols for <server instance>, and then select Properties
  2. On the Flags tab, in the ForceEncryption box, select Yes, and then click OK to close the dialog box
  3. Restart the affected MSSQL service

Validate the certificate is successfully configured for SQL Server

A message similar to the following entry will appear in the SQL Server log if the certificate has been successfully configured:

The certificate [Cert Hash(sha1) "0B3B8EAF69FE7D7C9EC516EDB99EFF94BB67DF85"] was successfully loaded for encryption.

Link:-

http://support.microsoft.com/kb/2023869

------------------Additional in case if you requires Copy existing certificate from one Windows Server to another windows Server---------------------------------------

Not Related to this Blog..


Move or copy an SSL certificate from a Windows server to another Windows server



 https://www.sslshopper.com/move-or-copy-an-ssl-certificate-from-a-windows-server-to-another-windows-server.html

https://support.microsoft.com/en-us/kb/316898




Labels: , , , , , , ,