This article is half-done without your Comment! *** Please share your thoughts via Comment ***
What is TDE (Transparent Data Encryption) method in SQL Server?
SQL Server: Transparent Data Encryption (TDE) to Encrypt a Database
TDE provides the ability to encrypt an entire database and to have the encryption be completely transparent to the applications that access the database. TDE 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.
In addition, any backups of the database are encrypted. This protects the data while it’s at rest as well as provides protection against losing sensitive information if the backup media were lost or stolen.
Which versions of SQL Server support TDE?
TDE requires SQL Server 2012 Enterprise edition.
TDE is also available in SQL Server 2008 and SQL Server 2008 R2 Datacenter and Enterprise editions.
Is there a performance impact for using TDE?
Yes, some performance overhead is involved in using TDE. The encryption and decryption process do require additional CPU cycles. The overhead for using TDE ranges from about 3 percent to 30 percent, depending on the type of workload.
How can you enable TDE in SQL server?
- Create a master key for the database.
- Create a certificate that’s protected by the master key.
- Create a special key that’s used to protect the database. This key is called the database encryption key (DEK) and you secure it using the certificate.
- Enable encryption.
SQL Server: How to enable Transparent Data Encryption (TDE) on a Database?
What is a MASTER KEY?
A master key is a symmetric key that is used to create certificates and asymmetric keys.
What are the Advantages of using TDE?
- Performs real-time I/O encryption and decryption of the data and log files
- Encrypts the Entire Database in rest
- No architectural changes needed
- No application code changes are required and the user experience is the same
- Easy to implement
- DBAs can still see the data
What are the Disadvantages of using TDE?
- Not granular – Cannot just encrypt specific tables/columns
- Not good for high CPU bottleneck servers
- Not protected through communication/networks
Which key provides the strongest encryption?
AES (256 bit)
The longer the key, the better the encryption, so choose longer keys for more encryption. However there is a larger performance penalty for longer keys. DES is a relatively old and weaker algorithm than AES.
AES: Advanced Encryption Standard
DES: Data Encryption Standard
Does TDE prevent the security administrator or database administrator from seeing the data in the database?
No. TDE protects the data at rest, but an authorized user such as a security administrator or a database administrator can access the data in a TDE-encrypted database. To prevent an SA or DBA from accessing selected parts of the data, you need to use application-level encryption.
How do you enable TDE?
To use TDE, first you need to create a master key using the CREATE MASTER KEY T-SQL statement. Next, you need to create a certificate using the CREATE CERTIFICATE command.
Then you need to use the CREATE DATABASE ENCRYPTION KEY statement to encrypt the Database Encryption Key (DEK). Finally, you need to use the ALTERDATABASE SET ENCRYPTION ON statement to actually encrypt the user database with the DEK. As soon as you use TDE for a user database, TempDB is automatically encrypted as well.
Is there a performance impact for using TDE?
Yes, some performance overhead is involved in using TDE. The encryption and decryption process do require additional CPU cycles. The overhead for using TDE ranges from about 3 percent to 30 percent, depending on the type of workload.
SQL Server instances with low I/O and low CPU usage will have the least performance impact. Servers with high CPU usage will have the most performance impact.
Does TDE encrypt the data stream sent to the client?
No. TDE encrypts the data stored on disk at the page and log level. The data that’s sent across the wire to the client connections isn’t encrypted.
What is the use of db_owner role?
Users in the db_owner role have it all, within a single database. They can grant and revoke access, create tables, stored procedures, views, run backups and schedule jobs. A user who is db_owner can even drop the database.
Why Use db_datareader role?
Let’s say the Accounting department has a separate database. Everyone in Accounting is able to create and run ad hoc reports directly against this database, but they shouldn’t be able to do anything else apart from seeing the contents of the database. Clearly granting db_owner access is out of the question.
Your junior DBA is out sick today, so you can’t make him grant SELECT permissions to every table in the database while you go out for lunch. Instead, you can simply grant db_datareader access to the Accounting department’s Windows group.
What is the use of db_ddladmin role?
Members of the db_ddladmin role are able to execute DDL commands (CREATE, ALTER, DROP) within the current database. It’s pretty self-explanatory – a member of db_ddladmin can run any CREATE, ALTER or DROP command within the current database. They cannot create new databases, nor can they alter or drop the current database.
Creating custom database application role, is bad practice or good practice?
It is good practice.
Granting permissions to roles rather than to users simplifies security administration. Permission sets that are assigned to roles are inherited by all members of the role. It is easier to add or remove users from a role than it is to recreate separate permission sets for individual users.
What kind of different encryption algorithms supported by SQL Server?
SQL Server supports several symmetric key encryption algorithms, including DES, Triple DES, RC2, RC4, 128-bit RC4, DESX, 128-bit AES, 192-bit AES, and 256-bit AES. The algorithms are implemented using the Windows Crypto API.
