How to encrypt/Decrypt Data

Question

Post reply

How to encrypt/Decrypt Data

komnaz

Grasshopper

Points: 24
More actions
April 20, 2010 at 11:38 am

#217994

Hi
Please help me. I want to do encryption/Decyption, i am giving examples
Example: http://www.google.com/Search/Test
Here i want to encrypt the only Test in this Url .After encrypting that should be like
http://www.google.com/Search/any encypted values(12we?12) and after decrytion that should ibe in origional form(www.google.com/Search/Test)
Note: In Sql Server2008/2005 i am unable to convert Varbinary to Varchar
Thanks

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323547 More actions · Answer 1

for encryption, i really like this article; lots of example,s and easy to follow along:

http://www.databasejournal.com/features/mssql/article.php/3483931/SQL-Server-2005-Security---Part-3-Encryption.htm

you know you have to encrypt the entire string, and not just part of it right?

so for your url example,

if not exists(SELECT * FROM sys.certificates WHERE name ='ElmerEncryptionCertificate')

BEGIN

CREATE CERTIFICATE ElmerEncryptionCertificate

WITH

SUBJECT = 'Elmer Certificate',

EXPIRY_DATE = '01/01/2011 '

PRINT '"Elmer Certificate" Created.'

END

ELSE

BEGIN

PRINT '"Elmer Certificate" Already Exists.'

END

DECLARE @encryptedstuff NVARCHAR(100)

SELECT @encryptedstuff = EncryptByCert(Cert_ID('ElmerEncryptionCertificate'), N'www.google.com/Search/Test')

SELECT @encryptedstuff

SELECT CAST(DecryptByCert(Cert_ID('ElmerEncryptionCertificate'), @encryptedstuff) AS NVARCHAR)

http://www.google.com/Search/Test

--becomes stuff that's not even readable

??????????????????????????????????????????????????Y?????????????

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

komnaz Grasshopper Points: 24 More actions · Answer 2

Thanks for the reply..But the result is in some Ascii or Sybolic form ...I require this encrypted value in Guid form.I can generate using Key_guid but it is Varbinary data here i cant convert into Varchar.

Please see again example : I have to encrypt the Part of the URI . In http://www.google.com/Search/Test here i have to encrypt Test only.if i use Key_Guid then it will be encrypted in GUID form but i am unable concatenat this encrypted value with http://www.google.com/Search because it is varchar.

Lowell SSC Guru Points: 323547 More actions · Answer 3

you'll need to paste the exact code you are using; encrypting part of a string, then concatenating the decrypted results is easy, so it's probably something fairly basic that is tripping you up.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!