Encrypting stroredprocedures

Anybody can help me for encrypting & decrypting Stored procedures...This is straight from the help file.

If you are creating a stored procedure and you want to make sure that the procedure definition cannot be viewed by other users, you can use the WITH ENCRYPTION clause. The procedure definition is then stored in an unreadable form.

After a stored procedure is encrypted, its definition cannot be decrypted and cannot be viewed by anyone, including the owner of the stored procedure or the system administrator

Here is an example:
CREATE PROCEDURE FactorAddRecord2
(
@.iSecurityId dINTEGER,
@.iFactor dNUMERIC_15_8
)
/**************************************************
DESCRIPTION:
AUTHOR:
DATE:
CHANGE LOG:
************************************************** /
WITH ENCRYPTION
AS
DECLARE @.vExchangeRateId dINTEGER;
BEGIN
BEGIN TRANSACTION
UPDATE
Security
SET
Current_Factor_N8 = @.iFactor
WHERE
Security_Id = @.iSecurityId;

INSERT INTO Data_Point_Hist
(As_Of_Date,
Security_Id,
Factor_N8)
VALUES
(dbo.fn_Today(),
@.iSecurityId,
@.iFactor);
COMMIT;
RETURN 0;

END|||oooops - was supposed to be a new post: DELETED

Encrypting Data using TSQL

I need to find a way of encrypting and decrypting data from SQL server 2000. I need to do this as transparently as possible, which is why I need to do this if at all possible before my web application encounters the data.

I know this is possible without 3rd party applications using SQL server 2005, as I have a working implementation already; however I need to do this with SQL server 2000 as upgrading is not an option. Using a 3rd party product to encrypt/decrypt is also not possible.

Any help much appreciated.

Matt Rose

You can write a stored procedure on your own,either as a extended stored procedure or you make a call to a COM object. Both options are not very niuce, compared to buying a thrid party products which was extensivly tested. Furtherone you could do this in your business logic, but as you don′t want to interfer with the curretn architecture, I guess this is not an option for you.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

What is the difference between a 3rd party product and writing something homegrown? (it's not necessarily cheaper to 'do it yourself' everytime)

Here's something that seems to do what you need - http://www.xpcrypt.com/ - though I haven't used it so I can't say if it's good or not.

/Kenneth

|||

Thanks for the suggestions. Unfortunately, I do have to do this myself because I've been told that there is no money available for a third party product.

No worries, I've found a solution to the problem from within the web application I was writing; the encryption and decryption is done by the application using a class library.

Thanks

Matt Rose

Encrypting and Decrypting Data

CREATE TABLE TabEncr (
id int identity (1,1),
NonEncrField varchar(30),
EncrField varchar(30)
)

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'OurSecretPassword'
CREATE CERTIFICATE my_cert with subject = 'Some Certificate'
CREATE SYMMETRIC KEY my_key with algorithm = triple_des encryption by certificate my_cert

OPEN SYMMETRIC KEY my_key DECRYPTION BY CERTIFICATE my_cert
INSERT INTO TabEncr (NonEncrField,EncrField)
VALUES ('Some Plain Value',encryptbykey(key_guid('my_key'),'Some Plain Value'))
CLOSE SYMMETRIC KEY my_key

OPEN SYMMETRIC KEY my_key DECRYPTION BY CERTIFICATE my_cert
SELECT NonEncrField,CONVERT(VARCHAR(30),DecryptByKey(EncrField))
FROM dbo.TabEncr
CLOSE SYMMETRIC KEY my_key

What is the problem with this code. It works fine , inserting the value encrypted but when i try to decrypt ,it returns a null value. What is missing. I also tried with symmetric key encryption with asymmetric key. Result is same, returns NULL value. I am using SQL 2005

Happy Coding...

The EncrField is of a wrong type; it should be varbinary, because the result of encryption is a varbinary value. If you replace the EncrField line with the following, then your script will work as expected:

EncrField varbinary(60)

Thanks
Laurentiu

|||

Hi Laurentiu Cristofor
Thanks for help. It works f?ne. But while trying your solution i also tried my original code and it worked fine. How can it be, i made some simple changes on code to see am i wrong but believe its working. Now there is big question, 1 week before it didn't work. But now its fine. Interesting and confusing.

(Modified; i tried again but it didn't worked. I think i miss somethink but what.)

|||

Maybe you are not recreating the table? The encryption code was correct - the table creation code was incorrect.

Thanks
Laurentiu

|||

how about batch update of data?

Edit:

Follow up on above@.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1365306&SiteID=1&mode=1 with the script

|||

What do you mean by batch update? Or do you mean batch insert?

Thanks
Laurentiu

|||what if i have a data (column) that i want to batch encrypt ?|||

You could do something similar to what you would do if you wanted to update all values of a non-encrypted column.

For example, you can issue an update statement like:

update t set c = encryptbykey(key_guid('skey'), c)

This assumes that c is varbinary and can accommodate the output of the encryption.

Thanks
Laurentiu

|||

Hi,

I got a similar issue with encrypt and decrypt.

In my case,

...

create table ( column Password varbinay(128) )

...

create symmetric key with certificate

...

OPEN SYMMETRIC KEY Sym_Key_01

DECRYPTION BY CERTIFICATE Cert;

UPDATE mytable

SET Password = EncryptByKey(Key_GUID('Password_01'),'ok')

select CONVERT(nvarchar, DecryptByKey(Password)) AS "Decrypted Password" from mytable

here, I didn't get the value 'ok' but a another wierd word (like a chinese word).

does someone know the reason?

Thanks,

Jone

Encrypting and Decrypting Data

CREATE TABLE TabEncr (
id int identity (1,1),
NonEncrField varchar(30),
EncrField varchar(30)
)

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'OurSecretPassword'
CREATE CERTIFICATE my_cert with subject = 'Some Certificate'
CREATE SYMMETRIC KEY my_key with algorithm = triple_des encryption by certificate my_cert

OPEN SYMMETRIC KEY my_key DECRYPTION BY CERTIFICATE my_cert
INSERT INTO TabEncr (NonEncrField,EncrField)
VALUES ('Some Plain Value',encryptbykey(key_guid('my_key'),'Some Plain Value'))
CLOSE SYMMETRIC KEY my_key

OPEN SYMMETRIC KEY my_key DECRYPTION BY CERTIFICATE my_cert
SELECT NonEncrField,CONVERT(VARCHAR(30),DecryptByKey(EncrField))
FROM dbo.TabEncr
CLOSE SYMMETRIC KEY my_key

What is the problem with this code. It works fine , inserting the value encrypted but when i try to decrypt ,it returns a null value. What is missing. I also tried with symmetric key encryption with asymmetric key. Result is same, returns NULL value. I am using SQL 2005

Happy Coding...

The EncrField is of a wrong type; it should be varbinary, because the result of encryption is a varbinary value. If you replace the EncrField line with the following, then your script will work as expected:

EncrField varbinary(60)

Thanks
Laurentiu

|||

Hi Laurentiu Cristofor
Thanks for help. It works f?ne. But while trying your solution i also tried my original code and it worked fine. How can it be, i made some simple changes on code to see am i wrong but believe its working. Now there is big question, 1 week before it didn't work. But now its fine. Interesting and confusing.

(Modified; i tried again but it didn't worked. I think i miss somethink but what.)

|||

Maybe you are not recreating the table? The encryption code was correct - the table creation code was incorrect.

Thanks
Laurentiu

|||

how about batch update of data?

Edit:

Follow up on above@.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1365306&SiteID=1&mode=1 with the script

|||

What do you mean by batch update? Or do you mean batch insert?

Thanks
Laurentiu

|||what if i have a data (column) that i want to batch encrypt ?|||

You could do something similar to what you would do if you wanted to update all values of a non-encrypted column.

For example, you can issue an update statement like:

update t set c = encryptbykey(key_guid('skey'), c)

This assumes that c is varbinary and can accommodate the output of the encryption.

Thanks
Laurentiu

|||

Hi,

I got a similar issue with encrypt and decrypt.

In my case,

...

create table ( column Password varbinay(128) )

...

create symmetric key with certificate

...

OPEN SYMMETRIC KEY Sym_Key_01

DECRYPTION BY CERTIFICATE Cert;

UPDATE mytable

SET Password = EncryptByKey(Key_GUID('Password_01'),'ok')

select CONVERT(nvarchar, DecryptByKey(Password)) AS "Decrypted Password" from mytable

here, I didn't get the value 'ok' but a another wierd word (like a chinese word).

does someone know the reason?

Thanks,

Jone

Encrypting and Decrypting Data

CREATE TABLE TabEncr (
id int identity (1,1),
NonEncrField varchar(30),
EncrField varchar(30)
)

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'OurSecretPassword'
CREATE CERTIFICATE my_cert with subject = 'Some Certificate'
CREATE SYMMETRIC KEY my_key with algorithm = triple_des encryption by certificate my_cert

OPEN SYMMETRIC KEY my_key DECRYPTION BY CERTIFICATE my_cert
INSERT INTO TabEncr (NonEncrField,EncrField)
VALUES ('Some Plain Value',encryptbykey(key_guid('my_key'),'Some Plain Value'))
CLOSE SYMMETRIC KEY my_key

OPEN SYMMETRIC KEY my_key DECRYPTION BY CERTIFICATE my_cert
SELECT NonEncrField,CONVERT(VARCHAR(30),DecryptByKey(EncrField))
FROM dbo.TabEncr
CLOSE SYMMETRIC KEY my_key

What is the problem with this code. It works fine , inserting the value encrypted but when i try to decrypt ,it returns a null value. What is missing. I also tried with symmetric key encryption with asymmetric key. Result is same, returns NULL value. I am using SQL 2005

Happy Coding...

The EncrField is of a wrong type; it should be varbinary, because the result of encryption is a varbinary value. If you replace the EncrField line with the following, then your script will work as expected:

EncrField varbinary(60)

Thanks
Laurentiu

|||

Hi Laurentiu Cristofor
Thanks for help. It works f?ne. But while trying your solution i also tried my original code and it worked fine. How can it be, i made some simple changes on code to see am i wrong but believe its working. Now there is big question, 1 week before it didn't work. But now its fine. Interesting and confusing.

(Modified; i tried again but it didn't worked. I think i miss somethink but what.)

|||

Maybe you are not recreating the table? The encryption code was correct - the table creation code was incorrect.

Thanks
Laurentiu

|||

how about batch update of data?

Edit:

Follow up on above@.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1365306&SiteID=1&mode=1 with the script

|||

What do you mean by batch update? Or do you mean batch insert?

Thanks
Laurentiu

|||what if i have a data (column) that i want to batch encrypt ?|||

You could do something similar to what you would do if you wanted to update all values of a non-encrypted column.

For example, you can issue an update statement like:

update t set c = encryptbykey(key_guid('skey'), c)

This assumes that c is varbinary and can accommodate the output of the encryption.

Thanks
Laurentiu

|||

Hi,

I got a similar issue with encrypt and decrypt.

In my case,

...

create table ( column Password varbinay(128) )

...

create symmetric key with certificate

...

OPEN SYMMETRIC KEY Sym_Key_01

DECRYPTION BY CERTIFICATE Cert;

UPDATE mytable

SET Password = EncryptByKey(Key_GUID('Password_01'),'ok')

select CONVERT(nvarchar, DecryptByKey(Password)) AS "Decrypted Password" from mytable

here, I didn't get the value 'ok' but a another wierd word (like a chinese word).

does someone know the reason?

Thanks,

Jone

encryping - decrypting stored procedures

Hi all...

Im wondering how do you encrypt and decrypt a stored procedure within sql server 2000 ?

i want to be able to encrypt data been inserted / updated using triple des algorithm and then on select / read - decrypt it.

Does anyone know a solution / stored procedure on how to do this? any ideas.

Thanks

Paul..

Im also wondering if there is a away of doing this on the code side without using stored procedures (asp.net c#) i have been able to encrypt and decrpt to a string but unable to do it through the datagrid object (dataset) or a datareader..

Any tips / help would be helpful

Thanks..You may implement a sql server function to encrypt and decrypt when insert or select.

Just my 2 cent.

Scott

Software Design Group|||The following article could possibly provide some thoughts;

http://www.15seconds.com/issue/030310.htm