Decrypt and VIEW's

• peleg

  SSCrazy

  Points: 2611

  More actions

  January 31, 2010 at 3:58 am

  #209563

  i want to decrypt a text, while i am using a view

  select CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber)) from myView where id=1

  like here : http://msdn.microsoft.com/en-us/library/ms181860(SQL.90).aspx

  when i want to decrypt, i first must open the SYMMETRIC KEY, for example :

  OPEN SYMMETRIC KEY SSN_Key_01

  DECRYPTION BY CERTIFICATE HumanResources037;

  GO

  SELECT NationalIDNumber, EncryptedNationalIDNumber

  AS 'Encrypted ID Number',

  CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))

  AS 'Decrypted ID Number'

  FROM HumanResources.Employee;

  GO

  beacuse of unabling to do that, i thought to use a scalared-function,

  but there it dosen't let me to use the "OPEN SYMMETRIC KEY"

  any idea what else can i do?

  Thanks

  Peleg

• peleg

  SSCrazy

  Points: 2611

  More actions

  January 31, 2010 at 4:44 am

  #1111307

  solved using

  http://msdn.microsoft.com/en-us/library/ms182559.aspx

• ganesan.n

  SSC Rookie

  Points: 48

  More actions

  March 3, 2010 at 9:12 am

  #1127868

  Hi,

  I am creating a symmetric key using "encryption by asymmetric" (not using certificate). I am having the same problem like not able to open symmetric key automatically. Is there any similar functions or any other alternate solutions available?

  Thanks

  -- Ganesan