Excrypting Database Objects

Question

Excrypting Database Objects

lucky-80472

SSCertifiable

Points: 7793
More actions
October 3, 2003 at 5:46 am

#59377

Hi All,
I was given a task to identify whether Microsoft is providing any encryption at the level of database objects like tables, views, user-defined functions, triggers etc. I know that stored procedures can be encrypted by adding the keywords "WITH ENCRYPTION" to the "CREATE PROC <PROCEDURE NAME>". Otherwise, any third party tools to work around.
As i am in the learning stage, i request all those GEMS to shed some light in this aspect.
Thanks & Bye
Ravi

Lucky

Viewing 8 posts - 1 through 7 (of 7 total)

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

mromm SSCarpal Tunnel Points: 4301 More actions · Answer 1

You can use WITH ENCRYPTION option with stored procedures, user-defined functions, triggers and views alike. Those store their source code is syscomments table. If you specify WITH ENCRYPTION, syscomments does not contain their source code. This does not apply to tables. The only way to hide those from a user is to limits that user's access rights.

Hope it helps.

Michael

alkanfer SSC Veteran Points: 268 More actions · Answer 2

Encrption works but it is very easy to decrypt it...

There is a tsql script that decrypts it

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

--get encrypted data

SET @OrigSpText1=(SELECT ctext FROM syscomments WHERE id = object_id(@objectName))

SET @OrigSpText2='ALTER PROCEDURE '+ @objectName +' WITH ENCRYPTION AS '+REPLICATE('-', 3938)

EXECUTE (@OrigSpText2)

SET @OrigSpText3=(SELECT ctext FROM syscomments WHERE id = object_id(@objectName))

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

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

execute( @resultsp)

GO

alkanfer SSC Veteran Points: 268 More actions · Answer 3

That script only decrypts a limited sized code. There is also a program which is capable of decrypting bigger scripts..

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 4

Hello Michael & Alkanfer,

Thanks for your reply. From your replies, i can use the keyword "WITH ENCRYPTION" on user-defined functions, triggers and views except tables. This is a tip for me in the long run.

Mr. Alkanfer, can you post the decryption script which will be useful for decrypting large scripts. It will be useful for my work as it consists of many stored procedures. I have used the script given by you (long back) and got into trouble when i lost some procedures which are more in length. I have restored the earlier day backup, so that the lost sp's were recovered.

Thanks & Bye

Ravi

Lucky

sswords SSCrazy Eights Points: 8207 More actions · Answer 5

For SQL Server 7.0 you can use this link: http://www.mssqlcity.com/FAQ/Devel/DecryptSP.htm

For SQL Server 2000 there is another utility here: http://www.exhedra.com/URLSEO/vb/scripts/ShowCode!asp/txtCodeId!505/lngWid!5/anyname.htm.

Hope this helps.

My hovercraft is full of eels.

mromm SSCarpal Tunnel Points: 4301 More actions · Answer 6

Ravi, to avoid loosing your objects, encripted or not, store the source code of all objects in text files, preferably with some version control software.

Michael

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 7

Decryption is easy if you have sa rights. Look for dOMNAR's dSQLSRVD. A Google search should turn up several links to it. This app doesn't have the 4000 character limitation of the stored procedure that's usually passed around (originally written by shoeboy).

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/

K. Brian Kelley
@kbriankelley