Introduction to basics of Security and Encryption in SQL Server
What is Encryption?
Encryption is the process of obfuscating data by the use of a key or password. This can make the data useless without the corresponding decryption key or password. Encryption does not solve access control problems. However, it enhances security by limiting data loss even if access controls are bypassed. For example, if the database host computer is misconfigured and a hacker obtains sensitive data, that stolen information might be useless if it is encrypted.
In SQL Server encryption can be used for connections, data, and stored procedures. We will see further about security, encryption, types of encryption.
Securing SQL Server can be viewed as a series of steps, involving four areas: the platform, authentication, objects (including data), and applications that access the system.
Platform and Network Security – The platform for SQL Server includes the physical hardware and networking systems connecting clients to the database servers, and the binary files that are used to process database requests. Best practices for physical security strictly limit access to the physical server and hardware components. For example, use locked rooms with restricted access for the database server hardware and networking devices. In addition, limit access to backup media by storing it at a secure offsite location.
Operating system service packs and upgrades include important security enhancements. Apply all updates and upgrades to the operating system after you test them with the database applications. Firewalls also provide effective ways to implement security. Logically, a firewall is a separator or restrictor of network traffic, which can be configured to enforce your organization’s data security policy. If you use a firewall, you will increase security at the operating system level by providing a chokepoint where your security measures can be focused. SQL Server uses operating system files for operation and data storage. A best practice for file security requires that you restrict access to these files.
SQL Server Objects Security – Access to SQL Server objects like SQL Server instance, databases, tables and other objects can be restricted with SQL Server logins and database users. Granting appropriate permissions limits the users from accessing unauthorized information.
Data in SQL Server can be encrypted. Encryption enhances security by limiting data loss even in the rare occurrence that access controls are bypassed. For example, if the database host computer is misconfigured and a malicious user obtains sensitive data, such as credit card numbers, that stolen information might be useless if it is encrypted.
Application Security – SQL Server security best practices include writing secure client applications. Which involves application securely connecting to SQL Server, using appropriate protocols, using network security and network encryption.
Not only applications, but there are tools which are used to access SQL Server like SQL Server Management Studio, SQLCMD utility, SQL Server configuration manager, profiler, BIDS studio, etc. Need to ensure these tools also use secure methods to connect to databases to get the data.
Encryption to Secure SQL Server Data:
Encryption is the process of obfuscating data by the use of a key or password. This can make the data useless without the corresponding decryption key or password. In SQL Server encryption can be used for connections, data, and stored procedures. Data encryption can be performed by the OS, by SQL Server, or by the application.
There are different types of encryptions available in SQL Server which includes
Cell-level Encryption, Transparent Data Encryption, and Transport-Level Encryption.
Cell-level Encryption – Cell-level encryption allows sensitive data to be encryption at a finer level of detail than the entire database. In a table, you might just want to encrypt certain column like salary or credit card number which should not be visible.
Benefits of using Cell-Level Encryption:
(1) Granular, user specific control on encrypting individual cells or column values rather than entire databases (compared to using Transparent Data Encryption – TDE).
(2) Data retains its encrypted state in memory unless it is actively decrypted.
Drawbacks of Cell-Level Encryption:
(1) Requires application changes and analysis of tables to locate sensitive data that needs to be encrypted.
(2) Encryption of data introduces randomization. This makes it impossible to index data and causes a performance impact since indexes on encrypted columns cannot be used while searching for a value.
(3) Cell-level encryption built-in functions only return varbinary type data and the output is limited to up to 8000 bytes.
Transparent Data Encryption – Transparent Data Encryption (also called as TDE) is a technology in SQL Server that offers encryption of data-at-rest. This feature automatically encrypts the entire database (data and log files), as well as database backups, without requiring any programming or code changes to your application. The process is entirely transparent, hence the name Transparent Data Encryption.
When TDE is first enabled for a specific database, SQL Server encrypts the database in the background. During this process, the database remains online and responsive to client requests (similarly, when encryption is disabled, SQL Server decrypts the database in the background). Encryption is performed at the page level, and does not increase the size of the database in any way. Once the entire database is encrypted, new data gets encrypted on the fly as it is written to disk, and all data gets decrypted when read back.
Benefits of using TDE:
(1) Ease of implementation and transparency: TDE is essentially a “flip-the-switch” solution that allows you to encrypt your entire database and log files without application modifications.
(2) Additional security compared to cell-level encryption: TDE automatically encrypts tempdb and the database log files to prevent data leakage.
(3) Any data-type supported: Unlike cell-level encryption that returns only varbinary data, TDE allows you to store data using any native data type.
(4) Support for indexes: Because data is decrypted in the buffer pool, TDE allows the SQL Server query processor to use existing indexes on the data.
Drawbacks of TDE:
(1) Lowest support encryption granularity is the database.
(2) The data is not protected from authenticated, authorized database users, including the DBA.
Transport-Level Encryption – Secure Sockets Layer (SSL) encryption enables transmitting encrypted data across the network between an instance of SQL Server and a client application. SSL is used in SQL Server to provide security at transport level. It is most commonly used to support web clients, but it can also be used to support SQL Server native clients.
Secure Sockets Layer (SSL) is a protocol for establishing a secure communication channel to prevent the interception of critical or sensitive information across the network and other Internet communications. SSL allows the client and the server to authenticate the identity of each other. After the participants are authenticated, SSL provides encrypted connections between them for secure message transmission.
Enabling SSL encryption increases the security of data transmitted across networks between instances of SQL Server and applications. However, enabling encryption does slow performance.
Encryption Mechanisms – SQL Server provides the following mechanisms for encryption:
Transact-SQL functions – Individual items can be encrypted as they are inserted or updated using Transact-SQL functions.
Asymmetric keys – An asymmetric key is made up of a private key and the corresponding public key. Each key can decrypt data encrypted by the other. Asymmetric encryption and decryption are relatively resource-intensive, but they provide a higher level of security than symmetric encryption. An asymmetric key can be used to encrypt a symmetric key for storage in a database.
Symmetric keys – A symmetric key is one key that is used for both encryption and decryption. Encryption and decryption by using a symmetric key is fast, and suitable for routine use with sensitive data in the database.
Certificates – A public key certificate, usually just called a certificate, is a digitally-signed statement that binds the value of a public key to the identity of the person, device, or service that holds the corresponding private key. Certificates are issued and signed by a certification authority (CA). The entity that receives a certificate from a CA is the subject of that certificate.