PGP for securing my database?

Question

I have considered TDE and cell level encryption mechanism for securing my database, however these two cannot fully satisfy my requirements.

I've found that PGP may help me, but it is mainly used for mail services.

What is the technical feasibility for implementing PGP for my SQL server database, is there any possibilities for that?

TDE does not satisfy my requirements because it only protects data-at-rest. I need to protect the data from an attacker who has access to the database while it is running on the server containing the encryption certificate, as well as the data contained within backups.

I'd also like to have some users able to see the decrypted values, while some other users cannot ever see the decrypted values.

1. I have implemented master-slave Replication, and need to enable security without affecting the replication.
2. Approximately 80% of coding has been completed, so its really hard to change the queries and stored procedures.
3. I need to enable security for selected tables only (payments, customer details, password, etc.)
4. I need to secure data from injection.

Answer 1

SQL Server has several built-in possibilities to secure data on a columnar basis.

1. ENCRYPTBYPASSPHRASE - use this to encrypt data with a passphrase using the TRIPLE DES algorithm with a 128 key bit length.

2. ENCRYPTBYKEY - use this to encrypt the data with a key stored in the master database. Without access to the key, the data cannot be recovered.

Take the following example using ENCRYPTBYPASSPHRASE and DECRYPTBYPASSPHRASE:

IF OBJECT_ID(N'dbo.EncryptedData', N'U') IS NOT NULL
DROP TABLE dbo.EncryptedData;
CREATE TABLE dbo.EncryptedData
(
    EncryptedDataID int NOT NULL IDENTITY(1,1)
        CONSTRAINT EncryptedData_PK
        PRIMARY KEY CLUSTERED
    , SomeData varbinary(1000) NOT NULL
    , SomeSalt binary(10) NOT NULL
        CONSTRAINT EncryptedDataSalt
        DEFAULT CRYPT_GEN_RANDOM(10)
);

DECLARE @passphrase nvarchar(100);
SET @passphrase = N'FlashFesterMariaAyin';

INSERT INTO dbo.EncryptedData (SomeData)
VALUES (ENCRYPTBYPASSPHRASE(@passphrase, N'This is a test', 1, N'SomeSalt'))

This shows the encrypted data held in the row:

SELECT ed.EncryptedDataID
    , ed.SomeData
    , ed.SomeSalt
FROM dbo.EncryptedData ed;

The encrypted row:

╔═════════════════╦════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╦════════════════════════╗
║ EncryptedDataID ║ SomeData                                                                                                                                                   ║ SomeSalt               ║
╠═════════════════╬════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╬════════════════════════╣
║ 1               ║ 0x01000000DD0FF92C67C40F30C130CED841677AB69D34765D6FD57112AC35F59A87395101B32A145E3D42F8889ECF9BDEEC45F970EDD35432178CBDE896AAC27E9363AA20D09782A385048447 ║ 0x9DB6EDA465A5F93C7ADD ║
╚═════════════════╩════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╩════════════════════════╝

This shows how to display decrypted data:

SELECT ed.EncryptedDataID
    , CONVERT(nvarchar(100), DECRYPTBYPASSPHRASE(@passphrase, SomeData, 1, N'SomeSalt'))
    , ed.SomeSalt
FROM dbo.EncryptedData ed;

The decrypted row:

╔═════════════════╦══════════════════╦════════════════════════╗
║ EncryptedDataID ║ (No column name) ║ SomeSalt               ║
╠═════════════════╬══════════════════╬════════════════════════╣
║ 1               ║ This is a test   ║ 0x9DB6EDA465A5F93C7ADD ║
╚═════════════════╩══════════════════╩════════════════════════╝

The SomeSalt column is a pseudo-randomly-generated value used to guard against whole-value-substitution attacks, and provides significant protection against rainbow-table attacks. Whole-value-substitution attacks consist of copying the encrypted value from one row to another row in order to bypass the need to know the encryption passphrase.

Answer 2

Max's answer provides some good context about how you can perform cell-level encryption, so I'm not going to re-hash that; rather, I'm going to add additional context I think you may be looking for.

2. Approximately 80% of coding has been completed, so its really hard to change the queries and stored procedures.

Sadly, if you want to use Cell-Level encryption (e.g. ENCRYPTBY*** methods) that shipped with SQL 2008, you will need to alter your queries. There's just no real way around it.

If you're running SQL 2016 SP1 or later though, you should be able to minimize your rework by enabling Always Encrypted. This is not a trivial feature to demonstrate, so instead, I'm going to point you toward Robert Sheldon's article from Redgate on how to enable and use this feature. My recommendation is that you use Always Encrypted if you can run the database on SQL 2016 SP1+ (as this feature is now included with Standard Edition, yay!).

I'd also like to have some users able to see the decrypted values, while some other users cannot ever see the decrypted values.

Using the ENCRYPTBYKEY, ENCRYPTBYASYMKEY, or ENCRYPTBYCERT methods (as demonstrated in Max's answer above, you can remove access to any encrypted data by explicitly DENY(ing) VIEW DEFINITION permissions on the symmetric key, asymmetric key, or certificate to any account (or database role) that you see fit. This will allow you to limit who has access to what within the database itself.

Using the ENCRYPTBYPASSPHRASE method however doesn't prevent users from sharing a passphrase among st themselves or others without your knowledge, so I would hesitate recommending this approach for anything truly sensitive.

Always Encrypted allows you to choose who has access to the decrypted data and who doesn't. Again, another reason to try it in your situation.

3. I need to enable security for selected tables only (payments, customer details, password, etc.)

You can encrypt many, if not all, columns in a table, but I don't think it's necessarily a good use of resources. Encryption, regardless the approach you take, requires CPU cycles to encrypt/decrypt data, so you should try to limit encrypted fields to only the data necessary.

4. I need to secure data from injection.

This is an entirely different area of threat. SQL Injection has nothing to do with the data being encrypted or not and more to do with how you pass client-side SQL to the server. I'm not going to get into this topic as many others (i.e. Microsoft, Erland Sommarskog, Aaron Bertrand) have done a much better job talking about it than I can here.

What is the technical feasibility for implementing PGP for my SQL server database, is there any possibilities for that?

Sure, you can use PGP, but your encryption and decryption routines will need to be handled at either the Application layer or by custom CLR routines. In either case, a rewrite to something is likely needed. There are also native alternatives as already discussed that I think should be used over PGP, but really your criteria will dictate what will work better.

Where is the data encrypted?

Wait, you didn't ask that question.... but you should have. All of the ENCRYPTBY*** routines identified above encrypt the data at the database layer only. They decrypt the data on the server and send it over the wire. Unless you're forcing SSL connections to SQL, this data will be traversing your network in plain text and anyone with a packet sniffer could potentially grab it in flight. Encrypting/Decrypting data on the client will ensure the data is encrypted across the wire, but not all approaches will allow for this.

Always Encrypted will perform the encryption at the client though, so with it your data is also safe in transit. It's really the best option for you and I would suggest you lobby to get SQL 2016 if you don't already have it.

Answer 3

lucky,

Have you considered using 3rd party tool like DBDefence ? It can automatically encrypt database transparently, so you would not have to change anything in your application. As you required, you may leave some tables unprotected.

You may authorize only your digitally signed application or a login with certain password to access the database.

Finally, DbDefence supports PKCS#11 modules and you can add your own encryption even PGP :) However it is not really applicable for that kind of encryption.

Software is free for SQL Server 2005 and 2008. For all other SQL Server versions it is free with small databases.

Disclaimer: I'm associated with the vendor.