SQL SP's and Function's Encrypted
-
I have a few SP's and Functions encrypted by a previous SQL DBA. I have the passcode. Does anyone have the t-sql for me to decrypt and view the queries inside the SP's and Functions. I have been researching this for three days and have nor gotten straight forward answer. Please Help Me!:crazy:
MCSE SQL Server 2012\2014\2016
-
take a look at this thread, which explains you can use a combination of the Dedicated Admin Connection and a procedure to view the encrypted definition.
http://jongurgul.com/blog/sql-object-decryption/
if you happen to have Redgate's SQLCompare, it will also decrypt any object definitions,and I'm sure you can find a powershell script to connect via the DAC and decrypt as well.
also, this application, which seems to be free, will do it as well:
https://www.devart.com/dbforge/sql/sqldecryptor/
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell,
This is where I get lost...
BEGIN TRANSACTION
DECLARE @sql NVARCHAR(MAX)
SET @sql = N'ALTER'+@ObjectNameStmTemplate
EXEC sp_executesql @sql
SELECT @DummyEncObj = sov.[imageval]
FROM sys.all_objects ao
INNER JOIN sys.sysobjvalues sov ON sov.[valclass]=1 AND ao.[Object_id]=sov.[objid]
WHERE ao.[object_id] = @object_id
ROLLBACK TRANSACTION
DECLARE @Final NVARCHAR(MAX)
SET @Final = N''
DECLARE @Pos INT
SET @Pos = 1
WHILE @Pos <= DATALENGTH(@EncObj)/2
BEGIN
SET @Final = @Final + NCHAR(UNICODE(SUBSTRING(CAST(@EncObj AS NVARCHAR(MAX)),@Pos,1))^(UNICODE(SUBSTRING(N'CREATE'+@ObjectNameStmTemplate COLLATE DATABASE_DEFAULT,@Pos,1))^UNICODE(SUBSTRING(CAST(@DummyEncObj AS NVARCHAR(MAX)),@Pos,1))))
SET @Pos = @Pos + 1
END
MCSE SQL Server 2012\2014\2016
-
i just confirmed this still works on SQL2016, so you are good for all lower versions too.
ok, try opening a dedicated admin connection,
and in the database in question, add this procedure:
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*===============================================================
NAME: Decrypt SQL 2005 stored procedures, functions, views,
and triggers
DESCRIPTION:
HEADS UP: In order to run this script you must log in
to the server in DAC mode: To do so, type
ADMIN: as your server name and use the "sa"
or any other server admin user with the appropriate password.
CAUTION! DAC (dedicated admin access) will kick out all other
server users.
The script below accepts an object (schema name + object name)
that were created using the WITH ENCRYPTION option and returns
the decrypted script that creates the object. This script
is useful to decrypt stored procedures, views, functions,
and triggers that were created WITH ENCRYPTION.
The algorithm used below is the following:
1. Check that the object exists and that it is encrypted.
2. In order to decrypt the object, the script ALTER (!!!) it
and later restores the object to its original one. This is
required as part of the decryption process: The object
is altered to contain dummy text (the ALTER uses WITH ENCRYPTION)
and then compared to the CREATE statement of the same dummy
content.
Note: The object is altered in a transaction, which is rolled
back immediately after the object is changed to restore
all previous settings.
3. A XOR operation between the original binary stream of the
enrypted object with the binary representation of the dummy
object and the binary version of the object in clear-text
is used to decrypt the original object.
USER PARAMETERS: @ObjectOwnerOrSchema
@ObjectName
RESULTSET: NA
RESULTSET SORT: NA
USING TABLES/VIEWS: sys.sysobjvalues
syscomments
================================================================*/
CREATE PROCEDURE [dbo].[SQLDecryptor]
(
@ObjectOwnerOrSchema NVARCHAR(128) ,
@ObjectName NVARCHAR(128)
)
AS
--SET @ObjectOwnerOrSchema = 'dbo'
--SET @ObjectName = 'myproc'
DECLARE @i INT
DECLARE @ObjectDataLength INT
DECLARE @ContentOfEncryptedObject NVARCHAR(MAX)
DECLARE @ContentOfDecryptedObject NVARCHAR(MAX)
DECLARE @ContentOfFakeObject NVARCHAR(MAX)
DECLARE @ContentOfFakeEncryptedObject NVARCHAR(MAX)
DECLARE @ObjectType NVARCHAR(128)
DECLARE @ObjectID INT
SET NOCOUNT ON
SET @ObjectID = OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName
+ ']')
-- Check that the provided object exists in the database.
IF @ObjectID IS NULL
BEGIN
RAISERROR ( 'The object name or schema provided does not exist in the database',
16, 1 )
RETURN
END
-- Check that the provided object is encrypted.
IF NOT EXISTS ( SELECT TOP 1
*
FROM syscomments
WHERE id = @ObjectID
AND encrypted = 1 )
BEGIN
RAISERROR ( 'The object provided exists however it is not encrypted. Aborting.',
16, 1 )
RETURN
END
-- Determine the type of the object
IF OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']',
'PROCEDURE') IS NOT NULL
SET @ObjectType = 'PROCEDURE'
ELSE
IF OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']',
'TRIGGER') IS NOT NULL
SET @ObjectType = 'TRIGGER'
ELSE
IF OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName
+ ']', 'VIEW') IS NOT NULL
SET @ObjectType = 'VIEW'
ELSE
SET @ObjectType = 'FUNCTION'
-- Get the binary representation of the object- syscomments no longer holds
-- the content of encrypted object.
SELECT TOP 1
@ContentOfEncryptedObject = imageval
FROM sys.sysobjvalues
WHERE objid = OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName
+ ']')
AND valclass = 1
AND 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 + 4000 < @ObjectDataLength
SET @ContentOfFakeObject = @ContentOfFakeObject
+ REPLICATE(N'-', 4000)
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
SET XACT_ABORT OFF
BEGIN TRAN
EXEC ( @ContentOfFakeObject
)
IF @@ERROR <> 0
ROLLBACK TRAN
-- Get the encrypted content of the new "fake" object.
SELECT TOP 1
@ContentOfFakeEncryptedObject = imageval
FROM sys.sysobjvalues
WHERE objid = OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName
+ ']')
AND valclass = 1
AND subobjid = 1
IF @@TRANCOUNT > 0
ROLLBACK TRAN
-- 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 + 4000 < @ObjectDataLength
SET @ContentOfFakeObject = @ContentOfFakeObject
+ REPLICATE(N'-', 4000)
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 + 4000 < @ObjectDataLength
SET @ContentOfDecryptedObject = @ContentOfDecryptedObject
+ REPLICATE(N'A', 4000)
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 in chunks of 2K characters
SET @i = 0
WHILE DATALENGTH(@ContentOfDecryptedObject) / 2 > ( @i + 1 ) * 2000
BEGIN
PRINT ( SUBSTRING(@ContentOfDecryptedObject, 1 + 2000 * @i,
2000 * ( @i + 1 )) )
SET @i = @i + 1
END
-- Now print the last chunk, or the only chunk
-- (if @ContentOfDecryptedObject does not exceed 2K characters)
PRINT ( SUBSTRING(@ContentOfDecryptedObject, 1 + 2000 * @i,
2000 * ( @i + 1 )) )
then, still in the same Dedicated Admin Connection window, Erase the code int he window, and execute the procedure for each object you know exists:
EXEC [dbo].[SQLDecryptor] 'dbo','TheEncryptedProcedure'EXEC [dbo].[SQLDecryptor] 'Production','vwSomeEncryptedView'
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
If I run that it says the object is not encrypted or not found.
If I try to modify or alter I get access rights error.
The UserDefinedFunction '[dbo].[FunctionName]' cannot be scripted as its data is not accessible. (Microsoft.SqlServer.Smo)
Property TextHeader is not available for UserDefinedFunction '[dbo].[FunctionName]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. The text is encrypted. (Microsoft.SqlServer.Smo)
I have redgate SQL Prompt7 downloaded, do I need another product to view this?
MCSE SQL Server 2012\2014\2016
-
are you a sysadmin?
are you connecting via a dedicated connection?
does this return anything with the IsEncrypted column being 1?
SELECT
sm.object_id,
OBJECT_SCHEMA_NAME(sm.object_id) AS SchemaName,
OBJECT_NAME(sm.object_id) AS object_name,
o.type, o.type_desc,
sm.definition,
CAST(CASE WHEN sm.definition IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted]
FROM sys.sql_modules AS sm
JOIN sys.objects AS o ON sm.object_id = o.object_id
ORDER BY o.type;
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
yes I am sysadmin and yes I am in on a DAC connection. I double checked my properties and the connection name is admin:SqlServerName (DomainName\MyUserName)
and yes when I run the query above the IsEncrypted = 1 for the object
MCSE SQL Server 2012\2014\2016
-
very weird;
since you got an SMO error, i might worry that you are using an older version of SQL Server Management Studio to connect to a higher version of SQL server than SSMS, but i'm out of ideas.
i was able to decrypt with the code i provided, and I've done it plenty of times, in otehr flavors of SQL before.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I am on the SQL Server itself. Thank you for your help, I may just have to get RedGate.
MCSE SQL Server 2012\2014\2016
-
I think that works when it's created with the standard WITH ENCRYPTION on the create for the stored procedure - i.e. Create Procedure SomeProc With Encryption as blah blah blah.
I'm guessing that's not how these were encrypted just with the statement:
I have a few SP's and Functions encrypted by a previous SQL DBA. I have the passcode.
It sounds like they could have been created with SMK, DMK or something along those lines.
Sue
-
That's what I am thinking too. There is a password that I have to decypt.
MCSE SQL Server 2012\2014\2016
-
OK, I had to use RedGate SQL Prompt 7 to decrypt the Function.
I don't think that I have ever been this frustrated with SQL in my life!
thank you all for your help!
MCSE SQL Server 2012\2014\2016
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply