Windows OS Encryption Interface
The CryptProtectData() requires a password to create the cipher. The LSA provides the existing user's password hash. Obviously all applications running under the same user account will be able to access these functions and decrypt any protected data if aware of the location under the same context.The operating system however only provides the mechanism for data protection. This implies that the applications calling these functions should implement their own storage for protected data. When you aren’t aware of the location where the cipher text is stored; the functions and password hash serve no purpose to get unauthorized access to sensitive information. But it’s certainly possible hence DPAPI allows an application to use an additional secret when protecting data.
Encryption in SQL server.
Thus we have 3 techniques to encrypt data in SQL server
1) Symmetric Keys
Symmetric keys use the same key to encrypt and decrypt data.
2) Asymmetric Keys
Asymmetric key ideally uses a public key to encrypt data and a matching private key is used to decrypt the data. It can also be used vice versa but only the matching pair of keys can decrypt the data encrypted by other.
3) Certificates.
Certificates are containers for the user's public key which store additional data identifying the organization that issued the certification.
Until SQL server 2005 these were the only 3 techniques for encryption. With the advent of SQL server 2008 SQL server introduced 2 new techniques for encryption namely
This feature is available in all editions of SQL server. This is also known as Cell-Level Encryption /Column Level Encryption in SQL server. SQL server makes use of the below encrypt,decrypt functions to protect data within columns of record.