Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

MS SQL 2008 R2, Table Filed Value --> Encryption & Decryption (if length > 30)

Posted on 2011-10-17
5
Medium Priority
?
249 Views
Last Modified: 2012-08-13
Hi,

   Note:- This question has some relation with the question "http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_27322186.html".

ENCRYPTION-CHRIS.sql
Truncated decrypted text

   The above attached SQL QUERY / ENCRYPT ALGORITHM QUERY is working fine if the PLAIN TEXT length is less than or equal to 30.

  If the PLAIN TEXT length is greater than 30 then, the ENCRYPTION or DECRYPTION algorithm truncates the remaining characters.

  How can I solve this issue?

  I want to encrypt and decrypt 255 length PLAIN TEXT. Is it possible or not?

  If possible, please let me know the sample code.
  Othewise, please let me know the alternate solution with sample codes.
0
Comment
  • 2
  • 2
5 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36978504
can you please change the
CONVERT(varbinary
CONVERT(varchar

into:
CONVERT(varbinary(max)
CONVERT(varchar(max)

also, check the options to see if there is a "max" setting of 30 for the varchar output fields ...
0
 
LVL 9

Expert Comment

by:sachinpatil10d
ID: 36978543
Try this
-- PART 7

INSERT INTO CHRIS_TABLE (PasswordClear) VALUES ('CHRIS 1234567890 1234567890 1234567890');
OPEN SYMMETRIC KEY CHRIS_TEST_KEY
   DECRYPTION BY CERTIFICATE CHRIS_ENC_DECR_TEST;
UPDATE [CHRIS_TABLE] SET [PasswordEncrypted] = EncryptByKey(Key_GUID('CHRIS_TEST_KEY'), [PasswordClear], 1, HashBytes('SHA1', CONVERT(varbinary , [ID])));
CLOSE SYMMETRIC KEY CHRIS_TEST_KEY;
OPEN SYMMETRIC KEY CHRIS_TEST_KEY
   DECRYPTION BY CERTIFICATE CHRIS_ENC_DECR_TEST;
UPDATE [CHRIS_TABLE] SET [PasswordDecrypted] = CONVERT(NVARCHAR(max), DecryptByKey([PasswordEncrypted], 1, HashBytes('SHA1', CONVERT(varbinary, [ID]))));
CLOSE SYMMETRIC KEY CHRIS_TEST_KEY;

Open in new window

0
 

Author Comment

by:chrisdtrinityphysicians
ID: 36978635
Dear angelIII,

As per the Expert Comment ID:36978504 I modified my query and attached here for your reference. ENCRYPTION-CHRIS.sql

Okay. Really I am happy. But I have 2 doubts.

(1) What is the differece betwenn VARCHAR / VARBINARY and VARCHAR(MAX) / VARBINARY(MAX) in convert statment? How will it take 30? As per me either it need to take 1 or MAX... But it is taking 30? How?

(2) Please can explain your line "also, check the options to see if there is a "max" setting of 30 for the varchar output fields ... "?
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 800 total points
ID: 36978703
here is the official "Remark" about the 30 as default length:
http://msdn.microsoft.com/en-us/library/ms176089.aspx
0
 

Author Closing Comment

by:chrisdtrinityphysicians
ID: 36978734
I am really happy
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question