Skip to main content
Skip table of contents

Create a Certificate for Microsoft SQL Server Connections

This topic provides information about configuring certificates for SQL Server connections.

The following code snippet can be executed via PowerShell to create a self-signed certificate on a computer running SQL Server. The certificate meets requirements for encryption for a stand-alone SQL Server instance and is saved in the local computer's certificate store (PowerShell must be launched as an administrator).

CODE
# Define parameters 
# Update Subject with FQDN of server 
$certificateParams = @{ 
	Type = "SSLServerAuthentication" 
	Subject = "CN=USR123456789.qa.site.com" 
	DnsName = @("{0}" -f [System.Net.Dns]::GetHostByName($env:computerName).HostName, 'localhost') 
	KeyAlgorithm = "RSA" 
	KeyLength = 2048 
	HashAlgorithm = "SHA256" 
	TextExtension = "2.5.29.37={text}1.3.6.1.5.5.7.3.1" 
	NotAfter = (Get-Date).AddMonths(36) 
	KeySpec = "KeyExchange" 
	Provider = "Microsoft RSA SChannel Cryptographic Provider" 
	CertStoreLocation = "cert:\LocalMachine\My" 
} 

# Call the cmdlet 
New-SelfSignedCertificate @certificateParams

Create and Export the SQL Certificate

To create the SQL certificate:

  1. Update the Subject line of the code snippet above with the FQDN of the server. e.g. Subject = "CN=USR123456789.qa.site.com"
  2. Verify that there is a new certificate in the computer's cert store, as shown below:

  3. Grant the SQL Server service account “NT Service\MSSQLSERVER” to the certificate's private key by clicking on Manage Private Keys.





  4. Open the SQL Server Configuration Manager tool.
  5. Right-click on Protocols for MSSQLSERVER and open the Properties pop-up.
    The newly-generated certificate appears on the Certificate tab.



  6. Select this certificate from the drop-down and then select OK.
  7. Restart the SQL service.

Import the SQL Certificate into Windows Trusted Certificates

You will need to manually import this certificate into the Trust Root Certification Authorities on all LogRhythm servers that connect to SQL (such as Platform Manager, Data Processors, Web Consoles, and AI Engines).

  1. Open the Microsoft Management Console and click Export.



  2. Select No when asked whether to export the private key:



  3. Choose the DER encoded format:


  4. Specify the name of the certificate being exported:



  5. On the client machine, right-click on the exported certificate and select Install Certificate to open the certificate import wizard.
  6. Select Local Machine, and then click Next.



  7. Select Place all certificates in the following store and then click Browse.
  8. Select Trusted Root Certificate Authorities.



  9. Click Finish.
  10. Open the Microsoft Management Console and verify the certificate:



  11. Open all LogRhythm server Configuration Managers (listed below) and update the server setting to the FQDN used in certificate creation (in our example above, this was USR123456789.qa.site.com).
    This FQDN also needs to be used in SQL Server Management Studio connection properties.

Configuration Manager Options


JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.