Using Encryption and Obfuscation
Topic last updated -- June 2007
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.
Obfuscation converts passwords and definitions of stored procedures, views, triggers, and functions to an unreadable format. Note that obfuscated passwords, stored procedures, views, triggers, and functions can be reverse engineered.
Microsoft® SQL Server™ encrypts or can encrypt any data sent between the client and the server as network packets.
SQL Server can obfuscate:
- Login and application role passwords stored in SQL Server.
- 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 obfuscated. This prevents non-administrative users from viewing any passwords, including their own. Additionally, application role passwords can be obfuscated when the application role is activated before they are sent over the network. Note that obfuscated passwords may be susceptible to de-obfuscation attempts.
Note Using the sp_addlogin system stored procedure, SQL Server logins can be added without obfuscating the password, if required. However, this is not recommended unless the passwords are already obfuscated 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 marginally slows the performance of the Net-Libraries because encryption requires the following additional actions:
- 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
To configure a client to use the Multiprotocol Net-Library
Stored Procedure, Function, View, and Trigger Definitions
When the definition of a stored procedure, function, trigger, or view is saved in the syscomments system table, it can be obfuscated (for example, if a SQL Server system contains proprietary stored procedures, functions, triggers, or views whose definitions should not be viewed by users and third parties). Note that obfuscated stored procedures, functions, triggers, and views can be reverse engineered because SQL Server must de-obfuscate these database objects for execution. In SQL Server 2000, the obfuscated text of these database object definitions, which are visible in the syscomments system table, may be susceptible to de-obfuscation attempts.