October 3, 2003 at 5:46 am
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
October 3, 2003 at 12:06 pm
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
October 5, 2003 at 4:40 pm
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
October 5, 2003 at 4:44 pm
That script only decrypts a limited sized code. There is also a program which is capable of decrypting bigger scripts..
October 5, 2003 at 11:26 pm
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
October 6, 2003 at 10:06 am
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.
October 6, 2003 at 10:57 am
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
October 6, 2003 at 6:42 pm
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
K. Brian Kelley
@kbriankelley
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply