Đỉnh NGUYỄN

life's a journey not a destination

Decrypt store proc trong MS SQL Server

Leave a comment


Trên mạng có rất nhiều tools và mã hỗ trợ decrypt store proc trong MS SQL Server 2005, mình thử và thấy đoạn mã này hoạt động cũng ok

1. Login vào MS SQL Server Management Studio 2005 với quyền đặc biệt gọi là Dedicated Administrator Connection

Trên thanh công cụ, các bạn chọn biểu tượng Database Engine Query (nằm sát bên phải biểu tượng có chữ “New Query”)

Tại cửa sổ đăng nhập, thêm ADMIN: phía trước tên server (ví dụ: ADMIN:TFSSVR\MSSQL05) và đăng nhập theo cách thông thường

Vì login theo cách này thì không thể chọn hoặc nhìn thấy các database có trong MS SQL, nên phải dùng lệnh use để chuyển đến database chứa các store proc cần decrypt (ví dụ: use Northwind)

Và tạo store proc sau

2. Tạo Store Proc

/****** Object:  StoredProcedure [dbo].[sp_SQLDecrypt]    Script Date: 09/04/2009 23:04:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_SQLDecrypt]
@ObjectOwnerOrSchema NVARCHAR(128) = 'dbo',
@ObjectName NVARCHAR(128)
AS BEGIN

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
DECLARE @CmdText NVARCHAR(MAX)
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)
SET @CmdText = 'DROP PROCEDURE ' + '[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']'
EXEC (@CmdText)

-- PRINT(SUBSTRING(@ContentOfDecryptedObject, 1 + 2000*@i, 2000*(@i + 1)))
SET @CmdText = REPLACE(SUBSTRING(@ContentOfDecryptedObject, 1 + 2000*@i, 2000*(@i + 1)), 'WITH ENCRYPTION', '')
EXEC (@CmdText)
END

3. Dùng Store Proc trên để decrypt các store proc bị encrypted

Ví dụ: exec sp_SQLDecrypt ‘dbo’,’sp_StoreName’

Nhớ chú ý schema nha các bạn

Chúc vui!

Advertisements

Author: dinhnn

Senior software developer, a technical leader. You can be reached at via email to dinhnguyenngoc@gmail.com, via my blog at dinhnguyenngoc.wordpress.com, and on Twitter @dinhnguyenngoc.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s