SQL Server 2005 Data encryption problem

This is a discussion on SQL Server 2005 Data encryption problem within the Sharepoint forums in Microsoft Tools category; 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'), ...

Go Back   Application Development Forum > Microsoft Tools > Sharepoint

Object Mix

Register FAQ Calendar Search Today's Posts Mark Forums Read
  #1  
Old 09-07-2008, 06:15 PM
Ajit
Guest
 
Default SQL Server 2005 Data encryption problem

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

Reply With Quote
Reply


Thread Tools
Display Modes


All times are GMT -5. The time now is 08:46 PM.


Powered by vBulletin® Version 3.7.2
Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.
Search Engine Optimization by vBSEO 3.2.0
vB Ad Management by =RedTyger=

In an effort to better serve ads to our visitors, cookies are used on objectmix.com. For more information, check out our Privacy Policy.