0

I am trying to encrypt some sensitive information on a database (SQL Server 2008 R2 Enterprise Edition) using the TDE encryption.

This is the test code I am doing :

use test1
create table users1(pid int not null, username varchar(20), pass varchar(20), encryptedpass varchar(5000))
GO

insert into users1 values(123456, 'mark', 'qwerty', '')
insert into users1 values(123448, 'katy', 'poiuytr', '')
insert into users1 values(147384, 'dick', '567tgbyhn', '')
insert into users1 values(844749, 'sam', 'ujnuhbygv', '')
insert into users1 values(585948, 'max', 'wsxedcrfv', '')
insert into users1 values(383929, 'jake', '1qaz2wsx', '')

CREATE MASTER KEY ENCRYPTION BY 
PASSWORD = 'MaxSecurity'
GO

CREATE CERTIFICATE TestCert1
WITH SUBJECT = 'Password_Encryption';
GO

CREATE SYMMETRIC KEY Keyx
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE TestCert1;
GO

OPEN SYMMETRIC KEY Keyx
DECRYPTION BY CERTIFICATE TestCert1;

UPDATE test1.dbo.users1
SET encryptedpass = EncryptByKey(key_guid('Keyx'), pass);

CLOSE SYMMETRIC KEY keyx;
GO

However, when doing 'select * from users1' I am not able to view the encrypted column. The permissions are set to view as well.

Requesting help!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jigar Gada
  • 1

1 Answers1

0

Your column datatype for encryptedpass should be varbinary, not varchar.

How is the data "not visible"?

You should be able to open the symmetric key, and then query the column with the decryptbykey function, for example,

select decryptbykey(encryptedpass) from users1;

(You may also want to cast the result back to varchar.)

If you just

select * from users1;

then you should see binary data in the encryptedpass column (with column datatype varbinary).

Note that this is not "TDE" -- what you are using is "column-level" or "cell-level" encryption. When using TDE, the entire database is encrypted, transparently, and there is no need for encryptbykey/decryptbykey. Cf. Transparent Data Encryption (TDE).

Jim Flood
  • 8,144
  • 3
  • 36
  • 48