x

How to Decrypt the Stored Procedure in Sql Server 2008?

Hi to all

One of my collegue was created that sp's now i want to manage all those things, i dont know how to decrypt the procedure's, we lost the copy of that procedure.

I am using the following code to decrypt my procedure, but it did'nt work to get the procedure it always give the null value.

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 , @ct nvarchar(max)

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

SET @OrigSpText3=(SELECT top 1 ctext FROM syscomments  WHERE id = object_id(@objectName) order by colid)
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)))))
print @resultsp
 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
set @ct = (SELECT ctext FROM syscomments WHERE id = object_id(@objectName))
print @ct
execute( @resultsp)
GO
Thanks to all
more ▼

asked Sep 20, 2010 at 07:12 AM in Default

varun 1 gravatar image

varun 1
22 8 8 9

(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first
more ▼

answered Sep 20, 2010 at 07:31 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.5k 61 65 87

(comments are locked)
10|1200 characters needed characters left
Encryption should never be for access control--so I agree with Matt, but for a slightly different reason. Encrypt data not stored procs. Creating a CLR proc might give better obfuscation but those can be reverse-engineered too if you are determined enough.
more ▼

answered Oct 13, 2010 at 04:25 PM

Scot Hauder gravatar image

Scot Hauder
6.1k 13 15 18

You don't have to be very determined if you use the [SQL Browser plugin for reflector][1]...

[1]: http://www.denisbauer.com/NETTools/SQL2005Browser.aspx
Oct 14, 2010 at 01:20 AM Matt Whitfield ♦♦
I was thinking more of viewing the actual source code using an MSIL disassembler after someone had used dotnet obfuscator on it
Oct 14, 2010 at 04:25 AM Scot Hauder
(comments are locked)
10|1200 characters needed characters left
AFAIK, the encrypted stored procedure cannot be decrypted. If it can be what is the use of encrypting it?
more ▼

answered Sep 20, 2010 at 09:48 AM

Adiga gravatar image

Adiga
221 1

Encrypted stored procedures are easily decrypted. The code in C# is about 25 lines long. There is little point in encrypting them, to be honest. It's like putting up a sign on a gate that says 'keep out', even though the gate is not locked.
Sep 21, 2010 at 02:00 AM Matt Whitfield ♦♦

@Matt, I must respectfully beg to differ. Yes, they are easily decrypted, but that is not the same thing as being totally unprotected. It means that someone has to take deliberate action with effort to decrypt them. This means

  1. The casually curious will be kept out.

  2. If someone does get in and you learn about it then you can say with certainty they were knowingly exceeding their authorizations rather than being casually curious or trying to understand their system. This can matter a great deal in companies with strict policies about who can be terminated.

  3. I am not a lawyer, but I believe that at least in the US there are legal ramifications under the DMCA and other laws when dealing with reverse engineering/copyright situations, especially if those procedures are part of a licensed integrated software solution.

Security does not have to be unbreakable to be useful. My bicycle lock is easily destroyed by a good pair of bolt cutters or a few minutes with a metal file, but it works well enough for its purposes.
Oct 12, 2010 at 01:58 PM TimothyAWiseman
@TimothyAWiseman - I know exactly where you're coming from, but what I am saying is that when most people think of encryption, they think of something that is not easily broken. And that's not what you get with encrypted procedures... I would also say that the casually curious person who is skilled enough to want to look at a procedure definition would more than likely find a way... That's why I say there's little point in encrypting them.
Oct 13, 2010 at 07:15 AM Matt Whitfield ♦♦

@Matt, I also see where you coming from and you make good points. To someone not familiar with theoretical encryption and the specifics of how SQL Servers encrypts its procedures the word encryption would imply a lot more security then you actually get. And that can be very dangerous.

Still, as long as you actually understand what you are getting there are times and places it is useful.

For instance, if you are say distributing a copyrighted solutions that uses stored procedures you might care much more about the DMCA implications of the encryption then you do about it actually working.

Also, I once had to (temporarily !!! until I got the better solution in place) hardcode a password into a stored procedure. I used "with encryption" knowing full well how it could be circumvented. This was enough that I knew my coworkers would not just stumble over the password. I knew most of them did not know it could easily be bypassed and the rest would respect that they should not be poking around there.

SQL Servers procedure encryption is just enough security say "You don't belong here", but sometimes that is all you need.
Oct 13, 2010 at 09:26 AM TimothyAWiseman
@TimothyAWiseman - wise words as ever sir. I think it does definitely come down to the fact that it's a good mechanism as a deterrent - which I shouldn't overlook. Perhaps the sign that says 'keep out' on the garden gate is enough sometimes :)
Oct 13, 2010 at 11:48 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

For resolution of this issue, we have formulated Free SQL Decryptor tool that Decrypt SQL Server Stored Procedure 2008 and is powerful solution amongst all the outside solutions availed in online marketplace. It helps any of the users to get the encrypted data converted to decrypted database in the SQL Server database and now you can simply decrypt stored procedure in SQL Server 2008.

more ▼

answered Apr 05, 2013 at 04:15 AM

avirasimon gravatar image

avirasimon
1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1850

asked: Sep 20, 2010 at 07:12 AM

Seen: 5713 times

Last Updated: Apr 05, 2013 at 04:15 AM