How to: Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager)

Enable encrypted connections for an instance of the SQL Server Database Engine by specifying a certificate for the Database Engine using SQL Server Configuration Manager. The server computer must have a certificate provisioned, and the client machine must be set up to trust the certificate's root authority.

Certificates are stored locally for the users on the computer. To install a certificate for use by SQL Server, you must be running SQL Server Configuration Manager under the same user account as the SQL Server service unless the service is running as LocalSystem, NetworkService, or LocalService, in which case you may use an administrative account.

The client must be able to verify the ownership of the certificate used by the server. If the client has the public key certificate of the certification authority that signed the server certificate, no further configuration is necessary. Microsoft Windows includes the public key certificates of many certification authorities. If the server certificate was signed by a public or private certification authority for which the client does not have the public key certificate, you must install the public key certificate of the certification authority that signed the server certificate.

To provision (install) a certificate on the server

1. On the Start menu, click Run, and in the Open box, type MMC and click OK.

2. In the MMC console, on the File menu, click Add/Remove Snap-in.

3. In the Add/Remove Snap-in dialog box, click Add.

4. In the Add Standalone Snap-in dialog box, click Certificates, click Add.

5. In the Certificates snap-in dialog box, click Computer account, and then click Finish.

6. In the Add Standalone Snap-in dialog box, click Close.

7. In the Add/Remove Snap-in dialog box, click OK.

8. In the Certificates snap-in, expand Certificates, expand Personal, and then right-click Certificates, point to All Tasks, and then click Import.

9. Complete the Certificate Import Wizard, to add a certificate to the computer, and close the MMC console. For more information about adding a certificate to a computer, see your Windows documentation.

To export the server certificate

1. From the Certificates snap-in, locate the certificate in the Certificates / Personal folder, right-click the Certificate, point to All Tasks, and then click Export.

2. Complete the Certificate Export Wizard, storing the certificate file in a convenient location.

To configure the server to accept encrypted connections

1. In SQL Server Configuration Manager, expand SQL Server 2005 Network Configuration, right-click Protocols for <server instance>, and then select Properties.

2. In the Protocols for*<instance name>* Properties dialog box, on the Certificate tab, select the desired certificate from the drop down for the Certificate box, and then click OK.

3. On the Flags tab, in the ForceEncryption box, select Yes, and then click OK to close the dialog box.

4. Restart the SQL Server service.

To configure the client to request encrypted connections

1. Copy either the original certificate or the exported certificate file to the client computer.

2. On the client computer, use the Certificates snap-in to install either the root certificate or the exported certificate file.

3. In the console pane, right-click SQL Native Client Configuration, and then click Properties.

4. On the Flags page, in the Force protocol encryption box, click Yes.

To encrypt a connection from SQL Server Management Studio

1. On the Object Explorer toolbar, click Connect, and then click Database Engine.

2. In the Connect to Server dialog box, complete the connection information, and then click Options.

3. On the Connection Properties tab, click Encrypt connection.

See Also

Concepts

Encrypting Connections to SQL Server

Help and Information

Getting SQL Server 2005 Assistance