Problem: I read in a PCI security tip that I should configure Secure Sockets Layer (SSL) encryption on our SQL Servers, but this requires a trusted certificate. Could you please help me with how to complete this task?
Solution:
SSL/TLS certificates can be used by SQL Server in order to encrypt all communication between a SQL Server instance and its client connections, by encrypting the communication channel. It’s useful for PCI security and SQL Server Audit also.
SSL/TLS certificates are widely used to secure access to SQL Server. With earlier versions of SQL Server, organizations with large SQL Server estates had to spend considerable effort to maintain their SQL Server certificate infrastructure, often through developing scripts and running manual commands.
With SQL Server 2019, certificate management is integrated into the SQL Server Configuration Manager, simplifying common tasks such as:
- Viewing and validating certificates installed in a SQL Server instance.
- Identifying which certificates may be close to expiring.
- Deploying certificates across Always On Availability Group machines from the node holding the primary replica.
- Deploying certificates across machines participating in an Always On failover cluster instance from the active node.
Note: You can use certificate management in SQL Server Configuration Manager with lower versions of SQL Server, starting with SQL Server 2008.
One of our customers from the USA has successfully installed SecureNT Intranet SSL Certificate on SQL Server.
Note: During the certificate installation, the regular PFX file gives mutliple errors. Contact support@intranetssl.net for obtaining the correct PFX file.
To install a certificate for a single SQL Server instance
- In SQL Server Configuration Manager, in the console pane, expand SQL Server Network Configuration.
- Right-click Protocols for <instance Name>, and then select Properties.
- Choose the Certificate tab, and then select Import.
- Select Browse and then select the certificate file.
- Select Next to validate the certificate. If there are no errors, select Next to import the certificate to the local instance.
Configuring SQL Server to accept encrypted connections
You can configure SSL using the SQL Server Configuration Manager. First, you should run SQL Server Configuration Manager under the SQL Server service account. The only exception is if the service is running as LocalSystem, NetworkService, or LocalService, in this case, you can use an administrative account.
- Expand SQL Server Network Configuration and right-click on Protocols for <YourMSSQLServer>, then click Properties.
- On the Certificate tab, select the certificate you would like to use.
- On the Flags tab, select Yes in the ForceEncryption box, then click OK.
- Restart the SQL Server service.
Configuring the SQL Server clients to use encrypted connections
You should export the certificate from your SQL Server and install it on the client computer to establish the encryption.
- Open the MMC Certificates Snap-in as described above.
- Right-click the Certificate, point to All Tasks and then click Export.
- Complete the Certificate Export Wizard, storing the certificate file in a selected location.
- Copy the certificate to the client computer.
- Use the MMC Certificates Snap-in on the client computer to install the exported certificate file.
- In the SQL Server Configuration Manager right-click SQL Server Native Client Configuration, and then click Properties.
- On the Flags tab, select Yes in the ForceEncryption box, then click OK.
You can also encrypt the connection from SQL Server Management Studio:
- Click Options in the Connect to Server dialog.
- On the Connection Properties tab, tick the Encrypt connection checkbox.
Source:
- How to configure SSL encryption in SQL Server 2008 R2 (mssqltips.com)
- Certificate Management in SQL Server 2017 and 2019 (sqlshack.com)
- Certificate Management (SQL Server Configuration Manager) – SQL Server 2019 | Microsoft Docs