Hi,

We are using SQL Server 2005 Express edition.We encrypt data in the table
using asymmetric keys:

CREATE ASYMMETRIC KEY MyKey AUTHORIZATION dbo With ALGORITHM = RSA_512
ENCRYPTION BY PASSWORD = 'wcixoOEb5h77gh5'

This was run aginst the master database.To our surprise, what we found is
that SQL Server won't accepts any key names starting with nonalphabetic
characters. We do the encryption as follows :

INSERT INTO CardInfo (OrderId, PAN, ExpDate, CardType, LastCardDigits)
VALUES('10004',
CONVERT(varbinary(300),EncryptByAsymKey(AsymKey_ID ('MyKey'),'1001100110011001')),
CONVERT(varbinary(150),EncryptByAsymKey(AsymKey_ID ('MyKey'),'1210')),
CONVERT(varbinary(100),EncryptByAsymKey(AsymKey_ID ('MyKey'),'VI')),
'1001')

Now we try to decrypt the data using the key name and password.

SELECT CONVERT(nvarchar(300),DecryptByAsymKey(AsymKey_ID( 'MyKey'), PAN,
N'wcixoOEb5h77gh5')),
CONVERT(nvarchar(150),DecryptByAsymKey(AsymKey_ID( 'MyKey'), ExpDate,
N'wcixoOEb5h77gh5')),
CONVERT(nvarchar(100),DecryptByAsymKey(AsymKey_ID( 'MyKey'), CardType,
N'wcixoOEb5h77gh5'))
FROM CardInfo WHERE OrderId = '10004'

But its returning some junk data only.What could be the problem ?
We have SQL Server sp2 also installed. AsymKey_ID('MyKey') returns a valid
integer.

Is there any way to trace this problem ?


With Regards
Ajit

"Ajit" <Ajit@discussions.microsoft.com> wrote in message
news:40F33B84-7E80-48F9-AD62-303101B553B1@microsoft.com...
> Hi,
>
> We are using SQL Server 2005 Express edition.We encrypt data in the table
> using asymmetric keys:
>
> CREATE ASYMMETRIC KEY MyKey AUTHORIZATION dbo With ALGORITHM = RSA_512
> ENCRYPTION BY PASSWORD = 'wcixoOEb5h77gh5'
>
> This was run aginst the master database.To our surprise, what we found is
> that SQL Server won't accepts any key names starting with nonalphabetic
> characters. We do the encryption as follows :
>
> INSERT INTO CardInfo (OrderId, PAN, ExpDate, CardType, LastCardDigits)
> VALUES('10004',
> CONVERT(varbinary(300),EncryptByAsymKey(AsymKey_ID ('MyKey'),'1001100110011001')),
> CONVERT(varbinary(150),EncryptByAsymKey(AsymKey_ID ('MyKey'),'1210')),
> CONVERT(varbinary(100),EncryptByAsymKey(AsymKey_ID ('MyKey'),'VI')),
> '1001')
>
> Now we try to decrypt the data using the key name and password.
>
> SELECT CONVERT(nvarchar(300),DecryptByAsymKey(AsymKey_ID( 'MyKey'), PAN,
> N'wcixoOEb5h77gh5')),
> CONVERT(nvarchar(150),DecryptByAsymKey(AsymKey_ID( 'MyKey'), ExpDate,
> N'wcixoOEb5h77gh5')),
> CONVERT(nvarchar(100),DecryptByAsymKey(AsymKey_ID( 'MyKey'), CardType,
> N'wcixoOEb5h77gh5'))
> FROM CardInfo WHERE OrderId = '10004'
>
> But its returning some junk data only.What could be the problem ?
> We have SQL Server sp2 also installed. AsymKey_ID('MyKey') returns a valid
> integer.
>
> Is there any way to trace this problem ?
>
>
> With Regards
> Ajit

Hi

The problem seems to be the conversion to/from nvarchar because your have
not inserted nvarchar.

either decrypt with:
SELECT CONVERT(varchar(300),DecryptByAsymKey(AsymKey_ID(' MyKey'), PAN,
N'wcixoOEb5h77gh5')),
CONVERT(varchar(150),DecryptByAsymKey(AsymKey_ID(' MyKey'), ExpDate,
N'wcixoOEb5h77gh5')),
CONVERT(varchar(100),DecryptByAsymKey(AsymKey_ID(' MyKey'), CardType,
N'wcixoOEb5h77gh5'))
FROM CardInfo WHERE OrderId = '10004'

or encrypt with:
INSERT INTO CardInfo (OrderId, PAN, ExpDate, CardType, LastCardDigits)
VALUES('10005',
CONVERT(varbinary(300),EncryptByAsymKey(AsymKey_ID ('MyKey'),N'1001100110011001')),
CONVERT(varbinary(150),EncryptByAsymKey(AsymKey_ID ('MyKey'),N'1210')),
CONVERT(varbinary(100),EncryptByAsymKey(AsymKey_ID ('MyKey'),N'VI')),
'1001')


SELECT CONVERT(nvarchar(300),DecryptByAsymKey(AsymKey_ID( 'MyKey'), PAN,
N'wcixoOEb5h77gh5')),
CONVERT(nvarchar(150),DecryptByAsymKey(AsymKey_ID( 'MyKey'), ExpDate,
N'wcixoOEb5h77gh5')),
CONVERT(nvarchar(100),DecryptByAsymKey(AsymKey_ID( 'MyKey'), CardType,
N'wcixoOEb5h77gh5'))
FROM CardInfo WHERE OrderId = '10005'

John