Encrypt KEY

[ERUM]

I have made sp as follows

create PROCEDURE [dbo].[ProcMyProc](@ClearText nvarchar(50))
AS
BEGIN

OPEN SYMMETRIC KEY MyKey DECRYPTION BY PASSWORD= 'MY_Password_128';
grant view definition on symmetric key ::MyKey to OUTSOURCE/VerticityDev
Declare @Temp varbinary(8000);

Set @Temp =EncryptByKey(Key_GUID('MyKey'),@ClearText);
--close symmetric key MyKey;
select @Temp as temp;
END

exec [dbo].[ProcMyProc] 'ERUM'


sp is created successfully but when i run it ,it says



Msg 15151, Level 16, State 1, Procedure ProcMyProc, Line 4
Cannot find the symmetric key 'MyKey', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Procedure ProcMyProc, Line 5
Cannot find the symmetric key 'MyKey', because it does not exist or you do not have permission.

any urgent help would highly appreciated

[szlamany]

I'm guessing you are doing this in a query window from within Management Studion - right?

If so then you need GO statements to define the end of the procedure

create PROCEDURE [dbo].[ProcMyProc](@ClearText nvarchar(50)) 
AS
BEGIN

OPEN SYMMETRIC KEY MyKey DECRYPTION BY PASSWORD= 'MY_Password_128';
grant view definition on symmetric key ::MyKey to OUTSOURCE/VerticityDev
Declare @Temp varbinary(8000);

Set @Temp =EncryptByKey(Key_GUID('MyKey'),@ClearText);
--close symmetric key MyKey;
select @Temp as temp;
END

GO

exec [dbo].[ProcMyProc] 'ERUM'

I have never used the OPEN SYMMETRIC KEY.

Why are you granting VIEW definition to it from within the SPROC also??

[ERUM]

yes i m using in sql server 2005 managemnt studio
i put Go statement too .

secondly i have found on certain forum that u need to grant permission for that purpose . after that i change my sp like this

alter PROCEDURE [dbo].[ProcMyProc](@ClearText nvarchar(50))
AS
BEGIN

OPEN SYMMETRIC KEY MyKey DECRYPTION BY PASSWORD= 'MY_Password_128';
grant view definition on symmetric key ::MyKey to noor
Declare @Temp varbinary(8000);
Set @Temp =EncryptByKey(Key_GUID('MyKey'),@ClearText);
close symmetric key MyKey;
select @Temp as temp;
END
GO


but still errors like this
.
Msg 15313, Level 16, State 1, Procedure ProcMyProc, Line 5
The key is not encrypted using the specified decryptor.
Msg 15151, Level 16, State 1, Procedure ProcMyProc, Line 6
Cannot find the user 'noor', because it does not exist or you do not have permission.
Msg 15315, Level 16, State 1, Procedure ProcMyProc, Line 9
The key 'MyKey' is not open. Please open the key before using it.

[szlamany]

I believe you need to test each of those statements in a query window by itself - one step at a time - making sure each one works.

When I try this syntax on my laptop

OPEN SYMMETRIC KEY MyKey DECRYPTION BY PASSWORD= 'MY_Password_128';

Msg 15151, Level 16, State 1, Line 1
Cannot find the symmetric key 'MyKey', because it does not exist or you do not have permission.

Which makes sense since I've not created any keys.

There is a CREATE SYMMETRIC KEY statement - have you used that already?

[ERUM]

I used this statement too

[szlamany]

Forgetting about using the stored procedure for now.

In a query window - can you CREATE and then OPEN the KEY - all in one window??

If so show me that code so I can try it myself.

If that doesn't work - show me that code - so I can try it myself.

If you are looking for a tutorial on using SYMMETRIC KEYS then go to the MSDN site or the HELP from Mgt Studio and do the research.

[ERUM]

Thanks a lot for all of you

I have bit problem in sql server encrypt and decrypt function

can i save the encrypted value in variable in following select statement


when i run following query it
SELECT ENCRYPTBYKEY(KEY_GUID ('MyKey'),'Hello World')
it give me following

0x009A637026147A46B2C39803AA7A441C0100000026724F379A32BD09188B0BE060E7B4416DF2A44758EDBC7F8C04353AE5 632CC2

so that when I decrypt the value i used that variable to return back string

'Hello World'

Looking for reply!!!!!!!!

[szlamany]

Have you read any of the MSDN or BOOKS ONLINE help for these functions?

I just read the ENCRYPTBYKEY help and it's complex to say the least. I've never used any of these functions myself - but it sure appears that they are used to "store" encrypted data and then you decrypt on the way out of the DB.

If you cannot get an answer here - try the MSDN Tech Net site...

http://www.microsoft.com/technet/com...lserver.server

Post you question there - or maybe just search that site first.

[ERUM]

my problem resolve like this way
DECLARE @temp Varchar(500)
set @temp=(SELECT ENCRYPTBYKEY(KEY_GUID ('MyKey'),'Hello World'))
print @temp

SELECT CONVERT(VARCHAR,DECRYPTBYKEY(@temp)) as DECRYPT

[ERUM]

(I set password field as uniqueidentifier).While storing my data in Database after encryption and I run insert statement in database it insert all the values in the table other than password .

For example if I write

Insert into table1 (login, password) values ( ‘user1’,’x343434343434343fgdfkgdkfgdkg’)

Where ’x343434343434343fgdfkgdkfgdkg’ is encrypted data …

And when I run statement select * from table1

It shows login as user1 but password is blank.

So what should I suppose to do ….so that it store encrypted data as uniqueidentifier..

Help Required!!!!!

[Ken B]

Are you still having problems with this?

[ERUM]

yes i have bit problem but of different kind.actually i m cabable to encrypt the data but unable to store encrypted data in the database as i mention in my last post

[szlamany]

Did you ever try that MSDN Technet site??

[Ken B]

If you are using vb2005 and sql server 2005, I can provide you with a sample project that will show you how to use SYMMETRIC KEY to store a password and display it unencrypted.

[Ken B]

Everything here is from Laurentiu Cristofor's blog

http://blogs.msdn.com/lcris/default.aspx

This is a project that I wrote to teach myself how to use SYMMETRIC KEY, there are a few problems with it that has nothing to do with SYMMETRIC KEY stuff, so I never got around to fixing them:

Before you use it, you should know that the database location is hardcoded. You will need to change the location to yours.

Dim strDataBaseFileName As String = "c:\Code Files\symmetricKey\symmetricKeyTest.mdf"

When you click on the Create Database button, you will get an error , click ok and reclick the create button and the database will be created. If you can fix this problem, please let me know.

When you click on the two insert buttons, no response is return, you might want to put a messagebox in them saying the data was inserted.

If you have any questions, just let me know.