how to decrypt a encrypted stored proc in 2005

[DallasBlue]

Hi,
can I get back the source code of the stored procedure which is created
with 'with encryption' option.
sys.sql_modules/sys.all_sql_modules/syscomments dosent show it even after
logging in DAC. All I see is null in the text column. and no luck with the
trace as well.

has anyone tried attaching a debugger to the server process to get the
encrypted stored procedures text ??

Thanks,
GA

[Sue Hoegemeier]

That won't show it either. You can't see the definition of
encrypted stored procedures - that would kind of defeat the
purpose of encrypting them in the first place.
You need to have the original source code of stored
procedures created with the encryption option.

-Sue

[DallasBlue]

I actually compiled my proc with encryption and now i dont have src code so I
was trying to get it back... there sure must be a way as I saw some tools
doing it.

Any thots, hints , pointers will be appreciated.

thanks,
GA

[Puneet]

There's a software, which claims it does it, you can download it from
http://www.elitude.net/
However, not sure, if it's going to mess up your system even furthur or how
it will do it. and seems like you got to purchase it for full version.

Cheers,

Puneet.

[Dr. Network]

SQL 2005 still obfuscates object encryption in a similar manner to SQL 2000.
It is simply an XOR of the encrypted bytes against a known sample to dump
out the real unencrypted bytes. The trick is getting to the the encrypted
bytes. In SQL 2005, instead of querying the SYSCOMMENTS view to get the
binary bytes to decrypt, you need to run the following query from the
dedicated admin connection (DAC) to get the bytes to decrypt:

SELECT imageval FROM sys.sysobjvalues WHERE id = object_id(@procedure) AND
valclass = 1 AND subobjid = 1

where @procedure is the name of the object that you want to decrypt. The
table sys.sysobjvalues is one of those new system tables that is normally
hidden. It is actually easier in SQL 2005 because you don't have to string
together 4000 character blocks from SYSCOMMENTS records like you had to do
in SQL 2000. Code to do the XOR on SQL 2000 is well documented on the web.
I'm including my work below.

This is code that I quickly rolled together from my old SQL 2000 decrypt
code. It isn't perfect yet but should get you down the line. It only does
procedures. It actually wraps the ALTER PROCEDURE in a rolled-back
transaction so it shouldn't be too problematic. Then again my local copy of
SQL Server 2005 only starts with the -f flag this morning ;-). Have fun.

Chuck

CREATE PROCEDURE dbo.sp__procedure$decrypt
(@procedure sysname = NULL, @revfl int = 1)
AS
SET NOCOUNT ON

IF @revfl = 1
BEGIN
PRINT 'CAUTION: THIS PROCEDURE DELETES AND REBUILDS THE ORIGINAL STORED
PROCEDURE.'
PRINT ' MAKE A BACKUP OF YOUR DATABASE BEFORE RUNNING THIS PROCEDURE.'
PRINT ' IDEALLY, THIS PROCEDURE SHOULD BE RUN ON A NON-PRODUCTION COPY
OF THE PROCEDURE.'
PRINT ' To run the procedure, change the @revfl parameter to 0'
RETURN 0
END

DECLARE @intProcSpace bigint, @t bigint, @maxColID smallint,@intEncrypted
tinyint,@procNameLength int
select @maxColID = max(colid),@intEncrypted = encrypted FROM
sys.sysobjvalues WHERE id = 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 id =
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 id =
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

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

"Puneet" <Pun...@discussions.microsoft.com> wrote in message
news:CB5448F0-0CD4-4C61...@microsoft.com...

[Dr. Network]

Some may criticize me for telling this "secret". For what reason?

Everyone that cares needs to realize that object "encryption" in SQL 2005 is
only obfuscation. It should not be relied upon to really hide anything of
real tangible value.

You still need to log in with the DAC to get at the encrypted object. If
people can log into your database with the DAC, they have the keys to the
kingdom already.

If you really need to hide a secret in SQL Server, you have two options in
my mind:

1.) If you are a vendor and are encypting your objects to guard your
intellectual property, I would encourage you to use SQL CLR with a
professional obfuscation application over the top of your .NET code to guard
your property and save the TSQL stored procedures and functions for data
access.
2.) Use the DBMS data encryption using the master keys and the like and not
object encrytion.

Chuck

"Dr. Network" <charles...@jenzabar.net> wrote in message
news:%23Cr%23k63x...@TK2MSFTNGP06.phx.gbl...

[Mike C#]

I was actually a little surprised that they didn't change it up to use the
built-in symmetric encryption functionality to encrypt/decrypt SP's and
still use that XOR method. I did notice that BOL was careful to call it
"obfuscation" rather than "encryption", although the keywords are still
"WITH ENCRYPTION" and not changed to the more accurate "WITH OBFUSCATION"...

"Dr. Network" <charles...@jenzabar.net> wrote in message

news:OKOzj$3xGHA...@TK2MSFTNGP05.phx.gbl...

[Dr. Network]

I suppose if you really don't want to let out your intellectual property, I
would simply obfuscate everything, including table and column names. If you
are a vendor, you simply have the logical names in your data model and they
map to obscure physical names. Then your stored procedures could be
obfuscated by removing all line feeds and excess spaces and tabs and
obfuscating/encrypting them.

It could get pretty ugly.

Chuck

"Mike C#" <x...@xyz.com> wrote in message
news:%23DE7%23w4xG...@TK2MSFTNGP04.phx.gbl...

[Mike C#]

Yeah, I think the best defense against clients messing with your IP is a
solid legal contract :)

"Dr. Network" <charles...@jenzabar.net> wrote in message

news:%23qb%23244xG...@TK2MSFTNGP04.phx.gbl...

[Laurentiu Cristofor [MSFT]]

We cannot change the syntax for backward compatibility reasons. We cannot
remove the feature for the same reason. So we documented the feature as what
it is: obfuscation.

The purpose of this feature was to make it hard for someone to reverse
engineer the code in a database. But the feature can't be unbreakable
because the system must be able to access the code, and the system
administrator controls the system. Using a stronger encryption algorithm
won't help because we have no way to protect the encryption key - it has to
be available to the system and hence to the system administrator. Such a
feature cannot transcend its obfuscation state, at least not with the
current technology.

So the feature is unchanged in SQL 2005 from SQL 2000. You should not think
of it as a security feature - it's just a code obfuscation scheme.

Thanks

--
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/

This posting is provided "AS IS" with no warranties, and confers no rights.

"Mike C#" <x...@xyz.com> wrote in message
news:%23DE7%23w4xG...@TK2MSFTNGP04.phx.gbl...

[VJ]

When I use your stored proc on SQL 2005, i get following error message:

Invalid object name 'sys.sysobjvalues'.

Is something I am doing wrong or the Stored Proc is broken?

Let me know.

Thanks.

[DallasBlue]

awesome. Thank you so very much. and thanks a million :-)

sysobjvalues .. that did the trick , UUUUUUhhhhhh I did not find anywhere.

[DallasBlue]

First you need to log in to sql server 2005 in DAC mode, Only then you can
see this sysobjvalues....

[DallasBlue]

awesome. Thank you so very much.

sysobjvalues... uuuhhhhhm I could not find it for two days...you ROCK
Dr.Network

thanks,GA

[DallasBlue]

--Here you go. this is the working version , basically encrypt column is not
in sysobjvalues and id column is objid HTH

CREATE PROCEDURE dbo.sp__procedure$decrypt
(@procedure sysname = NULL, @revfl int = 1)
AS
SET NOCOUNT ON

IF @revfl = 1
BEGIN
PRINT 'CAUTION: THIS PROCEDURE DELETES AND REBUILDS THE ORIGINAL STORED
PROCEDURE.'
PRINT ' MAKE A BACKUP OF YOUR DATABASE BEFORE RUNNING THIS PROCEDURE.'
PRINT ' IDEALLY, THIS PROCEDURE SHOULD BE RUN ON A NON-PRODUCTION COPY
OF THE PROCEDURE.'
PRINT ' To run the procedure, change the @revfl parameter to 0'
RETURN 0
END

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 =

OPEN ms_crs_syscom

CLOSE ms_crs_syscom
DEALLOCATE ms_crs_syscom

DROP TABLE #CommentText

DROP TABLE #output

GO

[VJ]

I was able to make it work on a test stored proc - just few lines in
the code. But I have a very large Stored Proc - probably more than 15
lines in the SysComment table (close to 60000 characters). For this
stored proc, the decrypt sp returns no results. I am not sure if this
is a problem with the decrypt stored proc or there is something else
they may have done to encrypt the sp.

[VJ]

After doing some more testing, I realized that if i have more than 3900
characters in the Stored Proc, it wouldn't work. Please let us know if
there is a fix for this.

Thanks.

[Theo Ekelmans]

Op 28 Aug 2006 12:37:17 -0700 schreef VJ:
I've changed the code in the script a bit to accommodate >4K SP's, and reformatted it a bit to make it a bit more readable.

Enjoy

Theo

CREATE PROCEDURE dbo.sp_SpDeObfuscation (@procedure sysname = NULL, @safety int = 1)
AS

/*
Name: sp_SpDeObfuscation
Purpose: Decrypt SP's in SQL 2005
Author: Theo Ekelmans (th...@ekelmans.com)
Based on: A script that is discussed in news://microsoft.public.sqlserver.security
Version: 1.0 - 2006-11-16
Changes: none (yet)

Input: exec sp_SpDeObfuscation '<sp_name>', 0

Output: Switch to text output!

Note: You need to use the Dedicated Administrator Connection with
SQL Server Management Studio be in DAC to be able to run this SP
*/

SET NOCOUNT ON

IF @safety = 1

BEGIN
PRINT 'CAUTION: THIS PROCEDURE DELETES AND REBUILDS THE ORIGINAL STORED PROCEDURE.'

PRINT ' '

PRINT 'MAKE A BACKUP OF YOUR DATABASE BEFORE RUNNING THIS PROCEDURE.'

PRINT ' '

PRINT 'IDEALLY, THIS PROCEDURE SHOULD BE RUN ON A NON-PRODUCTION COPY OF THE PROCEDURE.'

PRINT ' '
PRINT 'To run the procedure, change the @safety parameter to 0'
RETURN 0
END

DECLARE @intProcSpace bigint
DECLARE @t bigint
DECLARE @maxColID smallint
DECLARE @intEncrypted tinyint
DECLARE @procNameLength int

DECLARE @real_01 nvarchar(max)
DECLARE @fake_01 nvarchar(max)
DECLARE @fake_encrypt_01 nvarchar(max)
DECLARE @real_decrypt_01 nvarchar(max)

DECLARE @real_decrypt_01a nvarchar(max)

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

SELECT @maxColID = max(subobjid)

FROM sys.sysobjvalues
WHERE objid = object_id(@procedure)

select @procNameLength = datalength(@procedure) + 29

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 )

-- We'll begin the transaction and roll it back later
BEGIN TRAN

-- alter the original procedure, replacing with dashes ( the cast('-' as nvarchar(max)) is to allow for SP's larger than 4K)
SET @fake_01 = 'ALTER PROCEDURE '+ @procedure +' WITH ENCRYPTION AS ' + REPLICATE(cast('-' as nvarchar(max)), 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(cast('-' as nvarchar(max)), 40003 - @procNameLength)

--start counter
SET @intProcSpace=1

--fill temporary variable with with a filler character ( the cast(N'A' as nvarchar(max)) is to allow for SP's larger than 4K)
SET @real_decrypt_01 = replicate(cast(N'A' as nvarchar(max)), (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 check' from #output -- Testing

-- -------------------------------------
-- Beginning of extract from sp_helptext
-- -------------------------------------
declare @dbname sysname

declare @BlankSpaceAdded int
declare @BasePos int
declare @CurrentPos int
declare @TextLength int
declare @LineId int
declare @AddOnLen int
declare @LFCR int --lengths of line feed carriage return
declare @DefinedLength int
declare @SyscomText nvarchar(max)
declare @Line nvarchar(255)

OPEN ms_crs_syscom

--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

-- Clean up

CLOSE ms_crs_syscom
DEALLOCATE ms_crs_syscom
DROP TABLE #CommentText

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

ROLLBACK TRAN
DROP TABLE #output

-- Drop the procedure that was setup with dashes and rebuild it with the good stuff

GO

[virtual gray]

Oh! the listing is not complete.

Vg

*** Sent via Developersdex http://www.developersdex.com ***

[Shota.G...@gmail.com]

What about other "encrypted" objects in database.
Is there any ready script for them?
i.e. udfs, views.

Would be glad if somebody can post it.

[Theo Ekelmans]

Not complete? In what way?

Theo

Op Fri, 24 Nov 2006 23:10:41 -0800 schreef virtual gray:

[Theo Ekelmans]

I'm not posting that one.

The thead i replied in was about the lack security, en i just helped
pointing out that you cannot rely on SQL for protection of your SP's.

Encryption is done for a reason, if you want info about something that
is encrypted, contact the developer. I for one would be pissed if my
code was reused in some other project without my permission.

Grtz,

Theo Ekelmans

Op 4 Dec 2006 05:24:44 -0800 schreef Shota.G...@gmail.com:

[David Rueter]

Would the following have any value as a means to protect stored procedure
source?

Suppose that the stored procedures are needed exclusively from a particular
application. What if the application established a connection to the
database, and they dynamically created all the stored procedures as
temporary stored procedures?

How hard would it be for a malicious snooper to obtain the source for
temporary stored procedures, assuming that he could not access the session
in which the temporary stored procedures were created?

(I understand the performance impact could be considerable--but in my case
one session will be used extensively for an extended period of time--so the
total impact for creation of the temporary stored procedures should be
acceptable.)

I have T-SQL code that I'd like to protect as much as is reasonable, but
would not be eager to move it to .NET

Your comments are appreciated.

Sincerely,

David Rueter
dru...@assyst.com

"Theo Ekelmans" <theo.e...@ordina.nl> wrote in message
news:113cm6c9xwqqd.1...@40tude.net...

[unknown]

This is great code... with one exception. There is a limit to the amount
of characters it decrypts. If the imageval's datalength is 8000 or
less... it's great. If it is more... it only can do the 8000 and then
fails because of the varchar(max) limitation. I have been playing with
this code doing substrings of the imageval... but still can't get the
rest of the procedure decrypted. Does anyone have any working code for
larger stored procedures?

[unknown]

Guess I should read to the last page. It still isn't working. I patched
the bottom of the previous code... since it was missing.. to that of the
"greater than 4k" code. It still only returns the top 4000 characters.

[unknown]

This is the full code. Thanks THEO!

CREATE PROCEDURE [dbo].[sp_SpDeObfuscation]

(@procedure sysname = NULL, @revfl int = 1)
AS
SET NOCOUNT ON

IF @revfl = 1

BEGIN
PRINT 'CAUTION: THIS PROCEDURE DELETES AND REBUILDS THE ORIGINAL STORED
PROCEDURE.'

PRINT ' MAKE A BACKUP OF YOUR DATABASE BEFORE RUNNING THIS
PROCEDURE.'
PRINT ' IDEALLY, THIS PROCEDURE SHOULD BE RUN ON A NON-PRODUCTION
COPY OF THE PROCEDURE.'
PRINT ' To run the procedure, change the @revfl parameter to 0'
RETURN 0
END

select @real_decrypt_01a = ''

EXECUTE (@fake_01)

--start counter
SET @intProcSpace=1

SET @intProcSpace=@intProcSpace+1
END

print @real_decrypt_01

OPEN ms_crs_syscom

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

[unknown]

[Theo Ekelmans]

Hi David,

It would not be very hard to grab your code, i envisage a job running once a minute
checking for new SP's, and decoding that.

If you want to protect your T-SQL code, there is no good way in SQL right now.

Grtz,
Theo

Op Wed, 13 Dec 2006 15:29:17 -0800 schreef David Rueter:

[Jon Jon]

Has anyone adjusted this script to decrypt functions, I
looked at this and in the section where it makes a dummy
or "fake" procedure, I had difficulty trying to do this
with a function because you can make a procedure with the
just saying "create procedure foo as" this will not work
with a function the smallest code I could get is something
like "create function foo () return int as begin return 0
end" so one would neet to put the (40003 - proclength) in
the "------" section in right before the return 0 and tag the
rest at the end to get it so it doesn't produce and error.
This is where I had trouble getting this to work. Any
thoughts...

[giotso...@gmail.com]

On Wednesday, August 23, 2006 at 10:41:02 PM UTC+4, DallasBlue wrote:
> Hi,
> can I get back the source code of the stored procedure which is created
> with 'with encryption' option.
> sys.sql_modules/sys.all_sql_modules/syscomments dosent show it even after

[giotso...@gmail.com]

>hi 100010