SSL, or Secure Sockets Layer, serves as the standard for securing host-server interactions in web environments. Beyond web contexts, SSL also ensures encrypted connections and data transfers between specific SQL Server instances and client applications. To establish an encrypted connection, a trusted SSL certificate validates the SQL Server instance when requested by the client application, or vice versa.
Proper configuration of the SQL Server involves adhering to the guidelines set by the certificate authority (CA), ensuring that certificates are appropriately signed by trusted sources.
Encryption demands some additional processing, enabling encryption can downgrade the SQL Server performance. The following article describes how you can enable encrypted connections to SQL Server Database Engine.
Server & Certificate Prerequisites
- The SSL certificate must be issued for server authentication. The certificate name (CN) must be the fully qualified domain name (FQDN) of the machine. You can verify this as Enhanced Key Usage property of the certificate, which must specify Server Authentication (1.3.6.1.5.5.7.3.1).
- The server must have an installed (imported) certificate and SQL client machines must be set up so as to trust the root authority of the certificate.
- You must be running SQL Server Configuration Manager under the same user account as the SQL Server Service unless the service is running as a Local System, Network Service or Local Service.
- The certificate must be stored either in a local computer certificate store or the current user certificate store.
- The system time must be after the “Valid from” date and before the “Valid to” date of the certificate.
- The client must be able to verify the ownership of the certificate used by the server.
Provide with (install) a certificate on the server
Providing with a certificate is the process of installing a certificate by importing it into your machine.
- On the Start menu, click Run. In the opened dialog box, type MMC and click OK.
- In the MMC console, on the File menu, click Add/Remove Snap-in.
- In the Add/Remove Snap-in dialog box, click Add.
- In the Available Snap-in dialog box, click Certificates, and then click Add.
- In the Certificates snap-in dialog box, click Computer account, and then click Finish.
- Now, in the Add/Remove Snap-in dialog box, click OK.
- Go to Certificates snap-in, expand Certificates, expand Personal folder, and then right-click Certificates, point to All Tasks, and then click Import.
- At Certificate Import Wizard, browse the certificate path and add to the computer, close the MMC console.
Export the server certificate
- Go to Certificates snap-in, locate your certificate in the Certificates/Personal folder, right-click the Certificate, point to All Tasks, and then click Export.
- Complete the Certificate Export Wizard, storing your certificate file at your convenient path.
Configure SQL server to accept encrypted connections
- Open SQL Server Configuration Manager, expand SQL Server Network Configuration, right-click Protocols for <server instance name>, and then select Properties.
- At Properties dialog box, on the Certificate tab, select the desired certificate from the drop down menu for the Certificate box, and then click OK.
- On the Flags tab, in the Force Encryption box, select Yes, and then click OK
to close the dialog box.
- Restart the SQL Server service.
Encrypt a connection from SQL Server Management Studio
- Open SQL Server Management Studio
- On the Object Explorer toolbar, click Connect, and then click Database Engine.
- In the Connect to Server dialog box, complete the connection information, and then click Options
.
- On the Connection Properties tab, click Encrypt connection.
- Click on Connect.
