can we use integer value Encrypt/Decrypt with Symmetric key.

• asitkumarsinha

  SSC Enthusiast

  Points: 130

  More actions

  May 30, 2008 at 3:21 am

  #191285

  Hi

  I am using sql server 2005, I want to encrypt data and i am using Symmetric key.

  In Symmetric key encrypt the varchar , varbinary data encrypt but for integer it show the error.

  so please suggest me can we encrypt the integer data.

  if yes then how ?

  In below example if we use integer then encrypt it will show error.

  --1) Create master key.

  CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Asit'

  --2) Create certificate.

  CREATE CERTIFICATE CertificateTest2

  WITH SUBJECT ='CertificateTest2'

  --3) Create SYMMETRIC KEY .

  CREATE SYMMETRIC KEY TestSymKey

  WITH ALGORITHM = TRIPLE_DES

  ENCRYPTION BY CERTIFICATE CertificateTest2

  --4) Open SYMMETRIC KEY .

  OPEN SYMMETRIC KEY TestSymKey

  DECRYPTION BY CERTIFICATE CertificateTest2;

  --5) create table.

  create table SYMMETRIC1

  (

  id int not null primary key ,

  name varbinary(MAX) not null,

  card_num varbinary(MAX) not null

  );

  --6)SEA THE RECORDS

  SELECT * FROM SYMMETRIC1

  --7)INSERT RECORD IN TABLE

  INSERT INTO SYMMETRIC1(ID,NAME,CARD_NUM)VALUES (

  EncryptByKey( key_guid('TestSymKey'), 2,1,'SQL Server') ,

  EncryptByKey( key_guid('TestSymKey'),'Asit sinha',1,'SQL Server'),

  EncryptByKey( key_guid('TestSymKey'),'11',1,'SQL Server') )

  --8)FETCH ENCRYPTED RECORD FROM TABLE AND DECRYPT THE VALUE

  SELECT

  convert(varchar(max),DecryptByKey(id,1,'SQL Server')) as ID,

  convert(varchar(max),DecryptByKey(Name,1,'SQL Server')) as Name,

  convert(varchar(max),DecryptByKey(card_num,1,'SQL Server')) as Card_Num

  FROM SYMMETRIC1

  --9)SEA THE RECORDS

  SELECT * FROM SYMMETRIC1

  Asit Sinha

  :hehe:

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  June 4, 2008 at 11:34 am

  #824375

  Have you considered converting the integer to a fixed-length character string, using CAST or CONVERT, prior to encryption?

  Steve

  (aka smunson)

  :):):)

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)