Sql-server – PGP for securing the database

cryptographyencryptionsql server

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.

Best Answer

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.

Related Question