Encryptbykey question

[Dashley]

I doing my first encryptionwork with sql server. So far so good when using embedded vlaues

This works fine

INSERT INTO TDterminals (cid,number,expdate,secid) VALUES (456, encryptbykey( @KeyGuid, N'4549-5678-4444-4444),'05/2010', 222)

Now I want to replace the credit card number '4549-5678-4444-4444444444' with a Var. Declare @ccnbr char(16) SET @CCNBR = '4549-5678-4444-4444

I cant fingure out what to do with the "N" showing in front of the embedded card number N'4549-5678-4444-4444'

If I remove it and replace the number with @ccmbr it goes in the data base but is unreadable on the way out.
I'm staring at BOL and I beleive this is the cleartext attribut but am not sure how to set it up.

Any Help appreciated.

 

[foxdev]

Caveat: I've never used EncryptByKey.
The first issue, I think, is that you're defining @ccnbr as char(16) but then trying to store 19 characters (the 16 digits plus 3 hyphens).

Separately, you can use unicode literals (using the N'string' format) with the nchar data type, or non-unicode with char.

--------------

http://www.liquidsql.com

SQLS metasearch

 

[SQLSister]

the n indicates that it is using unicode. You need to store it in a nvarchar or nchar datatype.

"NOTHING is more important in a database than integrity." ESquared

 

[Dashley]

Here's the table build and the insert with embedded values in for the credit card number.

CREATE TABLE [dbo].[TDterminals](
	[indx] [int] IDENTITY(1,1) NOT NULL,
	[cid] [int] NOT NULL,
	[number] [varbinary](256) NOT NULL,
	[expdate] [datetime] NOT NULL,
	[secid] [int] NOT NULL
) ON [PRIMARY]

DECLARE @KeyGuid AS UNIQUEIDENTIFIER
SET @KeyGuid = key_guid( 'SecretTable_SecretData_Key')
IF( @KeyGuid is not null )
BEGIN
 INSERT INTO TDterminals (cid,number,expdate,secid) VALUES (123, encryptbykey( @KeyGuid, N'4388-1234-1234-1234'),'02/2009', 111)
END

When I try to do an insert I dont know how to handle the "N"