In symmetric key integer value will encrypt

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

In symmetric key integer value will encrypt

Author

Topic

Asit
Starting Member

6 Posts

Posted - 2008-05-29 : 08:12:42

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 varchar(100) 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

ranganath
Posting Yak Master

209 Posts

Posted - 2008-05-30 : 01:09:33

Hi,

try with this

Create Table dbo.temp (Id Int identity(1,1), Login Varchar(100))
Insert into Temp
Select 'Ranga' union all
Select 'Sai' union all
Select 'Nath' union all
Select 'Keerthi'

--Select * From Temp

IF NOT EXISTS
( SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = '23987hxJKL95QYV4369#ghf0%lekjg5k3fd117r$$#1946kcj$n44ncjhdlj'

CREATE CERTIFICATE Sales09
WITH SUBJECT = 'Customer Credit Card Numbers';

CREATE SYMMETRIC KEY CreditCards_Key11
WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE Sales09;

ALTER TABLE Temp
ADD pwd varbinary(128);

OPEN SYMMETRIC KEY CreditCards_Key11
DECRYPTION BY CERTIFICATE Sales09;

UPDATE temp
SET pwd = EncryptByKey(Key_GUID('CreditCards_Key11')
, [Login], 1, HashBytes('SHA1', CONVERT( varbinary , id)));

OPEN SYMMETRIC KEY CreditCards_Key11
DECRYPTION BY CERTIFICATE Sales09;

SELECT id, [Login], Pwd
AS 'Encrypted Pwd' , CONVERT(varchar, DecryptByKey(pwd, 1 , HashBytes('SHA1', CONVERT(varbinary, ID)))) AS 'Decrypted Pwd'
FROM Temp

Select * From Temp

Drop table Temp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources