Hi Klaas Just bumped into thiis build script. Looking great. I checked your blog entry for instructions. But the example you give only contains unencrypted MaxScript files as input. ( *.ms )Does your build script handle MaxScript files encrypted with the encryptScript API method? (*.mse )Alvast bedankt. |
I just noticed, by checking the script, that your build script has become more advanced than when the blog post was written. It can even do the encryption for me : encryptscripts=true It works very well. Just checked with 11 input script files. Amazing quick. |
Hi Robertino, thanks for the feedback. I must say this tool has evolved quite a bit since the time I first wrote about it. I use it for all my script deployments now, couldn't live without it. I really should post a bit more about it! Klaas |
Klaas, Yes the tool certainly evolved 'a bit'. ;) And a new or updated blog post (if you can find the time) would do it more justice. What I really like is that you set it up once, and forget about it until a file needs to be added to, or removed from deployment. Thanks a lot P.S. Also just found it on Scriptspot. |
-- Must connect to SQL Server using the Dedicate Admin Connection, eg 'admin:localhost'. Verified with SQL Server 2012. |
-- Originally from Williams Orellana's blog: http://williamsorellana.org/2012/02/decrypt-sql-stored-procedures/ |
DECLARE @ObjectOwnerOrSchema NVARCHAR(128) |
DECLARE @ObjectName NVARCHAR(128) |
SET @ObjectOwnerOrSchema ='dbo' |
SET @ObjectName ='PROCEDURE NAME HERE' |
DECLARE @i INT |
DECLARE @ObjectDataLength INT |
DECLARE @ContentOfEncryptedObject NVARCHAR(MAX) |
DECLARE @ContentOfDecryptedObject VARCHAR(MAX) |
DECLARE @ContentOfFakeObject NVARCHAR(MAX) |
DECLARE @ContentOfFakeEncryptedObject NVARCHAR(MAX) |
DECLARE @ObjectType NVARCHAR(128) |
DECLARE @ObjectID INT |
SETNOCOUNTON |
SET @ObjectID =OBJECT_ID('['+ @ObjectOwnerOrSchema +'].['+ @ObjectName +']') |
-- Check that the provided object exists in the database. |
IF @ObjectID ISNULL |
BEGIN |
RAISERROR('Object not found in the database.', 16, 1) |
RETURN |
END |
-- Check that the provided object is encrypted. |
IFNOTEXISTS(SELECTTOP1*FROM syscomments WHERE id = @ObjectID ANDencrypted=1) |
BEGIN |
RAISERROR('Object is not encrypted.', 16, 1) |
RETURN |
END |
-- Determine the type of the object |
IFOBJECT_ID('['+ @ObjectOwnerOrSchema +'].['+ @ObjectName +']', 'PROCEDURE') ISNOTNULL |
SET @ObjectType ='PROCEDURE' |
ELSE |
IFOBJECT_ID('['+ @ObjectOwnerOrSchema +'].['+ @ObjectName +']', 'TRIGGER') ISNOTNULL |
SET @ObjectType ='TRIGGER' |
ELSE |
IFOBJECT_ID('['+ @ObjectOwnerOrSchema +'].['+ @ObjectName +']', 'VIEW') ISNOTNULL |
SET @ObjectType ='VIEW' |
ELSE |
SET @ObjectType ='FUNCTION' |
-- Get the binary representation of the object- syscomments no longer holds |
-- the content of encrypted object. |
SELECTTOP1 @ContentOfEncryptedObject = imageval |
FROMsys.sysobjvalues |
WHEREobjid=OBJECT_ID('['+ @ObjectOwnerOrSchema +'].['+ @ObjectName +']') |
AND valclass =1and subobjid =1 |
SET @ObjectDataLength = DATALENGTH(@ContentOfEncryptedObject)/2 |
-- We need to alter the existing object and make it into a dummy object |
-- in order to decrypt its content. This is done in a transaction |
-- (which is later rolled back) to ensure that all changes have a minimal |
-- impact on the database. |
SET @ContentOfFakeObject =N'ALTER '+ @ObjectType +N' ['+ @ObjectOwnerOrSchema +N'].['+ @ObjectName +N'] WITH ENCRYPTION AS' |
WHILE DATALENGTH(@ContentOfFakeObject)/2< @ObjectDataLength |
BEGIN |
IF DATALENGTH(@ContentOfFakeObject)/2+8000< @ObjectDataLength |
SET @ContentOfFakeObject = @ContentOfFakeObject +REPLICATE(N'-', 8000) |
ELSE |
SET @ContentOfFakeObject = @ContentOfFakeObject +REPLICATE(N'-', @ObjectDataLength - (DATALENGTH(@ContentOfFakeObject)/2)) |
END |
-- Since we need to alter the object in order to decrypt it, this is done |
-- in a transaction |
SETXACT_ABORTOFF |
BEGINTRAN |
EXEC(@ContentOfFakeObject) |
IF@@ERROR<>0 |
ROLLBACKTRAN |
-- Get the encrypted content of the new 'fake' object. |
SELECTTOP1 @ContentOfFakeEncryptedObject = imageval |
FROMsys.sysobjvalues |
WHEREobjid=OBJECT_ID('['+ @ObjectOwnerOrSchema +'].['+ @ObjectName +']') |
AND valclass =1and subobjid =1 |
IF@@TRANCOUNT>0 |
ROLLBACKTRAN |
-- Generate a CREATE script for the dummy object text. |
SET @ContentOfFakeObject =N'CREATE '+ @ObjectType +N' ['+ @ObjectOwnerOrSchema +N'].['+ @ObjectName +N'] WITH ENCRYPTION AS' |
WHILE DATALENGTH(@ContentOfFakeObject)/2< @ObjectDataLength |
BEGIN |
IF DATALENGTH(@ContentOfFakeObject)/2+8000< @ObjectDataLength |
SET @ContentOfFakeObject = @ContentOfFakeObject +REPLICATE(N'-', 8000) |
ELSE |
SET @ContentOfFakeObject = @ContentOfFakeObject +REPLICATE(N'-', @ObjectDataLength - (DATALENGTH(@ContentOfFakeObject)/2)) |
END |
SET @i =1 |
--Fill the variable that holds the decrypted data with a filler character |
SET @ContentOfDecryptedObject =N'' |
WHILE DATALENGTH(@ContentOfDecryptedObject)/2< @ObjectDataLength |
BEGIN |
IF DATALENGTH(@ContentOfDecryptedObject)/2+8000< @ObjectDataLength |
SET @ContentOfDecryptedObject = @ContentOfDecryptedObject +REPLICATE(N'A', 8000) |
ELSE |
SET @ContentOfDecryptedObject = @ContentOfDecryptedObject +REPLICATE(N'A', @ObjectDataLength - (DATALENGTH(@ContentOfDecryptedObject)/2)) |
END |
WHILE @i <= @ObjectDataLength BEGIN |
--xor real & fake & fake encrypted |
SET @ContentOfDecryptedObject =STUFF(@ContentOfDecryptedObject, @i, 1, |
NCHAR( |
UNICODE(SUBSTRING(@ContentOfEncryptedObject, @i, 1)) ^ |
( |
UNICODE(SUBSTRING(@ContentOfFakeObject, @i, 1)) ^ |
UNICODE(SUBSTRING(@ContentOfFakeEncryptedObject, @i, 1)) |
))) |
SET @i = @i +1 |
END |
-- PRINT the content of the decrypted object |
SELECTSUBSTRING(@ContentOfDecryptedObject, 1, @ObjectDataLength) AS [processing-instruction(x)] FORXMLPATH('') |