SQL Server Performance Forum – Threads Archive

NULL Value for encryption value

When I ran follwing scripts I am getting NULL for last statment. Drop CERTIFICATE CertificateTest1 CREATE CERTIFICATE CertificateTest1
ENCRYPTION BY PASSWORD = ‘[email protected]$$w0d’
WITH SUBJECT =’This is Test certificate 1′,
START_DATE = ’12/1/2006′,
EXPIRY_DATE = ’12/31/2006′ SELECT EncryptByCert(Cert_ID(‘CertificateTest1′),’scott’) select CAST(DecryptByCert(Cert_ID(‘CertificateTest1’),EncryptByCert(Cert_ID(‘CertificateTest1′),’scott’) ) as varchar(50)) But if I create a CERTIFICATE with Database Master Key it will work. any reason? —————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/

---

Check this out… SELECT convert(Varchar(50), DecryptByCert(Cert_Id(‘CertificateTest1’),
EncryptByCert(Cert_ID(‘CertificateTest1′),’scott’) , N’[email protected]$$w0d’)) http://msdn2.microsoft.com/en-us/library/ms178601.aspx
Mohammed U.

---

thanks for the reply still I am not getting any value
I think it is nothing to do with convert SELECT DecryptByCert(Cert_Id(‘CertificateTest1’),
EncryptByCert(Cert_ID(‘CertificateTest1′),’scott’) , N’[email protected]$$w0d’) also returning null
—————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/

---

The following code is working fine for me… Drop CERTIFICATE CertificateTest1 CREATE CERTIFICATE CertificateTest1
ENCRYPTION BY PASSWORD = ‘[email protected]$$w0d’
WITH SUBJECT =’This is Test certificate 1′,
START_DATE = ’12/1/2006′,
EXPIRY_DATE = ’12/31/2006′ SELECT EncryptByCert(Cert_ID(‘CertificateTest1′),’scott’) — select CAST(DecryptByCert(Cert_ID(‘CertificateTest1’),EncryptByCert(Cert_ID(‘CertificateTest1′),’scott’) ) as varchar(50))
—
SELECT convert(Varchar(50), DecryptByCert(Cert_Id(‘CertificateTest1’),
EncryptByCert(Cert_ID(‘CertificateTest1′),’scott’) , N’[email protected]$$w0d’)) Out put..
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
0xF2071FDDCC048C60E46537A9CFE8ED8E7557E7E9FA16F0D312395D27BC64339693258CCD3FD4FEF55442C11211703185D5719FBC397364872EC7EEDCC1EFB7E8A0660C93DDB32C2B7EB4549C7D883BA9FB90A54F3126363C4F8D96768E78776CCB2DB785AC75C8321010D9C61F7252BEEB3A3E4A676EF4747A88BD10B725654C (1 row(s) affected)
————————————————–
scott (1 row(s) affected) Mohammed U.

---

oops. I missed the password in DecryptByCert. Thank you very much Mohamed —————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/

---

]]>