Decrypting encrypted stored procedures, views, functions in SQL server 2005,2008 &R2

Any stored procedure or view or function can be encrypted when stored in the sql server , so a user can not see the code inside even if the user has SYSADMIN role and it is also not recommended to use this encryption option to hide the code because once you use the encryption option Microsoft sql server does not provide the option or the feature to reverse the setting or decrypt the object , so when you use the encryption option , save your code in a separate instance































TITLE: Microsoft SQL Server Management Studio
——————————

Script failed for StoredProcedure ‘dbo.test_encrp’. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1750.9+((dac_inplace_upgrade).101209-1051+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Script+StoredProcedure&LinkId=20476

——————————
ADDITIONAL INFORMATION:

Property TextHeader is not available for StoredProcedure ‘[dbo].[test_encrp]’. This property may not exist for this object, or may not be retrievable due to insufficient access rights. The text is encrypted. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1750.9+((dac_inplace_upgrade).101209-1051+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=TextHeader&LinkId=20476

First we will see how it looks in the sql_modules catalog views, in the process we will also list all the encrypted

stored procedures in the databases

to find all the encrypted objects in the sql server database


here is the query used above

select o.name,s.definition,o.type_desc,

case

when definition is null then ‘yes’
else ‘no’
end as ‘is_encrypted’

from sys.sql_modules s inner join

sys.objects o on s.object_id = o.object_id

where type in (‘p’,’tr’,’FN’,’tf’,’v’)

now we got know how it is encrypted , and where to find the encrypted objects now we will look in to how to decrypt them

There are two ways you can do

  1. Using a script/stored procedure using a DAC connection
  2. Using third party tools

first we will look at the script method

first you need to have SYSADMIN role assigned to you , then you have to setup a DAC connection to the sql server instance that you

want to decrypt the objects

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

here is the code to decrypt

 

/****** Object:  StoredProcedure [dbo].[sp__procedure]    Script Date: 03/06/2012 19:56:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp__procedure]
(@procedure sysname = NULL)
AS
SET NOCOUNT ON

DECLARE @intProcSpace bigint, @t bigint, @maxColID smallint,@intEncrypted
tinyint,@procNameLength int
select @maxColID = max(subobjid)
–,@intEncrypted = encrypted
FROM
sys.sysobjvalues WHERE objid = object_id(@procedure)
–GROUP BY encrypted

–select @maxColID as ‘Rows in sys.sysobjvalues’
select @procNameLength = datalength(@procedure) + 29

DECLARE @real_01 nvarchar(max)

DECLARE @fake_01 nvarchar(max)

DECLARE @fake_encrypt_01 nvarchar(max)

DECLARE @real_decrypt_01 nvarchar(max),@real_decrypt_01a nvarchar(max)

select @real_decrypt_01a = ”

— extract the encrypted imageval rows from sys.sysobjvalues
SET @real_01=(SELECT imageval FROM sys.sysobjvalues WHERE objid =
object_id(@procedure) and valclass = 1 and subobjid = 1 )

— create this table for later use
create table #output ( [ident] [int] IDENTITY (1, 1) NOT NULL ,
[real_decrypt] NVARCHAR(MAX) )

— We’ll begin the transaction and roll it back later
BEGIN TRAN
— alter the original procedure, replacing with dashes
SET @fake_01=’ALTER PROCEDURE ‘+ @procedure +’ WITH ENCRYPTION AS
‘+REPLICATE(‘-‘, 40003 – @procNameLength)

EXECUTE (@fake_01)

— extract the encrypted fake imageval rows from sys.sysobjvalues
SET @fake_encrypt_01=(SELECT imageval FROM sys.sysobjvalues WHERE objid =
object_id(@procedure) and valclass = 1 and subobjid = 1)

SET @fake_01=’CREATE PROCEDURE ‘+ @procedure +’ WITH ENCRYPTION AS
‘+REPLICATE(‘-‘, 40003 – @procNameLength)
–start counter
SET @intProcSpace=1
–fill temporary variable with with a filler character
SET @real_decrypt_01 = replicate(N’A’, (datalength(@real_01) /2 ))

–loop through each of the variables sets of variables, building the real variable
–one byte at a time.
SET @intProcSpace=1

— Go through each @real_xx variable and decrypt it, as necessary
WHILE @intProcSpace<=(datalength(@real_01)/2)
BEGIN
–xor real & fake & fake encrypted
SET @real_decrypt_01 = stuff(@real_decrypt_01, @intProcSpace, 1,
NCHAR(UNICODE(substring(@real_01, @intProcSpace, 1)) ^
(UNICODE(substring(@fake_01, @intProcSpace, 1)) ^
UNICODE(substring(@fake_encrypt_01, @intProcSpace, 1)))))
SET @intProcSpace=@intProcSpace+1
END

— Load the variables into #output for handling by sp_helptext logic

insert #output (real_decrypt) select @real_decrypt_01
— select real_decrypt AS ‘#output chek’ from #output — Testing

— ————————————-
— Beginning of extract from sp_helptext
— ————————————-
declare @dbname sysname
,@BlankSpaceAdded int
,@BasePos int
,@CurrentPos int
,@TextLength int
,@LineId int
,@AddOnLen int
,@LFCR int –lengths of line feed carriage return
,@DefinedLength int
,@SyscomText nvarchar(4000)
,@Line nvarchar(255)

Select @DefinedLength = 255
SELECT @BlankSpaceAdded = 0
–Keeps track of blank spaces at end of lines. Note Len function ignores trailing blank spaces
CREATE TABLE #CommentText
(LineId int
,Text nvarchar(255) collate database_default)

— use #output instead of sys.sysobjvalues
DECLARE ms_crs_syscom CURSOR LOCAL
FOR SELECT real_decrypt from #output
ORDER BY ident
FOR READ ONLY

— Else get the text.

SELECT @LFCR = 2
SELECT @LineId = 1

OPEN ms_crs_syscom

FETCH NEXT FROM ms_crs_syscom into @SyscomText

WHILE @@fetch_status >= 0
BEGIN

SELECT @BasePos = 1
SELECT @CurrentPos = 1
SELECT @TextLength = LEN(@SyscomText)

WHILE @CurrentPos != 0
BEGIN
–Looking for end of line followed by carriage return
SELECT @CurrentPos = CHARINDEX(char(13)+char(10), @SyscomText,
@BasePos)

–If carriage return found
IF @CurrentPos != 0
BEGIN
–If new value for @Lines length will be > then the
–set length then insert current contents of @line
–and proceed.

While (isnull(LEN(@Line),0) + @BlankSpaceAdded +
@CurrentPos-@BasePos + @LFCR) > @DefinedLength
BEGIN
SELECT @AddOnLen = @DefinedLength-(isnull(LEN(@Line),0) +
@BlankSpaceAdded)
INSERT #CommentText VALUES
( @LineId,
isnull(@Line, N”) + isnull(SUBSTRING(@SyscomText,
@BasePos, @AddOnLen), N”))
SELECT @Line = NULL, @LineId = @LineId + 1,
@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
END
SELECT @Line = isnull(@Line, N”) +
isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos-@BasePos + @LFCR), N”)
SELECT @BasePos = @CurrentPos+2
INSERT #CommentText VALUES( @LineId, @Line )
SELECT @LineId = @LineId + 1
SELECT @Line = NULL
END
ELSE
–else carriage return not found
BEGIN
IF @BasePos <= @TextLength
BEGIN
–If new value for @Lines length will be > then the
–defined length

While (isnull(LEN(@Line),0) + @BlankSpaceAdded +
@TextLength-@BasePos+1 ) > @DefinedLength
BEGIN
SELECT @AddOnLen = @DefinedLength –
(isnull(LEN(@Line),0) + @BlankSpaceAdded)
INSERT #CommentText VALUES
( @LineId,
isnull(@Line, N”) + isnull(SUBSTRING(@SyscomText,
@BasePos, @AddOnLen), N”))
SELECT @Line = NULL, @LineId = @LineId + 1,
@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded =
0
END
SELECT @Line = isnull(@Line, N”) +
isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength-@BasePos+1 ), N”)
if LEN(@Line) < @DefinedLength and charindex(‘ ‘,
@SyscomText, @TextLength+1 ) > 0
BEGIN
SELECT @Line = @Line + ‘ ‘, @BlankSpaceAdded = 1
END
END
END
END

FETCH NEXT FROM ms_crs_syscom into @SyscomText
END

IF @Line is NOT NULL
INSERT #CommentText VALUES( @LineId, @Line )

select Text from #CommentText order by LineId

CLOSE ms_crs_syscom
DEALLOCATE ms_crs_syscom

DROP TABLE #CommentText

— ————————————-
— End of extract from sp_helptext
— ————————————-

— Drop the procedure that was setup with dashes and rebuild it with the good stuff
— Version 1.1 mod; makes rebuilding hte proc unnecessary
ROLLBACK TRAN

DROP TABLE #output
GO

the  second way is to use the third party tools , there many third party tools that are available to download

one of the best is the Optillect sql decrypter which is a free ware, good thing about it is it does not need SYSADMIN role as it does not

use DAC , rather it uses DBCC ind, and DBCC PAGE for the required information for the SYS.OBJVALUES table

http://download.cnet.com/Optillect-SQL-Decryptor/3640-10254_4-75300271-1.html

 

download and install it

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Advertisements

20 thoughts on “Decrypting encrypted stored procedures, views, functions in SQL server 2005,2008 &R2

    • akhil393 says:

      /****** Object: StoredProcedure [dbo].[sp__procedure] Script Date: 03/06/2012 19:56:01 ******/
      SET ANSI_NULLS ON
      GO

      SET QUOTED_IDENTIFIER ON
      GO

      CREATE PROCEDURE [dbo].[sp__procedure]
      (@procedure sysname = NULL)
      AS
      SET NOCOUNT ON

      DECLARE @intProcSpace bigint, @t bigint, @maxColID smallint,@intEncrypted
      tinyint,@procNameLength int
      select @maxColID = max(subobjid)
      –,@intEncrypted = encrypted
      FROM
      sys.sysobjvalues WHERE objid = object_id(@procedure)
      –GROUP BY encrypted

      –select @maxColID as ‘Rows in sys.sysobjvalues’
      select @procNameLength = datalength(@procedure) + 29

      DECLARE @real_01 nvarchar(max)

      DECLARE @fake_01 nvarchar(max)

      DECLARE @fake_encrypt_01 nvarchar(max)

      DECLARE @real_decrypt_01 nvarchar(max),@real_decrypt_01a nvarchar(max)

      select @real_decrypt_01a = ”

      – extract the encrypted imageval rows from sys.sysobjvalues
      SET @real_01=(SELECT imageval FROM sys.sysobjvalues WHERE objid =
      object_id(@procedure) and valclass = 1 and subobjid = 1 )

      – create this table for later use
      create table #output ( [ident] [int] IDENTITY (1, 1) NOT NULL ,
      [real_decrypt] NVARCHAR(MAX) )

      – We’ll begin the transaction and roll it back later
      BEGIN TRAN
      – alter the original procedure, replacing with dashes
      SET @fake_01=’ALTER PROCEDURE ‘+ @procedure +’ WITH ENCRYPTION AS
      ‘+REPLICATE(‘-’, 40003 – @procNameLength)

      EXECUTE (@fake_01)

      – extract the encrypted fake imageval rows from sys.sysobjvalues
      SET @fake_encrypt_01=(SELECT imageval FROM sys.sysobjvalues WHERE objid =
      object_id(@procedure) and valclass = 1 and subobjid = 1)

      SET @fake_01=’CREATE PROCEDURE ‘+ @procedure +’ WITH ENCRYPTION AS
      ‘+REPLICATE(‘-’, 40003 – @procNameLength)
      –start counter
      SET @intProcSpace=1
      –fill temporary variable with with a filler character
      SET @real_decrypt_01 = replicate(N’A’, (datalength(@real_01) /2 ))

      –loop through each of the variables sets of variables, building the real variable
      –one byte at a time.
      SET @intProcSpace=1

      – Go through each @real_xx variable and decrypt it, as necessary
      WHILE @intProcSpace= 0
      BEGIN

      SELECT @BasePos = 1
      SELECT @CurrentPos = 1
      SELECT @TextLength = LEN(@SyscomText)

      WHILE @CurrentPos != 0
      BEGIN
      –Looking for end of line followed by carriage return
      SELECT @CurrentPos = CHARINDEX(char(13)+char(10), @SyscomText,
      @BasePos)

      –If carriage return found
      IF @CurrentPos != 0
      BEGIN
      –If new value for @Lines length will be > then the
      –set length then insert current contents of @line
      –and proceed.

      While (isnull(LEN(@Line),0) + @BlankSpaceAdded +
      @CurrentPos-@BasePos + @LFCR) > @DefinedLength
      BEGIN
      SELECT @AddOnLen = @DefinedLength-(isnull(LEN(@Line),0) +
      @BlankSpaceAdded)
      INSERT #CommentText VALUES
      ( @LineId,
      isnull(@Line, N”) + isnull(SUBSTRING(@SyscomText,
      @BasePos, @AddOnLen), N”))
      SELECT @Line = NULL, @LineId = @LineId + 1,
      @BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
      END
      SELECT @Line = isnull(@Line, N”) +
      isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos-@BasePos + @LFCR), N”)
      SELECT @BasePos = @CurrentPos+2
      INSERT #CommentText VALUES( @LineId, @Line )
      SELECT @LineId = @LineId + 1
      SELECT @Line = NULL
      END
      ELSE
      –else carriage return not found
      BEGIN
      IF @BasePos then the
      –defined length

      While (isnull(LEN(@Line),0) + @BlankSpaceAdded +
      @TextLength-@BasePos+1 ) > @DefinedLength
      BEGIN
      SELECT @AddOnLen = @DefinedLength –
      (isnull(LEN(@Line),0) + @BlankSpaceAdded)
      INSERT #CommentText VALUES
      ( @LineId,
      isnull(@Line, N”) + isnull(SUBSTRING(@SyscomText,
      @BasePos, @AddOnLen), N”))
      SELECT @Line = NULL, @LineId = @LineId + 1,
      @BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded =
      0
      END
      SELECT @Line = isnull(@Line, N”) +
      isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength-@BasePos+1 ), N”)
      if LEN(@Line) 0
      BEGIN
      SELECT @Line = @Line + ‘ ‘, @BlankSpaceAdded = 1
      END
      END
      END
      END

      FETCH NEXT FROM ms_crs_syscom into @SyscomText
      END

      IF @Line is NOT NULL
      INSERT #CommentText VALUES( @LineId, @Line )

      select Text from #CommentText order by LineId

      CLOSE ms_crs_syscom
      DEALLOCATE ms_crs_syscom

      DROP TABLE #CommentText

      – ————————————-
      – End of extract from sp_helptext
      – ————————————-

      – Drop the procedure that was setup with dashes and rebuild it with the good stuff
      – Version 1.1 mod; makes rebuilding hte proc unnecessary
      ROLLBACK TRAN

      DROP TABLE #output
      GO

  1. Serg says:

    This decryptor is one of the best and now called dbForge decryptor. However there is a good solution to protect SQL code : Sql Shield. It is commercial and guys provide great discount if you are a small developer.

  2. Anoop says:

    Worked perfectly. Please ensure that you will login from local system to the sql server, not from any remote system. Use Windows Authentication always i.e to create the procedure and to execute it also.

  3. flipdoubt says:

    This article is quite informative, but the reader has to do a lot of tweaking to get it to work because whatever you used to enter the procedure’s text reformatted what you wrote such that the double-dashes are now em-dashes. It prettified it where you just want it to work. You should consider attaching a plain-text SQL file.

  4. Keneke says:

    Thanks! I downloaded and installed the Optillect SQL Decryptor utility, and it works great. Very nice interface and extremely fast – I would recommend using it instead of running the SQL.

    • maplemale says:

      This is on Microsoft SQL Server 2005 – 9.00.5324.00 (Intel X86) Aug 24 2012 14:24:46 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

  5. eralper says:

    Hi, this works like a charm. That is a great procedure to decrypt encrypted stored procedures. I tried it on a SQL2014 CTP2 database and successfully returned the text of the db object

  6. Sri says:

    Thank you very much, my SSMS was no opening in DAC mode, but the third party tool did the trick for me and i was able to get my encypted procedure back. Thanks once again

  7. Plutarco says:

    Simplemente una cátedra de SQL!!
    Muchas gracias por tan tremendo aporte, es muy fácil hablar de terceros, pero conocer a fondo la herramienta es lo difícil.
    Enorme aporte y MUCHAS GRACIAS!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s