In this article, we will be discussing about the different available options, for encrypting SQL Server databases.
The Need for Data Encryption
In corporate environments there is always the requirement/policy of data protection. Data is the most valuable asset in every organization, after its human resources of course, as it is with data where the organization’s business processes run and produce results.
SQL Server is one of the leading data platforms worldwide and as such, from SQL Server 2008 (Enterprise Edition) and later, all the necessary mechanisms for ensuring data protection are available within the Database Engine and can easily be used.
Transparent Data Encryption (TDE) in SQL Server
SQL Server 2008 introduced Transparent Data Encryption (TDE); a mechanism allowing the DBA to easily encrypt databases without affecting their operation as it allows full transparency to the database users and applications. Based on TDE, the DBA encrypts the database using a master key and a certificate. The user accesses the database transparently as the encryption is automatically maintained on the Database Engine-level and in the case of a potential data theft, i.e. someone illegally copies the database files (data and log files), when he tries to access the database it will not be allowed because the database is encrypted. You can find a simple example on how you can use Transparent Data Encryption in one of my older posts here.
As mentioned above, Transparent Data Encryption is available in SQL Server 2008 or later. However, if you would like to achieve database encryption in earlier versions of SQL Server, you can do it by using a special feature of Windows on the file system-level, that is Encrypting File System (EFS) which was introduced in version 3.0 of NTFS and provides file system-level encryption. EFS is available from Windows 2000 onwards. The way EFS works is transparent, yet very powerful. A high-level description of its operation is the following.
For encrypting files:
1. With a symmetric file encryption key (FEK) generated for this purpose, EFS encrypts the file.
2. Then it encrypts the FEK using the active Windows user’s public key.
For decrypting files:
1. EFS decrypts the encrypted FEK using the Windows user’s private key.
2. Then it decrypts the encrypted file using the decrypted FEK.
Encryption Options for Earlier SQL Server Versions
DBA Security Advisor helps you assess your SQL Server instances for security risks and misconfigurations
DBA Security Advisor, is our powerful SQL Server security tool, which assesses SQL Server instances for potential security risks and misconfigurations, based on a proven best practices set of security checks. Furthermore it provides recommendations for the detected security risks as well as remediation scripts and methods.
DBA Security Advisor: Security Report.Try DBA Security Advisor free for 30 days!
Strengthen your SQL Server Administration Skills – Enroll to our Online Course!
Check our online course on Udemy titled “Essential SQL Server Administration Tips” (special limited-time discount included in link).
Via the course, you will learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!
Featured Online Courses:
- Boost SQL Server Database Performance with In-Memory OLTP
- Essential SQL Server Administration Tips
- SQL Server Fundamentals – SQL Database for Beginners
- Essential SQL Server Development Tips for SQL Developers
- The Philosophy and Fundamentals of Computer Programming
- .NET Programming for Beginners – Windows Forms with C#
- Introduction to Data Science and SQL Server Machine Learning
- Introduction to Azure SQL Database for Beginners
- SQL Server 2019: What’s New – New and Enhanced Features
- Entity Framework: Getting Started – Complete Beginners Guide
- How to Import and Export Data in SQL Server Databases
- Learn How to Install and Start Using SQL Server in 30 Mins
- A Guide on How to Start and Monetize a Successful Blog
Other SQL Server Security-Related Articles
- How to Enable SSL Certificate-Based Encryption on a SQL Server Failover Cluster
- Why You Need to Secure Your SQL Server Instances
- Should Windows “Built-In\Administrators” Group be SQL Server SysAdmins?
- Frequent Password Expiration: Time to Revise it?
- Transparent Data Encryption (TDE) in SQL Server
- 10 Facts About SQL Server Transparent Data Encryption
- Encrypting a SQL Server Database Backup
- The “Public” Database Role in SQL Server
- …check all
Check our latest software releases!
Easily generate snippets with Snippets Generator!
Secure your databases using DBA Security Advisor!
Convert static T-SQL to dynamic and vice versa with Dynamic SQL Generator.
Rate this article:
Reference: SQLNetHub.com (https://www.sqlnethub.com)
Artemakis Artemiou is a Senior SQL Server Architect, Author, a 9 Times Microsoft Data Platform MVP (2009-2018) and a Udemy Instructor. He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Moreover, Artemakis teaches on Udemy, you can check his courses here.