Issue working with symmetric key for encryption

Please be gentle...I am very new to working with SQL.

I have the need to encrypt my columns in SQL 2005. I have created a symmetric key 'SecureKey' as well as a secure certificate 'SecureCert'

I have ran the script to create the key and the certificate successfully. When I run "select * sys.symmetic_keys;" the key shows up...when I run "select * sys.certificates;" the cert shows up.

Here is my issue, in the security folder under my database, these two things do not show up in the appropriate folders. Also when I run my encryption scripts, I am getting an error of

"msg 15151, Level 16, State 1, Line 3

cannot find the symmetric key 'SecureKey', because it does not exist or you do not have permission"

Am I missing something?

Thanks

Did you open the symmetric key before you encrypt data? If the symmetric key is not opened you wont be able to work with it ... anyway here's how it's supposed to be done: (I'm in work so i cant test if code is true but if something is screwed up go to this site:

http://blogs.msdn.com/lcris/search.aspx?q=encryption+sql+server+2005&p=1

this is where i got most of info when i started)

create database demo;

use demo;

-- create the database master key

--since you created certificate already then master key must be set already
create master key encryption by password = 'Pufd&s@.))%';

-- create a simple employee table
create table t_employees (id int primary key, name varchar(300), salary varbinary(300));

-- create a certificate to protect the symmetric key that will encrypt the data
-- the certificate will be encrypted by the database master key
create certificate cert_sk_admin with subject = 'Certificate for accessing symmetric keys - for use by admin';

-- create a key to protect the employee sensitive data, in this case - the salary
create symmetric key sk_employees with algorithm = triple_des encryption by certificate cert_sk_admin;

-- open the key so that we can use it
open symmetric key sk_employees decryption by certificate cert_sk_admin;

-- verify key was opened
select * from sys.openkeys;

-- insert some data
-- we will use the id as an authenticator value to tie the salary to the employee id
insert into t_employees(id, name,salary) values ( 1,'Alice Smith', encryptbykey(key_guid('sk_employees'), '$200000'));
insert into t_employees(id,name,salary) values (2, 'Bob Jones', encryptbykey(key_guid('sk_employees'), '$100000'));

-- see the result; salary is encrypted
select * from t_employees;

-- create a view to automatically do the decryption
-- note that when decrypting we specify that the id should be used as authenticator
create view v_employees as select id, name, convert(varchar(10), decryptbykey(salary)) as salary from t_employees;

-- create another view for charles that will automatically open the key using his certificate and do the decryption
create view v_employees_auto as select id, name, convert(varchar(10), decryptbykeyautocert(cert_id('cert_sk_charles'), salary)) as salary from t_employees;

-- see the result, the decrypted data is available
select * from v_employees;

-- now close the key
close all symmetric keys;

-- verify key was closed
select * from sys.openkeys;

-- use the view that will automatically open the key
select * from v_employees_auto;

-- note that the key is also automatically closed by the above query
-- it is only opened for the duration of the query
select * from sys.openkeys;

|||

Something I have recently learned: if you are testing as a low privileged user, then you must grant privileges to that use to see the key and cert. Something like:

GRANT CONTROL ON CERTIFICATE::[cert_SecretTable_SecretData_Key] TO [user_low_priv]

GRANT VIEW DEFINITION ON SYMMETRIC KEY::[SecretTable_SecretData_Key] TO [user_low_priv]

I have read it is better to use a view, but am having some problems of my own...

this snippet is from

http://blogs.technet.com/keithcombs/archive/2005/11/24/415079.aspx

|||

Thanks for the replies...I now have the key and cert where they belong and they have started working...after I installed SP2.

NOW, I have issues with my decryption. I have ran my scripts to encrypt the data and it is working. I also ran the script to decrypt, and it worked once. I have tried to repeat the process, but with no luck... when I run the decrypt scipt it returns nothing...not a null or anything. I don't understand.

Here is my encryption

I open the key and the certificate

SELECT * FROM dbo.database

GO

UPDATE database

SET Value = EncryptByKey(Key_GUID('KeyName'), Value);

GO

close symmetric key KeyName;

Now to decrypt

SELECT Value

AS 'Encrypted Value',

CONVERT(varchar, DecryptByKey(Value))

AS 'Decrypted Value'

FROM database;

CLOSE SYMMETRIC KEY KeyName;

|||

Hi, it looks like you are closing your symmetric key after the encryption, which is good practice. To perform the decryption, however, you will need to re-open your symmetric key. Thus before you perform your SELECT, you should once again run the OPEN SYMMETRIC KEY ... statement.

Please let us know if you run into any trouble with this and we will try to answer any questions you have.

Thanks,

Sung

|||

Sung,

Thanks for the reply...I am sorry, I just forgot to put that part of the script in there. I am opening and closing the Key on the decrypt also.

Ron

|||

In:

SELECT Value

AS 'Encrypted Value',

CONVERT(varchar, DecryptByKey(Value))

AS 'Decrypted Value'

FROM database;

you need to specifi length of varchar like this:

CONVERT(varchar(50), DecryptByKey(Value))

or something...