Encrypt and ?Decrypt Data?

I Also Post this to SQL Server Security section no one aswered.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1047735&SiteID=1

Here is the post:

CREATE TABLE TabEncr (
id int identity (1,1),
NonEncrField varchar(30),
EncrField varchar(30)
)

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'OurSecretPassword'
CREATE CERTIFICATE my_cert with subject = 'Some Certificate'
CREATE SYMMETRIC KEY my_key with algorithm = triple_des encryption by certificate my_cert

OPEN SYMMETRIC KEY my_key DECRYPTION BY CERTIFICATE my_cert
INSERT INTO TabEncr (NonEncrField,EncrField)
VALUES ('Some Plain Value',encryptbykey(key_guid('my_key'),'Some Plain Value'))
CLOSE SYMMETRIC KEY my_key

OPEN SYMMETRIC KEY my_key DECRYPTION BY CERTIFICATE my_cert
SELECT NonEncrField,CONVERT(VARCHAR(30),DecryptByKey(EncrField))
FROM dbo.TabEncr
CLOSE SYMMETRIC KEY my_key

What is the problem with this code. It works fine , inserting the value encrypted but when i try to decrypt ,it returns a null value. What is missing. I also tried with symmetric key encryption with asymmetric key. Result is same, returns NULL value. I am using SQL 2005

Happy Coding...

Do you have permissions on that key ? otherwise it will return false.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||How can i know it. And is it possible not to be owner of the key which i created. And it returns NULL value like i say that. What do u mean with false?|||

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1047735&SiteID=1

Answered. Thanks