The Definitive Guide To SQL Definitive Guide To SQL Server Encryption and Efinitive Guide to SQL Server Encryption and Key Management

The EKM Provider architecture supports two different methods of database encryption:

• Cell Level Encryption
• Transparent Database Encryption

Cell level encryption is also known as column level encryption. As its name implies it encrypts data in a column in a table. When a new row is inserted into a table, or when a column in a row is updated, the SQL Server database calls the EKM Provider software to perform encryption. When a column is retrieved from the database through a SQL SELECT or other statement the EKM Provider software is called to perform decryption. The EKM Provider software is responsible for both encryption and key management activity. Implementing cell level encryption requires minor changes to the SQL column definition. Learn SQL server dba online course for more skills and techniques.

Transparent Database Encryption, or TDE, provides encryption for the entire database and associated log files. All tables and views in the database are fully encrypted. Data is encrypted and decrypted as information is inserted, updated, and retrieved by users and applications. As its name implies, transparent data encryption requires no changes to applications, SQL definitions, or queries. The database works seamlessly after encryption is enabled.

Transparent Data Encryption is the easiest of the two encryption methods to implement. Later, I will discuss when it makes sense to use TDE and when Cell Level Encryption is a better choice.

Activating The EKM Provider

After installing the EKM Provider software from a third party, the SQL Server database administrator uses the SQL Server management console to activate the EKM Provider and place the database or columns under encryption control. The activation of the EKM Provider software causes the database to be immediately encrypted and all further data operations on the database will invoke the EKM Provider software.

Microsoft EKM Provider For Locally Stored Encryption Keys

Recognizing that some SQL Server customers wanted to encrypt data but did not have the resources or time to implement a key management solution, Microsoft provided a built-in EKM Provider that performs encryption but which stores encryption keys locally in the SQL Server context. Understanding that this was not a security best practice, Microsoft recommends that customers use a proper solution that separates encryption keys from the SQL Server database. That was good advice — locally stored encryption keys can be recovered by cyber criminals and the use of external key management systems provides better security and compliance.

EKM Provider Software

EKM Provider software is usually provided by your encryption key management vendor. This means that the features and functions of the EKM Provider software can vary a great deal from one vendor to another. Be sure that you fully understand the architecture and capabilities of the EKM Provider before you deploy SQL Server encryption.

SQL Server Versions That Support EKM

Provider support is available in all Enterprise editions of SQL Server including Data Warehouse and Business Intelligence editions, as well as SQL Server 2019 Standard. EKM provider support is not available in Standard, Web, or Express editions of SQL Server.

EKM Provider software performs encryption and key management tasks as an extension to the SQL Server database. The EKM Provider architecture opened the door for third party key management vendors to extend encryption to include proper encryption key management.

Database Encryption

TDE involves the encryption of the entire database space in SQL Server. There is no need or ability to select which tables or views are encrypted, all tables and views in a database are encrypted at rest (on disk). When data is read from disk (or any non-volatile storage) SQL Server decrypts the entire block making the data visible to the database engine. When data is inserted or updated the SQL Server database encrypts the entire block written to disk. sql server dba training for more effective learning.

With SQL Server TDE all of the data in your database is encrypted. This means that non-sensitive data is encrypted as well as sensitive data. There are advantages and disadvantages to this approach — you expend computing resources to encrypt data that may not be sensitive, but you also avoid mistakes in identifying sensitive data. By encrypting everything at rest you are also protected from expansion of regulatory rules about sensitive data protection.

1. SQL Server requests that the Data Encryption Key (DEK) is decrypted by the Key Encryption Key (KEK).
2. Key Connection sends the decryption request to Alliance Key Manager (AKM).
3. AKM decrypts the DEK with the KEK.
4. AKM sends the decrypted DEK to Key Connection.
5. Key Connection sends the decrypted DEK to SQL Server so that the database can be decrypted.

Protection of the Symmetric Key

When you enable Transparent Data Encryption on your SQL Server database the database generates a symmetric encryption key and protects it using the EKM Provider software from your key management vendor. The EKM Provider software sends the symmetric key to the key server where it is encrypted with an asymmetric key. The encrypted database key is then stored locally on disk in the SQL Server context.

When you start a SQL Server instance the SQL Server database calls the EKM Provider software to decrypt the database symmetric key so that it can be used for encryption and decryption operations. The decrypted database key is stored in protected memory space and used by the database. The encrypted version of the database key remains on disk. In the event the system terminates abnormally, the only version of the database key is the encrypted version on disk.

Starting the SQL Server Instance

During normal operation of SQL Server there is no invocation of the EKM Provider software and therefore no communication with an external key manager. Every normal restart of the SQL Server database instance will cause the EKM Provider software to be called to unlock the database key on the key server. It should be noted that it is the responsibility of the EKM Provider software to handle network or key server failure conditions. SQL Server itself has no visibility on the connection to an encryption key management solution. If the EKM Provider software is unable to retrieve an encryption key, the SQL Server start request will fail. We will discuss business continuity issues in more detail later in this series.

Protecting Database Logs

SQL Server logs may contain sensitive data and therefore must also be encrypted. Transparent Database Encryption addresses this by fully encrypting database logs along with the database itself. It is important to remember that encryption of the logs will only start after TDE is activated AND after you stop and restart the database log. If you neglect to restart logging sensitive data may be exposed in the SQL Server log files.

Table and Index Scanning

Certain SQL operations on indexes require that the SQL Server database have visibility on the entire index of a column. An example of a SELECT statement would be something like this:

SELECT Customer_Name, Customer_ Address FROM Orders WHERE Credit_ Card=’4111111111111111’;

To satisfy this SQL query the database must inspect every row in the table Orders. With TDE this means that the column Credit_Card must be decrypted in every row. Similar operations with the ORDERBY clause can cause table or index scans.

Performance Considerations

Transparent Data Encryption is very optimized for encryption and decryption tasks and will perform well for the majority of database implementations. Microsoft estimates the performance impact of TDE of 2% to 4% and we find this accurate for most of our customers. However, Microsoft SQL Server customers with very large SQL Server databases should use caution when implementing TDE. Be sure that you fully understand the impact of TDE on your application use of large tables. It is always recommended that you perform a proof-of-concept project on very large databases to fully assess the performance impact of encryption.

If you want to Gain In-depth Knowledge on SQL, please go through this link sql dba online training.