Using Encryption Methods

SQL Server Setup Help

SQL Server Setup Help

Using Encryption Methods

Encryption is a method for keeping sensitive information confidential by changing data into an unreadable form. Encryption ensures that data remains secure by keeping the information hidden from everyone, even if the encrypted data is viewed directly. Decryption is the process of changing encrypted data back into its original form so it can be viewed by authorized users.

Microsoft® SQL Server™ encrypts or can encrypt:

  • Login and application role passwords stored in SQL Server.

  • Any data sent between the client and the server as network packets.

  • Stored procedure definitions.

  • User-defined function definitions.

  • View definitions.

  • Trigger definitions.

  • Default definitions.

  • Rule definitions.

Note  If you are running Microsoft Windows® 2000 and want to use the Windows 2000 Encrypted File System to encrypt any SQL Server files, you must unencrypt the files before you can change the SQL Server service accounts. If you do not unencrypt the files and then reset the SQL Server service accounts, you cannot unencrypt the files.

Login and Application Role Passwords

Login and application role passwords stored in the SQL Server system tables are always encrypted. This prevents users, including system administrators, from viewing any passwords, including their own. Additionally, application role passwords can be encrypted when the application role is activated before they are sent over the network.

Note  Using the sp_addlogin system stored procedure, SQL Server logins can be added without encrypting the password, if required. However, this is not recommended unless the passwords are already encrypted because they are being imported from another instance of SQL Server.

Data in Network Packets

SQL Server allows data sent between the client and the server to be encrypted. This ensures that any application or user intercepting the data packets on the network cannot view confidential or sensitive data (for example, passwords sent across the network as a user logs into an instance of SQL Server). SQL Server can use the Secure Sockets Layer (SSL) to encrypt all data transmitted between an application computer and an instance of SQL Server. The SSL encryption is performed within the Super Socket Net-Library (Dbnetlib.dll and Ssnetlib.dll) and applies to all inter-computer protocols supported by SQL Server 2000. Enabling encryption slows the performance of the Net-Libraries. Encryption forces the following actions in addition to all of the work for an unencrypted connection:

  • An extra network round trip is required at connect time.

  • All packets sent from the application to the instance of SQL Server must be encrypted by the client Net-Library and decrypted by the server Net-Library.

  • All packets sent from the instance of SQL Server to the application must be encrypted by the server Net-Library and decrypted by the client Net-Library.

Shared memory Net-Library communications are inherently secure without the need for encryption. The shared memory Net-Library does not participates in inter-computer communications. The area of memory shared between the application process and the database engine process cannot be accessed from any other Windows process.

For compatibility with earlier versions of SQL Server, the Multiprotocol Net-Library continues to support its own encryption. This encryption is specified independently of the SSL encryption and is implemented by calling the Windows RPC encryption API. It does not require the use of certificates. The level of RPC encryption, 40-bit or 128-bit, depends on the version of the Windows operating system that is running on the application and database computers. The Multiprotocol Net-Library is not supported by named instances. For more information about SSL, see Net-Library Encryption.

Configuring a Multiprotocol Alias

When you configure a multiprotocol alias, enable encryption. This encryption feature applies only to the Multiprotocol Net-Library. This encryption feature is offered only for compatibility with existing applications. SQL Server clients should use the SSL encryption specified on the General tab in the Enable protocol encryption check box of the Client Network Utility. For more information on the Client Network Utility, see Configuring Client Net-Libraries.

To start the Client Network Utility