In SQL 2005, Microsoft removed this 'feature' by replacing the comment text with a null value and storing the encrypted SQL as an image value in sysobjvalues. The text is just bit shifted and can be bit shifted back with a little effort. After all, SQL has to decrypt it to re-compile it anyway.
If you spend any time at all supporting real-world environments, you will eventually find the need to debug or even repair an encrypted object that someone else wrote. I found myself in this situation many times. A quick google search turned up dozens of tools that promised to do this for SQL 2005 and SQL 2008. I tried a couple and was disappointed with the results. I also really needed something that I did not have to install on a customer's server. Ideally, I would be able to connect to the customer's database, decrypt the proc without affecting any of their existing SQL objects, and copy paste the code into a local editor window to take a look. I found several different discussions from which I gleaned an understanding of how to get the text back using nothing but SQL itself. (Again, logically you would expect this, since SQL decrypts the procs all the time in order to compile and run them).
Before I go further, let me state that I am not condoning stealing proprietary code. I am also not condoning the use of SQL encryption to protect your proprietary code.
I feel that if your code is super-secret, you should use one of the following methods to protect it:
- Put the logic in the application layer, rather than the data layer.
- Host the databases for your customers so you can control access to the objects
Ok, let me climb down off my soap box and show you what I came up with.
After some experimentation, I created a stored proc that will run on 2005 and 2008 and decrypt the majority of the stored procs I have pointed it at. It is either all or nothing. Either you get the entire stored proc or you get null back.
Edit: On very long procs I have noticed that it returns extra line breaks when reading across pages. These can occur in the middle of words, so some clean up of these may be required if you want to execute the output. Some day I may get around to fixing it programmatically. If you get an itch and want to do it yourself, I would welcome you solution in the comments to this post.
As an additional learning experiment, I decided to encrypt the proc and create it so that it could decrypt itself.
You must use a dedicated admin connection or DAC to create and run the proc, since it requires access to sysobjvalues.
Edit: For SQL 2012, you can connect to DAC by closing all connections and windows in SSMS, then click the Database Engine Query button on the toolbar. Login as Admin:xxxxx using SQL authentication and sa user.
Edit: OK, so today I got tired of removing the extra line breaks after each block. Not easy to fix. So I added an easy to find and replace string that I can use to quickly remove those breaks. In SSMS you can do a find/replace on the output using wildcards like this \n|||remove|||\n That will remove the extra line breaks and the script should compile.
--you must connect to Admin: SQLInstance
DROP PROC DecryptObject
Did this help you? If so, please leave a comment!
GO
CREATE PROCEDURE DecryptObject (
@schema nvarchar(255),
@ObjectName nvarchar(255))
with encryption
AS
BEGIN
DECLARE @id INT,
@TYPE NVARCHAR(50),
@Statement NVARCHAR(4000),
@IsEncrypted BIT
--GET THE ID FOR THE OBJECT
SELECT TOP 1 @id=object_id
FROM sys.all_objects
WHERE [type] NOT IN ('S','U','PK','F','D','SQ','IT','X','PC','FS','AF')
AND name = @ObjectName
AND SCHEMA_NAME([schema_id]) = COALESCE(@schema,'dbo')
IF @id IS NULL
BEGIN
PRINT @ObjectName + ' not found!'
RETURN
END
--SEE IF IT IS ENCRYPTED
SELECT @IsEncrypted=[encrypted]
FROM syscomments
WHERE [id] = @id
AND colid = 1
IF @IsEncrypted = 0
BEGIN
PRINT @ObjectName + ' not encrypted!'
RETURN
END
IF NOT EXISTS(SELECT 1 FROM sys.dm_exec_connections C INNER JOIN sys.endpoints E ON C.[endpoint_id]=E.[endpoint_id] WHERE E.[name]='Dedicated Admin Connection' AND C.[session_id] = @@SPID)
BEGIN
PRINT 'This procedure can only be run using a DAC Connection'
RETURN
END
--GET THE REST OF THE VALUES
SELECT @ObjectName=[name],
@TYPE= CASE WHEN [type] = 'P' THEN N'PROCEDURE'
WHEN [type] = 'V' THEN 'VIEW'
WHEN [type] IN ('FN','TF','IF') THEN N'FUNCTION'
ELSE [type]
END,
@Statement=CASE WHEN [type] = 'P' THEN N'WITH ENCRYPTION AS'
WHEN [type] = 'V' THEN N'WITH ENCRYPTION AS SELECT 123 ABC'
WHEN [type] IN ('FN','TF','IF') THEN N' () RETURNS INT WITH ENCRYPTION AS BEGIN RETURN 1 END'
ELSE [type]
END
FROM sys.all_objects
WHERE object_id = @id
DECLARE @BlockNumber INT,
@Block NVARCHAR(MAX),
@pos INT,
@DummyBlock NVARCHAR(MAX),
@DummyImage VARBINARY(MAX),
@EncyptedBlock NVARCHAR(MAX),
@EncyptedImage VARBINARY(MAX),
@pos2 INT,
@part1 NVARCHAR(MAX),
@part2 NVARCHAR(MAX),
@DummyImageSQL NVARCHAR(MAX),
@NewText NVARCHAR(4000)
--GET THE ECRYPTED IMAGE VALUE
SELECT @EncyptedImage = imageval
FROM sys.sysobjvalues
WHERE [objid] = @id
AND valclass = 1
BEGIN TRANSACTION
--BUILD THE SQL FOR THE DUMMY IMAGE
SELECT @pos2 = 1,
@part1= N'ALTER ' + @TYPE + ' ' + ISNULL((@schema+'.'),'') + @ObjectName + ' ' + @Statement
--FILL THE REST OF THE STRINGS AND START THE SQL FOR THE DUMMY IMAGE
SELECT @part1 = @part1 + REPLICATE('-',4000-LEN(@part1)),
@part2 = REPLICATE('-',8000),
@DummyImageSQL = N'EXEC(@part1'
--PROCESS THE ENCRYPTED IMAGE ONE BLOCK AT A TIME
WHILE @pos2 <= CEILING(DATALENGTH(@EncyptedImage) / 8000.0)
BEGIN
--BUILD THE SQL FOR THE DUMMY IMAGE
SET @DummyImageSQL=@DummyImageSQL + N'+@part2'
SET @pos2 = @pos2 + 1
END
--FINISH THE SQL
SELECT @DummyImageSQL = @DummyImageSQL + ')'
--EXEC SQL TO CREATE DUMMY IMAGE
EXEC sp_executesql @DummyImageSQL,
N'@part1 NVARCHAR(4000),@part2 VARCHAR(8000)',
@part1=@part1,
@part2=@part2
--GO GET THE DUMMY IMAGE THAT WAS CREATED
SELECT @DummyImage = imageval
FROM [sys].[sysobjvalues]
WHERE [objid] = @id
and [valclass] = 1
ROLLBACK TRANSACTION
SET @BlockNumber=1
--PROCESS ONE BLOCK AT A TIME
WHILE @BlockNumber<=CEILING(DATALENGTH(@EncyptedImage) / 8000.0)
BEGIN
--GET THE NEXT BLOCK FROM THE ENCRYPTED IMAGE AND THE DUMMY IMAGE
SELECT @EncyptedBlock = SUBSTRING(@EncyptedImage, (@BlockNumber - 1) * 8000 + 1, 8000),
@DummyBlock = SUBSTRING(@DummyImage, (@BlockNumber - 1) * 8000 + 1, 8000)
IF @BlockNumber=1
BEGIN
--BUILD THE CREATE STATEMENT HEADER
SELECT @NewText = N'CREATE ' + @TYPE + ' ' + ISNULL((@schema + '.'),'') + @ObjectName + ' ' + @Statement + REPLICATE('-',4000)
END
ELSE
BEGIN
--FILL IN HALF A BLOCK
SELECT @NewText=REPLICATE('-', 4000)
END
--FILL HALF THE SIZE OF THE ENCRYPTED BLOCK
SELECT @Block = REPLICATE(N'A', (DATALENGTH(@EncyptedBlock) / 2)),
@pos=1
--PROCESS ONE BIT AT A TIME
WHILE @pos<=DATALENGTH(@EncyptedBlock)/2
BEGIN
--USE BITWISE OR TO DECRYPT THE BIT
SELECT @Block =
STUFF(@Block, @pos, 1,
NCHAR(UNICODE(SUBSTRING(@EncyptedBlock, @pos, 1))
^ (UNICODE(SUBSTRING(@NewText, @pos, 1))
^ UNICODE(SUBSTRING(@DummyBlock, @pos, 1))
)
)
)
--NEXT POSITION
SELECT @pos = @pos+1
END
--PRINT THIS BLOCK BEFORE MOVING TO THE NEXT, THIS WILL HAVE THE EFFECT OF
--GENERATING ONE LONG STATEMENT IN THE RESULTS WINDOW
PRINT @Block
print '|||remove|||'
SELECT @BlockNumber = @BlockNumber + 1
print '|||remove|||'
SELECT @BlockNumber = @BlockNumber + 1
END
END
GO
exec DecryptObject 'dbo','DecryptObject'
