Data encryption is a transformation of data into another form to improve security. The people who have access to secret or decryption key or password can only read. The main purpose of it is to protect the confidentiality of digital data.
The SQL server encryption is a process to encrypt connections (i.e. links), data and procedures that are stored in a database. The various areas that are needed to be covered to secure SQL Server are the platform, authentication, objects mainly data and applications that access the system.
The various SQL Server encryption options are Transparent Data Encryption (TDE), Column-level Encryption, Encrypting and Decrypting Data with the.NET Framework, Encrypting File Systems, and BitLocker.
Transparent Data Encryption (TDE) is the primary encryption option that was made available in SQL Server 2008. It enables us to encrypt the whole database. The backups for databases using TDE are also encrypted and it protects the data at rest. It is easy for implementation as well. It encrypts the data stored in both the database’s data file (.mdf) and log file (.ldf) using either Advanced Encryption Standard (AES) or Triple DES (3DES) encryption.
Like data compression, TDE database encryption is performed at the page level. Data is encrypted on the disk and is decrypted as it’s read into memory. When we perform the encryption at the page level, it enables the encryption process to be completely transparent to the client applications. It doesn’t have many limitations on the searching ability or query the data in the encrypted database. In addition, since most database applications are optimized to minimize input/output for performance reasons, the encryption process becomes efficient. The participating systems become encrypted if the database is being used with AlwaysOn Availability Groups, database mirroring, or log shipping. The main point is that TDE encrypts the stored data but doesn’t encrypt the communications link between the server and the client applications. If we need to encrypt the data connection between the application and the server, we need to use an SSL connection for clients. Technologies such as database mirroring and AlwaysOn Availability Groups support network transport encryption as endpoint properties.
Always Encrypted is a feature that is designed to protect sensitive data, such as credit card numbers or national identification numbers, stored in SQL Server databases. The sensitive data inside client applications can be encrypted by clients and the encryption keys are never revealed to SQL Server. As a result, it provides a separation between those who own the data and those who manage the data.
Always Encrypted makes encryption transparent to applications. An Always Encrypted-enabled driver installed on the client computer achieves this by automatic encryption and decryption of sensitive data in the SQL Server client application. The driver encrypts the data in sensitive columns before passing the data to SQL Server, and automatically queries are rewritten so that the semantics of the application are preserved. Similarly, the driver transparently decrypts data, stored in encrypted database columns, contained in query results.
Disadvantages of Transparent Data Encryption compared to Always Encrypted:
1. Only protects data at rest – backups and data files are “safe” but data in motion or in memory is vulnerable.
2. Only complete database.
3. All data is encrypted the same way.
4. Requires Enterprise Edition.
5. Data always accessible to a system administrator.
The future of encryption and the innovation of the applications providers of the near future will forever influence how enterprises conduct business electronically. It begs the question, “What would we do if all our information were safe?”