[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Problem With SQL Server 2008 R2 Encryption

Posted on 2011-05-12
2
Medium Priority
?
538 Views
Last Modified: 2012-05-11
I'm using SQL Server 2008 R2 Standard Edition and I am trying to figure out encryption. I have done the below without the expected results. Can someone help?

Here are the results:

Encrypted = 0x005A871A34210549973A890DB48C9203010000002278EAE8B8F01147EE0B3C87AC83CB41705196404D43193FCFE8C199B1A4E2BF731C1333667EA445AA00B82F5D237C08

Decrypted = 0x3132332D34352D36302D39333730

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJKL969#ghf0%94467GRkjg5k3fd117r$$#1946kcj$n44nhdlj'
GO
CREATE CERTIFICATE TestCERTIFICATE WITH SUBJECT = 'Test CERTIFICATE';
GO
CREATE SYMMETRIC KEY TestKey WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE TestCERTIFICATE;
GO
OPEN SYMMETRIC KEY TestKey DECRYPTION BY CERTIFICATE TestCERTIFICATE;
go
select	EncryptByKey(Key_GUID('TestKey'), '123-45-60-9370'),
		DecryptByKey(EncryptByKey(Key_GUID('TestKey'), '123-45-60-9370'));
GO
close SYMMETRIC KEY TestKey
go

Open in new window

0
Comment
Question by:GoCubs
  • 2
2 Comments
 

Accepted Solution

by:
GoCubs earned 0 total points
ID: 35747017
I got it. I need to change
select      EncryptByKey(Key_GUID('TestKey'), '123-45-60-9370'),
            DecryptByKey(EncryptByKey(Key_GUID('TestKey'), '123-45-60-9370'));
to
select      EncryptByKey(Key_GUID('TestKey'), '123-45-60-9370'),
            convert(varchar,DecryptByKey(EncryptByKey(Key_GUID('TestKey'), '123-45-60-9370')));
0
 

Author Closing Comment

by:GoCubs
ID: 35747019
I found it elsewhere online
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Sometimes drives fill up and we don't know why.  If you don't understand the best way to use the tools available, you may end up being stumped as to why your drive says it's not full when you have no space left!  Here's how you can find out...
Resolving an irritating Remote Desktop connection that stops your saved credentials from being used.
This tutorial will walk an individual through locating and launching the BEUtility application to properly change the service account username and\or password in situation where it may be necessary or where the password has been inadvertently change…
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
Suggested Courses

873 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