-
Encrypt KEY
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
-
Re: Encrypt KEY
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
Code:
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??
-
Re: Encrypt KEY
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.
-
Re: Encrypt KEY
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?
-
Re: Encrypt KEY
I used this statement too :confused:
-
Re: Encrypt KEY
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.
-
Re: Encrypt KEY
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!!!!!!!!
-
Re: Encrypt KEY
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.
-
Re: Encrypt KEY
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
-
Re: Encrypt KEY
(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!!!!!
-
Re: Encrypt KEY
Are you still having problems with this?
-
Re: Encrypt KEY
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
-
Re: Encrypt KEY
Did you ever try that MSDN Technet site??
-
Re: Encrypt KEY
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.
-
1 Attachment(s)
Example with
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.
Code:
Dim strDataBaseFileName As String = "c:\Code Files\symmetricKey\symmetricKeyTest.mdf"
When you click on the Create Database button, you will get an error :mad: , 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.