June 9, 2008 at 11:28 pm
Dear all,
I want to encrypt my Employees table's emppassword column, but it always returns NULL, why ?
SELECT * FROM Employees
empid mgrid empname salary emppassword
----------- ----------- ------------------------- --------------------- -----------
1 NULL Nancy 10000.00 abc
2 1 Andrew 5000.00 abc
3 1 Janet 5000.00 abc
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'abc90123###'
CREATE CERTIFICATE MyCert WITH SUBJECT = 'My DB Access', START_DATE = '06/10/2008',
EXPIRY_DATE = '10/31/2010'
CREATE SYMMETRIC KEY MySymKey WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE MyCert
OPEN SYMMETRIC KEY MySymKey DECRYPTION BY CERTIFICATE MyCert
-- I wanted to encrypt the emppassword column
UPDATE dbo.Employees SET emppassword = EncryptByKey(KEY_GUID('MySymKey'),emppassword)
-- Then I try to decrypt it :
OPEN SYMMETRIC KEY MySymKey DECRYPTION BY CERTIFICATE MyCert
SELECT CONVERT(CHAR(10),DecryptByKey(emppassword)) FROM dbo.Employees
The result was :
----------
NULL
NULL
NULL
Why ? Please help me.
June 10, 2008 at 1:01 am
After reading this article, I know the cause of my problem and fixed it :
http://www.sqlservercentral.com/Forums/Topic252605-23-1.aspx
The reason is my "emppassword" column is not long enough to store the encrypted data.
June 12, 2008 at 5:15 am
Replace your decrypt query with :
SELECT CONVERT(NVARCHAR(100),DecryptByKey(emppassword)) FROM dbo.Employees
i.e replace char with nVarchar.
For further help see: http://allaboutoraclemssql.blogspot.com/2008/06/sql-2005-encryption.html
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply