After long enough , I found something interesting .. Decrypting existing encrypted objects of SQL Server !! (Ref : Source )
Step 1: Make sure remote admin connections is enable on server if not enable using following
EXEC sp_configure ‘remote admin connections’, 1
GO
RECONFIGURE
GO
Step 2 : Connect to SQL Server using DAC (Dedicated Administrator Connection)
- Open Sql Server Management Studio ( close the authentication screen !)
- Click on new query , on that authentication screen will pops up
- For authentication in Server use “ADMIN:” as prefix in your server name
- Connect to your SQL server using your user name prefix with”ADMIN:”
Note : Make sure your username credentials for a member should belong to the sysadmin group
Step 2 : Run Following Script BY replacing YOUR DATABASE and YOUR OBJECT
DECLARE @EncObj VARBINARY(MAX),@DummyEncObj VARBINARY(MAX),@ObjectNameStmTemplate NVARCHAR(MAX)
SET NOCOUNT ON
USE [YOUR DATABASE]
DECLARE @object_id INT,@name SYSNAME
SELECT @object_id = [object_id],@name = [name]
FROM sys.all_objects
WHERE name = N’YOUR OBJECT’
SELECT TOP 1
@ObjectNameStmTemplate = [ObjectStmTemplate]
,@EncObj = [imageval]
FROM
(
SELECT
SPACE(1)+
(
CASE WHEN [type] = ‘P’ THEN N’PROCEDURE’
WHEN [type] = ‘V’ THEN ‘VIEW’
WHEN [type] IN(‘FN’,‘TF’,‘IF’) THEN N’FUNCTION’
WHEN [type] IN(‘TR’) THEN N’TRIGGER’
ELSE [type]
END
)
+SPACE(1)+QUOTENAME(SCHEMA_NAME([schema_id]))+‘.’+QUOTENAME(ao.[name])+SPACE(1)+
(
CASE WHEN [type] = ‘P’ THEN N’WITH ENCRYPTION AS’
WHEN [type] = ‘V’ THEN N’WITH ENCRYPTION AS SELECT 123 ABC’
WHEN [type] IN(‘FN’) THEN N'() RETURNS INT WITH ENCRYPTION AS BEGIN RETURN 1 END’
WHEN [type] IN(‘TF’) THEN N'() RETURNS @t TABLE(i INT) WITH ENCRYPTION AS BEGIN RETURN END’
WHEN [type] IN(‘IF’) THEN N'() RETURNS TABLE WITH ENCRYPTION AS RETURN SELECT 1 N’
WHEN [type] IN(‘TR’) THEN N’ ON ‘ + OBJECT_NAME(ao.[parent_object_id]) + ‘ WITH ENCRYPTION FOR DELETE AS SELECT 1 N’
ELSE [type]
END
) +REPLICATE(CAST(N’-‘ AS NVARCHAR(MAX)),DATALENGTH(sov.[imageval])) COLLATE DATABASE_DEFAULT
,sov.[imageval]
FROM sys.all_objects ao
INNER JOIN sys.sysobjvalues sov ON sov.[valclass] = 1 AND ao.[Object_id] = sov.[objid]
WHERE [type] NOT IN(‘S’,‘U’,‘PK’,‘F’,‘D’,‘SQ’,‘IT’,‘X’,‘PC’,‘FS’,‘AF’,‘TR’) AND ao.[object_id] = @object_id
UNION ALL
–Server Triggers
SELECT SPACE(1)+‘TRIGGER’+SPACE(1)+QUOTENAME(st.[name])+SPACE(1)+N’ON ALL SERVER WITH ENCRYPTION FOR DDL_LOGIN_EVENTS AS SELECT 1′
+REPLICATE(CAST(N’-‘ AS NVARCHAR(MAX)),DATALENGTH(sov.[imageval])) COLLATE DATABASE_DEFAULT
,sov.[imageval]
FROM sys.server_triggers st
INNER JOIN sys.sysobjvalues sov ON sov.[valclass] = 1 AND st.[object_id] = sov.[objid] WHERE st.[object_id] = @object_id
–Database Triggers
UNION ALL
SELECT SPACE(1)+‘TRIGGER’+SPACE(1)+QUOTENAME(dt.[name])+SPACE(1)+N’ON DATABASE WITH ENCRYPTION FOR CREATE_TABLE AS SELECT 1′
+REPLICATE(CAST(N’-‘ AS NVARCHAR(MAX)),DATALENGTH(sov.[imageval])) COLLATE DATABASE_DEFAULT
,sov.[imageval]
FROM sys.triggers dt
INNER JOIN sys.sysobjvalues sov ON sov.[valclass] = 1 AND dt.[object_id] = sov.[objid] AND dt.[parent_class_desc] = ‘DATABASE’ WHERE dt.[object_id] = @object_id
) x([ObjectStmTemplate],[imageval])
–Alter the existing object, then revert so that we have the dummy object encrypted value
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,@Pos,1))^UNICODE(SUBSTRING(CAST(@DummyEncObj AS NVARCHAR(MAX)),@Pos,1))))
SET @Pos = @Pos + 1
END
–If the object is small then just print, else print in chunks
IF DATALENGTH(@Final) <= 8000
BEGIN
PRINT ‘–SMALL–‘
PRINT @Final
END
ELSE
BEGIN
PRINT ‘–BIG–‘
DECLARE @c INT
SET @c = 0
WHILE @c <=(DATALENGTH(@Final)/8000)
BEGIN
PRINT SUBSTRING(@Final,@c+(@c*4000),4000)
SET @c = @c + 1
END
END
Happy Coding !!
Recent Comments