Encrypting Stored Procedures AND the script file

[PCStorm]

Sure, I know we can encrypt by saying CREATE PROCEDURE with ENCRYPTION. But what about the file we load from at a customer site. What good is the encrypted procedure if the file we use to create the procedure is not encrypted.

Is there an easy way to do this? I tried encrypting and then scripting the procedure figuring it would create encrypted code, but it does not allow you to script the procedure once encrypted.

Someone out there must have done this or knows where my senior citizen brain is going wrong.

Thanks.

 

[SQLSister]

Encryption is a bad idea unless you absolutely need to use it. First you can't script an encrypted stored procedure.

You also can't use encrypted objects in replication.

You also can't view any information about the stored procedure to help you determine if the customer is running the correct version.

If you do decide to use encryption, make sure the objects are not encrypted in the development database, you might want to change them at some point without completely rewriting them. Only encrypt the stored procedures sitting on the customers' machines.

 

[osjohnm]

Hi PCStorm

As SQLSister says ENCRYTPTION is a dangerous thing and you should implement it only when you really have to.

The other problem is that once the proc is encrypted you can't edit it or even view the proc's code. Therefore you need to "decrypt" the procedure first. The following sql code will create a stored proc that will "decrypt" encrypted stored procedures. All you need to do is pass the stored proc's name to it.

eg. exec sp_decrypt_sp 'usp_get_permissions'

Here is the procedure's code:

create PROCEDURE sp_decrypt_sp (@objectName varchar(50))
AS
DECLARE @OrigSpText1 nvarchar(4000), @OrigSpText2 nvarchar(4000) , @OrigSpText3 nvarchar(4000), @resultsp nvarchar(4000)
declare @i int , @t bigint

--get encrypted data
SET @OrigSpText1=(SELECT ctext FROM syscomments WHERE id = object_id(@objectName))
SET @OrigSpText2='ALTER PROCEDURE '+ @objectName +' WITH ENCRYPTION AS '+REPLICATE('-', 3938)
EXECUTE (@OrigSpText2)

SET @OrigSpText3=(SELECT ctext FROM syscomments WHERE id = object_id(@objectName))
SET @OrigSpText2='CREATE PROCEDURE '+ @objectName +' WITH ENCRYPTION AS '+REPLICATE('-', 4000-62)

--start counter
SET @i=1
--fill temporary variable
SET @resultsp = replicate(N'A', (datalength(@OrigSpText1) / 2))

--loop
WHILE @i<=datalength(@OrigSpText1)/2
BEGIN
--reverse encryption (XOR original+bogus+bogus encrypted)
SET @resultsp = stuff(@resultsp, @i, 1, NCHAR(UNICODE(substring(@OrigSpText1, @i, 1)) ^
(UNICODE(substring(@OrigSpText2, @i, 1)) ^
UNICODE(substring(@OrigSpText3, @i, 1)))))
SET @i=@i+1
END
--drop original SP
EXECUTE ('drop PROCEDURE '+ @objectName)
--remove encryption
--preserve case
SET @resultsp=REPLACE((@resultsp),'WITH ENCRYPTION', '')
SET @resultsp=REPLACE((@resultsp),'With Encryption', '')
SET @resultsp=REPLACE((@resultsp),'with encryption', '')
IF CHARINDEX('WITH ENCRYPTION',UPPER(@resultsp) )>0
SET @resultsp=REPLACE(UPPER(@resultsp),'WITH ENCRYPTION', '')
--replace Stored procedure without enryption
execute( @resultsp)
GO

Since you want to use encryption then that should help you edit the procs but make sure that you don't encrypt this stored procedure! or if you do keep a &quot;hard copy&quot; of the sql somewhere safe. Also it might be a good idea to keep &quot;hard copies&quot; of all your encrypted stored procs at all your client sites.

There isn't away to encrypt the sql script that creates your procs that I know of but perhaps there is way and someone else can point you in the right direction.

Hope this helps

John