Microsoft KB Archive/316898

From BetaArchive Wiki

Article ID: 316898

Article Last Modified on 12/28/2005


APPLIES TO

  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 64-bit Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Workgroup Edition


This article was previously published under Q316898

IN THIS TASK

SUMMARY

REFERENCES

SUMMARY

This step-by-step article describes how to install a certificate on a computer that is running Microsoft SQL Server 2000 or Microsoft SQL Server 2005 by using Microsoft Management Console (MMC) and describes how to enable SSL Encryption at the server, or for specific clients.

Note You cannot use this method to put a certificate on a SQL Server clustered server.

If your company has implemented an Enterprise Certificate Authority, you can request certificates for a SQL Server stand-alone server, and then use the certificate for Secure Sockets Layer (SSL) encryption.

You can enable the Force Protocol Encryption option on the server, or on the client.

Note Do not enable the Force Protocol Encryption option on both the client and the server. To enable Force Protocol Encryption on the server, use the Server Network Utility. To enable Force Protocol Encryption on the client, use the Client Network Utility.

Important If you enable SSL encryption by using the Client Network Utility, then all connections from that client will request SSL encryption to any SQL Server to which that client connects.

Warning If you enable Force Protocol Encryption on the client computer, you cannot connect to previous versions of SQL Server from that specific client. Previous versions of SQL Server do not recognize SSL encryption.

If you enable Force Protocol Encryption on the server, you must install a certificate on the server.

If you want to enable Force Protocol Encryption on the client, you must have a certificate on the server and the client must have the Trusted Root Authority updated to trust the server certificate.

Note If you are using SQL Server 2005 to enable encrypted connections for an instance of SQL Server 2005, you can set the value of the ForceEncryption option to Yes. For more information, see the "How to: Enable Encryption Connections to the Database Engine (SQL Server Configuration Manager)" topic in SQL Server 2005.

back to the top

Install a certificate on a server with Microsoft Management Console (MMC)

To use SSL encryption, you must install a certificate on the server. Follow these steps to install the certificate by using the Microsoft Management Console (MMC) snap-in.

How to Configure the MMC Snap-in

  1. To open the Certificates snap-in, follow these steps:
    1. To open the MMC console, click Start, and then click Run. In the Run dialog box type:

      MMC
    2. On the Console menu, click Add/Remove Snap-in....
    3. Click Add, and then click Certificates. Click Add again.
    4. You are prompted to open the snap-in for the current user account, the service account, or for the computer account. Select the Computer Account.
    5. Select Local computer, and then click Finish.
    6. Click Close in the Add Standalone Snap-in dialog box.
    7. Click OK in the Add/Remove Snap-in dialog box. Your installed certificates are located in the Certificates folder in the Personal container.
  2. Use the MMC snap-in to install the certificate on the server:
    1. Click to select the Personal folder in the left-hand pane.
    2. Right-click in the right-hand pane, point to All Tasks, and then click Request New Certificate....
    3. The Certificate Request Wizard dialog box opens. Click Next. Select Certificate type is "computer".
    4. In the Friendly Name text box you can type a friendly name for the certificate or leave the text box blank, and then complete the wizard. After the wizard finishes, you will see the certificate in the folder with the fully qualified computer domain name.

    5. If you want to enable encryption for a specific client or clients, skip this step and proceed to the Enable encryption for a specific client section of this article.

      If you want to enable encryption at the server, open the Server Network Utility on the server where the certificate is installed, and then click to select the Force protocol encryption check box. Restart the MSSQLServer (SQL Server) service for the encryption to take effect. Your server is now ready to use SSL encryption.

back to the top

Enable encryption for a specific client

For the client to request the SSL encryption, the client computer must trust the server certificate and the certificate must already exist on the server. You have to use the MMC snap-in to export the Trusted Root Certification Authority used by the server certificate:

  1. To export the server certificate's Trusted Root Certificate Authority (CA), follow these steps:
    1. Open MMC, and then locate your certificate in the Personal folder.
    2. Right-click the certificate name, and then click Open.
    3. Review the Certification Path tab. Note the top most item.
    4. Navigate to the Trusted Root Certification Authorities folder, and then locate the Certificate Authority noted in step c..
    5. Right-click CA, point to All Tasks, and then click Export.
    6. Select all the defaults, and then save the exported file to your disk where the client computer can access the file.
  2. Follow these steps to import the certificate on the client computer:
    1. Navigate to the client computer by using the MMC snap-in, and then browse to the Trusted Root Certification Authorities folder.
    2. Right-click the Trusted Root Certification Authorities folder, point to All Tasks, and then click Import.
    3. Browse, and then select the certificate (.cer file) that you generated in step 1. Select the defaults to complete the remaining part of the wizard.
    4. Use the SQL Server Client Network Utility.
    5. Click to select the Force Protocol encryption option. Your client is now ready to use SSL encryption.

back to the top

How to test your client connection

To test your client connection you can either:

  • Use the Query Analyzer Tool.


-or-

  • Use any ODBC application where you can change the connection string.

Query Analyzer Tool

To use the Query Analyzer Tool, follow these steps:

  1. Use the SQL Server Client Network Utility.
  2. Click to select the Force protocol encryption option.
  3. Connect to the server that is running SQL Server 2000 by using Query Analyzer.
  4. Monitor the communication by using Microsoft Network Monitor or a Network Sniffer.

ODBC or OLEDB Application Sample Connection Strings

If you use ODBC or OLEDB connection strings, follow these steps:

  1. Modify the ODBC or OLEDB connection string. For example:

    ODBC

    Driver=SQLServer;Server=ServerNameHere;UID=UserIdHere;PWD=PasswordHere;Network=DBNETLIB.DLL;Encrypt=YES

    OLEDB

    Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=dbNameHere;Data Source=ServerNameHere;Use Encryption for Data=True
  2. Connect to the server that is running SQL Server 2000, and then monitor the communication by using Microsoft Network Monitor or a Network Sniffer.

back to the top


Additional query words: snapin snap in

Keywords: kbhowtomaster KB316898



Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.


Retrieved from ‘https://www.betaarchive.com/wiki/index.php?title=Microsoft_KB_Archive/316898&oldid=174246’
the Creative Commons 3.0 ShareAlike (except the Microsoft KB Archive).
Powered by MediaWiki