How to establish and enforce encrypted multiprotocol connections in SQL Server 2000 (841695)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
Bug #: 21342 (Content Maintenance)

Important This article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 Description of the Microsoft Windows Registry

SUMMARY

When you connect to an instance of Microsoft SQL Server 2000, the data that is transmitted between the server computer and the client computer can be encrypted by using one of the following encryption methods:
  • SSL encryption
  • Multiprotocol encryption
This article describes how to perform the following:
  • Enable multiprotocol encryption for all the client connections.
  • Enable multiprotocol encryption for a specific client.
  • Enable multiprotocol encryption over a firewall.
  • Enforce multiprotocol connections over TCP/IP.
  • Enforce multiprotocol connections over Named Pipes.

IN THIS TASK

INTRODUCTION

When a client computer connects to an instance of SQL Server that is running on another computer in the network, the data that is exchanged between the client computer and the server computer may have to be kept confidential, especially in scenarios such as authentication. To make sure that the data remains secure, you can use encryption. Encryption keeps the sensitive information secure by changing data to an unreadable form. In SQL Server 2000, you can encrypt data that is transmitted between a client computer and a server computer over a network. To do this, enable one of the following encryption methods in SQL Server:
  • SSL encryption: SQL Server permits data to be encrypted over the network by using all network libraries that use Secure Sockets Layer (SSL) encryption. The SSL encryption method uses certificates to encrypt and decrypt SQL Server data. For additional information about how to enable SSL encryption in SQL Server, click the following article number to view the article in the Microsoft Knowledge Base:

    276553 How to enable SSL encryption for SQL Server 2000 with Certificate Server

  • Multiprotocol encryption: The Multiprotocol server network library uses the remote procedure call (RPC) facility of the operating system. Additionally, the Multiprotocol server network library communicates over most interprocess communication (IPC) mechanisms, such as TCP/IP sockets and Named Pipes, that are supported by the operating system. If you configure your instance of SQL Server to listen on the Multiprotocol server network library, you can encrypt the data that corresponds to the instance of SQL Server without using certificates.
Note For compatibility with earlier versions of SQL Server, the Multiprotocol Net-Library continues to support its own encryption. You must use multiprotocol encryption only to make your instance of SQL Server compatible with your existing systems. The Multiprotocol server network library is not supported with named instances of SQL Server.

Back to the top

Enable multiprotocol encryption for all the client connections

To configure the default instance of SQL Server to listen on the Multiprotocol server network library and to enable multiprotocol encryption for all client connections, follow these steps on the server computer:
  1. Click Start, point to Programs, point to Microsoft SQL Server, and then click Server Network Utility.
  2. In the Instance(s) on this server list, click the default instance of SQL Server that is installed on your computer.
  3. If Multiprotocol is not listed in the Enabled protocols list, click Multiprotocol in the Disabled protocols list, and then click Enable.
  4. In the Enabled protocols list, click Multiprotocol, and then click Properties.
  5. In the InstanceName - Multiprotocol dialog box, click to select the Enable encryption check box.
  6. Click OK.
  7. In the Server Network Utility dialog box, click Apply, and then click OK.

    Note If you receive a message box that states that the changes will not take effect until the SQL Server service is stopped and restarted, click OK.
  8. Stop and then restart the MSSQLServer service.
  9. Create a new Security registry value to enforce multiprotocol encryption on the server computer. To do this, follow these steps.

    Warning If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.
    1. Click Start, click Run, type Regedt32, and then click OK.
    2. In Registry Editor, create a new registry value named Security under the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\RPCNetlib registry key with the following properties:
      • Value Name: Security
      • Value Type: REG_SZ
      • Value Data: Encrypt
    3. Close Registry Editor.
After you configure your instance of SQL Server to listen on the Multiprotocol server network library and you enable multiprotocol encryption, you must configure the SQL Server clients to use the Multiprotocol client network library to connect to your instance of SQL Server. To do this, follow these steps on the SQL Server client computer:
  1. Click Start, point to Programs, point to Microsoft SQL Server, and then click Client Network Utility.
  2. In the SQL Server Client Network Utility dialog box, click the General tab.
  3. If Multiprotocol is not listed in the Enabled protocols list, click Multiprotocol in the Disabled protocols list, and then click Enable.
  4. In the SQL Server Client Network Utility dialog box, click the Alias tab, and then click Add.
  5. In the Add Network Library Configuration dialog box, under Network libraries, click to select the Multiprotocol option.
  6. In the Server alias box, type the name of the instance of SQL Server that is listening on the Multiprotocol server network library.
  7. Leave the Additional parameters box empty unless your server must have specific parameters. Contact your network administrator before you enter additional parameters.
  8. Click OK.
  9. In the SQL Server Client Network Utility dialog box, click Apply, and then click OK.
Back to the top

Enable multiprotocol encryption for a specific client

To configure a specific SQL Server client to use multiprotocol encryption when the SQL Server client connects to your instance of SQL Server by using the Multiprotocol client network library, you must disable multiprotocol encryption on the server computer. To do this, follow these steps on the server computer:
  1. Click Start, point to Programs, point to Microsoft SQL Server, and then click Server Network Utility.
  2. In the Instance(s) on this server list, click the default instance of SQL Server that is installed on your computer.
  3. In the Enabled protocols list, click Multiprotocol, and then click Properties.
  4. In the InstanceName - Multiprotocol dialog box, click to clear the Enable encryption check box.
  5. Click OK.
  6. In the Server Network Utility dialog box, click Apply, and then click OK.

    Note If you receive a message box that states that the changes will not take effect until the SQL Server service is stopped and restarted, click OK.
  7. Stop and then restart the MSSQLServer service.
After you disable multiprotocol encryption on the server computer, you must enable the SQL Server client to use encryption when the client connects to your instance of SQL Server by using the Multiprotocol client network library. To do this, follow these steps on the SQL Server client computer:
  1. Click Start, point to Programs, point to Microsoft SQL Server, and then click Client Network Utility.
  2. In the SQL Server Client Network Utility dialog box, and then click the General tab.
  3. If Multiprotocol is not listed in the Enabled protocols list, click Multiprotocol in the Disabled protocols list, and then click Enable.
  4. In the SQL Server Client Network Utility dialog box, click the Alias tab, and then click Add.
  5. In the Add Network Library Configuration dialog box, under Network libraries, click to select the Multiprotocol option.
  6. In the Server alias box, type the name of the instance of SQL Server that is listening on the Multiprotocol server network library.
  7. Leave the Additional parameters box empty unless your server must have specific parameters. Contact your network administrator before you enter additional parameters.
  8. Click OK.
  9. In the SQL Server Client Network Utility dialog box, click Apply, and then click OK.
  10. Create a new Security registry value to enforce multiprotocol encryption on the client computer. To do this, follow these steps.

    Warning If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.
    1. Click Start, click Run, type Regedt32, and then click OK.
    2. In Registry Editor, create a new registry value named Security under the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\RPCNetlib registry key with the following properties:
      • Value Name: Security
      • Value Type: REG_SZ
      • Value Data: Encrypt
    3. Close Registry Editor.
Back to the top

Enable multiprotocol encryption over a firewall

To enable multiprotocol encryption over a firewall, you must configure your firewall server so that the TCP ports that are required for the communication between the server computer and the SQL Server client computer are not blocked. After you configure your instance of SQL Server to use the Multiprotocol server network library through a specific TCP port, you must also configure all the SQL Server clients to connect to your instance of SQL Server by using the Multiprotocol client network library through the TCP port where SQL Server is listening. To do this, follow these steps:
  1. Configure your instance of SQL Server to listen on a static TCP port. By default, the default instance of SQL Server is configured to listen on TCP port 1433.
  2. Make sure that the firewall server does not block the network traffic to the server computer through the TCP port where your instance of SQL Server is listening.
  3. Configure your instance of SQL Server to use the Multiprotocol server network library through the TCP port where your instance of SQL Server is listening. To do this, follow these steps.

    Warning If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.
    1. Click Start, click Run, type Regedt32, and then click OK.
    2. In Registry Editor, create a new registry value named RPCProtocols under the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\RPCNetLib registry key with the following properties:
      • Value Name: RPCprotocols
      • Value Type: REG_MULTI_SZ
      • Value Data: ncacn_ip_tcp,TCP port
    3. Close Registry Editor.
  4. Enable multiprotocol encryption on the server computer. To do this, follow the steps in the "Enable multiprotocol encryption for all the client connections" section in this article.
  5. Force the SQL Server client connections to your instance of SQL Server through the TCP port where your instance of SQL Server is listening. To do this, follow these steps on the client computer:
    1. Click Start, point to Programs, point to Microsoft SQL Server, and then click Client Network Utility.
    2. In the SQL Server Client Network Utility dialog box, click the Alias tab, click the server alias that corresponds to your instance of SQL Server, and then click Edit.
    3. Under Connection parameters, type ncacn_ip_tcp:IP Address of the server computer[TCP port] in the Server name box.
    4. Click OK.
    5. Click Apply, and then click OK.
    If the SQL Server client computer does not have Client Network Utility and the Microsoft Data Access Components (MDAC) SQL Server ODBC Driver version 3.70.0623 is installed on the client computer, you can run the Windbver.exe file. By default, the Windbver.exe file is located in one of the following folders:
    • Installation Drive\Winnt\System32
    • Installation Drive\Windows\System32
    To force the SQL Server client connections to your instance of SQL Server through a specified TCP port by using the Windbver.exe file, follow these steps on the client computer:
    1. Click Start, click Run, type Windbver, and then click OK.
    2. In the SQL Server Client Configuration Utility dialog box, click the Advanced tab.
    3. Under Client Configuration, type the name of the server computer in the Server box.
    4. In the Network Protocol list, click Multi-Protocol.
    5. In the Connection String box, type ncacn_ip_tcp:IP Address of the server computer[TCP port].
    6. Click Add/Modify, and then click Done.
Back to the top

Enforce multiprotocol connections over TCP/IP

You can enforce the SQL Server client connections that connect to your instance of SQL Server by using the Multiprotocol client network library over TCP/IP. To do this, follow these steps on the client computer:
  1. Click Start, point to Programs, point to Microsoft SQL Server, and then click Client Network Utility.
  2. In the SQL Server Client Network Utility dialog box, click the Alias tab, click the server alias that corresponds to your instance of SQL Server, and then click Edit.
  3. Under Connection parameters, type ncacn_ip_tcp:IP Address of the server computer in the Server name box.
  4. Click OK.
  5. Click Apply, and then click OK.
Back to the top

Enforce multiprotocol connections over Named Pipes

You can enforce all the SQL Server client connections that connect to your instance of SQL Server by using the Multiprotocol server network library over Named Pipes. To do this, follow these steps on the client computer:
  1. Click Start, point to Programs, point to Microsoft SQL Server, and then click Client Network Utility.
  2. In the SQL Server Client Network Utility dialog box, click the Alias tab, click the server alias that corresponds to your instance of SQL Server, and then click Edit.
  3. Under Connection parameters, type ncacn_np:Name of the instance of SQL Server in the Server name box.
  4. Click OK.
  5. Click Apply, and then click OK.
Back to the top

REFERENCES

For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

239894 How to establish encrypted multiprotocol connections with SQL Server 7.0


164667 Replication setup over a firewall


132224 Network encryption available using the Multi-protocol net library


814950 FIX: A computer might transmit nonencrypted data when clients use the Multiprotocol Net-Library with the encryption option enabled


For additional information about how to use static and dynamic TCP ports in SQL Server, click the following article number to view the article in the Microsoft Knowledge Base:

823938 How to use static and dynamic port allocation in SQL Server 2000

For additional information about the TCP ports that are required to communicate to SQL Server over a firewall, click the following article number to view the article in the Microsoft Knowledge Base:

287932 TCP ports needed for communication to SQL Server through a firewall



Back to the top
Modification Type:MajorLast Reviewed:7/15/2004
Keywords:kbhowto kbSqlClient kbServer kbnetwork KbClientServer kbClient kbSecurity KB841695 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.