RktDetection.sql

RKTDetection.sql is a TSQL script that will script that return the definitions of system

• stored procedures
• functions
• views

RKTDetection.sql can be downloaded here

This script should be run on a victim system and then again on a known-good SQL Server instance. It's important that the victim and known-good systems are running the same SQL Server version and service pack to reduce fals positives.

Once the results of both scripts have been preserved they can be imported into a SQL database and compared using the following sytnax.

-- Step #1
--
-- Create trusted and non trusted tables to hold collected definitions
--
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'DBSE_TrDf' AND type = 'U')
DROP TABLE [DBSE_TrDf]
GO
CREATE TABLE [dbo].[DBSE_TrDf](
[object] [varchar](max) NULL,
[Line] [varchar](max) NULL,
[SYNTAX] [varchar](max) NULL,
[Hash] [varchar](max) NULL
) ON [PRIMARY]
GO
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'DBSE_VcDf' AND type = 'U')
DROP TABLE [DBSE_VcDf]
GO
CREATE TABLE [dbo].[DBSE_VcDf](
[object] [varchar](max) NULL,
[Line] [varchar](max) NULL,
[SYNTAX] [varchar](max) NULL,
[Hash] [varchar](max) NULL
) ON [PRIMARY]

-- Step #2
--
-- ** Use Bulk Insert to import trusted and victim definitions
-- ** Note you will need to change the path can defintion file as appropiate **
--
--Import trusted definitions
BULK INSERT DBSE_TrDf
FROM 'Z:\rktdetection.trusted.txt'
WITH
(
FIELDTERMINATOR ='|'
)
-- Import victim definitions
--
BULK INSERT DBSE_VcDf
FROM 'Z:\rktdetection.victim.txt'
WITH
(
FIELDTERMINATOR ='|'
)

-- Step #3
--
--Generate hashes on imported definitions
UPDATE DBSE_TrDf set hash = CHECKSUM(syntax)
UPDATE DBSE_VcDf set hash = CHECKSUM(syntax)

-- Step #4
--
-- Run the following syntax to identify definition anomalies
--
SELECT trusted.object, trusted.line, trusted.syntax as 'trusted_syntax',
UnTrusted.syntax as 'untrusted_syntax', trusted.hash as 'trusted_hash', untrusted.hash
as 'untrusted_hash' from DBSE_TrDf trusted, DBSE_VcDf untrusted where trusted.object =
untrusted.object and trusted.line = untrusted.line and Trusted.hash <> UnTrusted.hash
order by object, line

Once complete you will have a listing of object name, and line containing the anomaly for each applicable line of syntax

Enjoy.