How do i decrypt encrypted stored procedure?

Question

Post reply

How do i decrypt encrypted stored procedure?

Hejime

SSC Veteran

Points: 245
More actions
January 25, 2010 at 10:43 pm

#218525

hi there,
i'm having a problem on how to decrypt an encrypted stored procedure on SQL Server 2008
i found this but it doesn't seem to help.. infact, it deletes my stored procedure instead
Create Procedure Decryptsp2K (@objName varchar(50))
AS
-- INPUT: object name (stored procedure, view or trigger)
DECLARE @a nvarchar(4000), @b-2 nvarchar(4000), @C nvarchar(4000), @d nvarchar(4000), @i int, @t bigint
--get encrypted data
SET @a=(SELECT ctext FROM syscomments WHERE id = object_id(@objName))
SET @b-2='ALTER PROCEDURE '+ @objName +' WITH ENCRYPTION AS '+REPLICATE('-', 4000-62)
EXECUTE (@b)
--get encrypted bogus SP
SET @C=(SELECT ctext FROM syscomments WHERE id = object_id(@objName))
SET @b-2='CREATE PROCEDURE '+ @objName +' WITH ENCRYPTION AS '+REPLICATE('-', 4000-62)
--start counter
SET @i=1
--fill temporary variable
SET @d = replicate(N'A', (datalength(@a) / 2))
--loop
WHILE @i<=datalength(@a)/2
BEGIN
--xor original+bogus+bogus encrypted
SET @d = stuff(@d, @i, 1,
NCHAR(UNICODE(substring(@a, @i, 1)) ^
(UNICODE(substring(@b, @i, 1)) ^
UNICODE(substring(@c, @i, 1)))))
SET @i=@i+1
END
--drop original SP
EXECUTE ('drop PROCEDURE '+ @objName)
remove encryption
--try to preserve case
SET @d=REPLACE((@d),'WITH ENCRYPTION', '')
SET @d=REPLACE((@d),'With Encryption', '')
SET @d=REPLACE((@d),'with encryption', '')
IF CHARINDEX('WITH ENCRYPTION',UPPER(@d) )>0
SET @d=REPLACE(UPPER(@d),'WITH ENCRYPTION', '')
--replace SP
execute( @d)
GO

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Here's an article that seems like it would help:

http://cvakumar.com/msdynamics/?p=100

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jerry Hung SSChampion Points: 12968 More actions · Answer 2

The timing is great

See this Aaron's blog post about it

http://sqlblog.com/blogs/aaron_bertrand/archive/2010/01/24/decrypting-a-question-of-morals-ethics-or-both.aspx

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

That is a good blog entry on the topic. I would add that there are other scenarios where an SP may need to be decrypted. I ran into one such situation once upon a time. An in-house developed database and procs in production. A proc was failing and we had no idea why. Proc was not in source control - and was encrypted. The scenario is rare, but does happen. In order to fix that proc, we had to decrypt the proc.

Besides the ethics part of Aarons post, there is the google part. There are many resources on the net that show how to decrypt a proc.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Hejime SSC Veteran Points: 245 More actions · Answer 4

CirquedeSQLeil (1/26/2010)
Here's an article that seems like it would help:
http://cvakumar.com/msdynamics/?p=100

i seem to get lots of errors pointing to

DATALENGTH(@ContentOfFakeObject)/2 < and all others similar

An expression of non-boolean type specified in a context where a condition is expected, near ';'.

Jerry Hung (1/26/2010)
The timing is great
See this Aaron's blog post about it
http://sqlblog.com/blogs/aaron_bertrand/archive/2010/01/24/decrypting-a-question-of-morals-ethics-or-both.aspx

i know about ethics but the problem is,

i was just assigned to this project which have been on going for quite a long time

with no help from my workmates or much from the net,

i can't decrypt the encrypted stored procedures to even modify the existing sql

maybe i should start "encrypting" my resignation letter instead 🙂

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Here is a commercial product that may help:

http://www.devlib.net/decryptsql.htm

and another site that offers some information

http://www.sql-shield.com/decrypt-stored-procedure.html

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Fraggle-805517 SSCrazy Eights Points: 8907 More actions · Answer 6

It can be done easily through SMO. We actually have an in house process that will go through and encrypt or decrypt and entire database of procs,triggers, and functions. We use this for specific client databases who want our databases on their servers instead of ours.

In watching the .NET programmer, he did it in less than 50 lines of code.

Fraggle

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

Fraggle-805517 (1/26/2010)
It can be done easily through SMO. We actually have an in house process that will go through and encrypt or decrypt and entire database of procs,triggers, and functions. We use this for specific client databases who want our databases on their servers instead of ours.
In watching the .NET programmer, he did it in less than 50 lines of code.
Fraggle

Interesting option. Do you have a reference we might be able to lookup?

Thanks

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Fraggle-805517 SSCrazy Eights Points: 8907 More actions · Answer 8

Will have to get back with you on that once I talk with the programmer as I personally know more about women than I do SMO.

Fraggle

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

Fraggle-805517 (1/26/2010)
Will have to get back with you on that once I talk with the programmer as I personally know more about women than I do SMO.
Fraggle

Thanks

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Hejime SSC Veteran Points: 245 More actions · Answer 10

CirquedeSQLeil (1/26/2010)
Here is a commercial product that may help:
http://www.devlib.net/decryptsql.htm
and another site that offers some information
http://www.sql-shield.com/decrypt-stored-procedure.html

this is weird.. the 3rd party applications requires sql browser to run

but i can't seem to get it to run

if i run it, it'll say, sql browser service ran and stopped by it's own

i wonder is it because i upgraded from sql server 2005 to sql server 2008

Fraggle-805517 (1/26/2010)
Will have to get back with you on that once I talk with the programmer as I personally know more about women than I do SMO.
Fraggle

thanks.. looking forward to see the code

Fraggle-805517 SSCrazy Eights Points: 8907 More actions · Answer 11

Well can't give you the code exactly, but here is what my .NET programmer gave me. Said anyone who knows something about SMO should be able to adjust correctly to do what you need it to do.

###############################################################################

## Encrypt-DB.ps1

##

## Example:

##

## PS > .\Encrypt-DB.ps1 "dbtest" "databasename" "login" "password"

##############################################################################

param( $serverName,

$dbname,

$login,

$password)

# Load-SMO assemblies

[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlEnum")

[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")

[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")

[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")

$server = New-Object -typeName Microsoft.SqlServer.Management.Smo.Server $serverName

if($login -ne "")

{

$server.ConnectionContext.LoginSecure = $false

$server.ConnectionContext.Login = $login

$server.ConnectionContext.Password = $password

}

$database = $server.Databases[$dbname]

$storedProcs =$database.StoredProcedures

Write-Host "Encrypting stored procedures for database:" $dbname

Write-Host "Starting to encrypt " $storedProcs.Count " stored procedures..."

foreach($sp in $storedProcs)

{

# Encrypt each stored proc.

#$sp.TextMode = $false

#$sp.IsEncrypted = $true

#$sp.TextMode = $true

#$sp.Alter()

}

Write-Host "Completed encrypting stored procedures"

Again this is all Greek to me, so please don't ask me to explain it as I will probably put something into the google translater, translate it to ancient Greek or something, and respond back with that.

😀

Fraggle

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

the code is nice. I was just looking for the Internet Reference if you had it handy.

Thanks

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events