Encryption is a good technique using which a particular data is obfuscated with a key or password. This makes the data useless until any decryption key or password is used to unlock it. Encryption makes data secure and prevents data loss, even if access controls are bypassed. Users can use encryption in SQL Server for connections, stored procedures and data.
In SQL Server data is encrypted by hierarchical encryption and key management infrastructure. Each encryption layer is used to encrypt the layer below it by using asymmetric keys, symmetric keys, and a combination of certificates. Both Asymmetric keys and Symmetric keys are stored outside of SQL Server in an Extensible Key Management (EKM) module.
Below given figure shows how encryption hierarchy plays its role and encrypts a database. Here PW denotes the password.
Note: Encrypt data using symmetric keys instead of asymmetric keys and certificates for better performance.
Outside the SQL server, an EKM module holds both the symmetric or asymmetric key.
Service master key protects database master keys. The SQL Server setup creates service master key encrypted by DPAPI (Windows Data Protection API). The Service Master key and Database Master keys, are all symmetric keys.
Mechanism of Encryption:
SQL Server provides the following types of Encryption Mechanism.
- Transact SQL-functions
- Asymmetric Keys
- Symmetric Keys
- Certificates
- Transparent Data Encryption
Transact-SQL Functions
Using transact-SQL functions, user can encrypt individual data items while they are inserted or updated. For more information, see ENCRYPTBYPASSPHRASE(Transact-SQL) and DECRYPTBYPASSPHRASE(Transact-SQL).
Certificates
Certificates that are often called the Public Key Certificates, is a digitally-signed statement that binds the value of the public key to the identity of a particular person, service or device that holds private key. Certification authority allows signing and issuing the certificates. The subject of the certificate is the one that receives a certificate from a CA. Certificates contain the following information.
- Public key of the subject.
- Identifier information like; name and email, etc.
- The valid time period of the certificate. When the validity of the certificate is expired, then the new certificate must be requested.
- Identifier information of the issuer.
- The digital signature of the issuer. In this process data is transformed (some secret information of the sender) into a tag called, Signature.
- Server created self signed certificates follow X.509 standard and X.509 v1 fields.
Asymmetric Keys
An Asymmetric Key is a combination of public and private key. Each key used to decrypt data encrypted by the owner. Asymmetric encryption provides a higher level of security comparable to symmetric keys and are relatively-intensive. It is used to encrypt a symmetric key for storage purpose in the database.
Symmetric Keys
A symmetric key is used for both encryption and decryption of the data information. By using symmetric key the process of encryption and decryption has been made faster and suitable for sensitive data stored in the database.
Transparent Data Encryption
TDE is used to encrypt entire database using symmetric key called Database Encryption Key. The database key is protected by other certificates or keys which are protected by the master or by an asymmetric key, stored in an EKM module. TDE uses symmetric key for encryption.
Conclusion:
In the above paragraph I have explained all the encryption techniques and their types in SQL Server along with a brief description about their process or uses. The encryption process is more important considering a part of the SQL Server database that guarantees data security by its complex techniques. For more details about the encryption type, kindly wait for the next blog update.
