Encryption problem

Hi All,

Currently for a project for the company I work for, we are looking to migrate from SQL Server 2000 to 2005. As part of the investigation I am looking at the some of the new features in 2005.

Looking at the encryption facilities which we are looking to use I have come across a problem (or perceived problem)

I have the following test table (where in production we will replace CardNo with the CustomerID)

//--

USE [Encryption2]
GO
/****** Object: Table [dbo].[Cards] Script Date: 11/13/2006 11:42:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Cards](
[CardNo] [nvarchar](50) COLLATE Latin1_General_CI_AI NULL,
[Encrypted] [varbinary](max) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

//--

I'm creating test records using an INSERT as follows

insert into cards (cardno, encrypted) values ('1111-2222-3333-4444', ENCRYPTBYPASSPHRASE('adminpw', '1111-2222-3333-4444'))

in trying to replicate how we may send data to the db.

On retrieving the data using

select CardNo, Encrypted, convert(nvarchar(50), DECRYPTBYPASSPHRASE('adminpw', Encrypted)) as decrypted from cards

The 'decrypted' column is not returning the expected '1111-2222-3333-4444' but a corrupted value.

Update the data using

update Cards set encrypted = ENCRYPTBYPASSPHRASE('adminpw', CardNo) where CardNo = '1111-2222-3333-4444'

and the decrypted value returned is '1111-2222-3333-4444'.

Update using

update Cards set encrypted = ENCRYPTBYPASSPHRASE('adminpw', '1111-2222-3333-4444') where CardNo = '1111-2222-3333-4444'

and the value is once again invalid.

Am I doing anything wrong? Any ideas and help would be welcome.

Thanks

So I did a quick test. It looks like this is happening because you are encrypting a non-unicode string, then decrypting it and converting it to unicode, which results in garbled text. If you replace:

'1111-2222-3333-4444'

with

N'1111-2222-3333-4444'

On your insert, I believe this should work. Please let me know if it does not.

Thanks,

Sung

|||

Note, this works on the update where you select from the column because you are explicitly selecting from a unicode column. Thus in that one case you are using a unicode string whereas in the other cases you are using a non-unicode string.

Thanks,

Sung

|||Works a treat, thanks for the help. :)