Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server
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
Posted 20-Sep-10 3:47am
Comments
janaka4595 at 5-Jul-13 7:51am
   
nice its work thanks
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Just download Optillect's SQL Decryptor freeware
A modern tool that decrypts T-SQL procedures, functions, triggers, and views on any SQL Server Cool | :cool:
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 6

you can execute it, but can't view the SQL Statements used to construct it
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 8

Hello,
 
Try this link, it gives a step by step description.
 
http://sqljunkieshare.com/2012/03/07/decrypting-encrypted-stored-procedures-views-functions-in-sql-server-20052008-r2/
 
Regards
Himanshu
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 9

Hi
 
Unless you know the decryption key it is not possible in general but you do can take advantage of certain software which are available in the market which are capable to perform complex decryption process and help you get back your encrypted database back to normal. I suggest you to take help of this software to decrypt your database. http://www.sqlmdfviewer.org/sql-decryptor.html
 
Regards
Henry Desouza
  Permalink  
Comments
CHill60 at 17-Jan-14 11:59am
   
Answering 3 year old questions with links to software published for sale by a company you work for is sort of frowned upon
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Probably a naive question but have you tried accessing it via SSMS?
  Permalink  
Comments
Simon_Whale at 20-Sep-10 12:19pm
   
you can execute it, but can't view the SQL Statements used to construct it
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

as its not stored in "plain text" on the server you will need to look at 3rd party tools.
 
Google suggestions
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

try this
 
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
 
after that
bass your object to this proc
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

Optillect's SQL Decryptor freeware has worked for me as well. It gives you a couple of options. You can see the code of the sp if you just wanted to see it or you can have the sp decrypted on the server. The app just needs to connect to the sql server.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 CHill60 320
1 Sarvesh Kumar Gupta 238
2 Sergey Alexandrovich Kryukov 173
3 OriginalGriff 163
4 DamithSL 153


Advertise | Privacy | Mobile
Web02 | 2.8.140709.1 | Last Updated 2 May 2014
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid