c# - 加密 SqlDependency 创建的存储过程

Question

我创建了一个 SqlDependency以便在特定查询的结果发生变化时触发事件。

// Create a command
SqlConnection conn = new SqlConnection(connectionString);
string query = "SELECT MyColumn FROM MyTable;";
SqlCommand cmd = new SqlCommand(query, conn)
cmd.CommandType = CommandType.Text;

// Register a dependency
SqlDependency dependency = new SqlDependency(cmd);
dependency.OnChange += DependencyOnChange;

当这段代码执行时，会自动创建一个存储过程，其名称如下

SqlQueryNotificationStoredProcedure-82ae1b92-21c5-46ae-a2a1-511c4f849f76

此程序未加密，这违反了我的要求。我有两个选择:

1. 让客户相信自动生成的过程未加密并不重要，因为它只进行清理工作并且不包含真实信息(感谢 ScottChamberlain 指出这一点)。
2. 找到一种方法来加密 SqlDependency 生成的存储过程。

我怎样才能完成选项 2？

---

相关存储过程的内容:

CREATE PROCEDURE [dbo].[SqlQueryNotificationStoredProcedure-b124707b-23fc-4002-aac3-4d52a71c5d6b]
AS
BEGIN
    BEGIN TRANSACTION;

    RECEIVE TOP (0) conversation_handle
    FROM [SqlQueryNotificationService-b124707b-23fc-4002-aac3-4d52a71c5d6b];

    IF (
        SELECT COUNT(*)
        FROM [SqlQueryNotificationService-b124707b-23fc-4002-aac3-4d52a71c5d6b]
        WHERE message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer'
    ) > 0
    BEGIN
        IF (
            ( 
                SELECT COUNT(*)
                FROM sys.services
                WHERE NAME = 'SqlQueryNotificationService-b124707b-23fc-4002-aac3-4d52a71c5d6b'
            ) > 0
        )
        DROP SERVICE [SqlQueryNotificationService-b124707b-23fc-4002-aac3-4d52a71c5d6b];

        IF (OBJECT_ID('SqlQueryNotificationService-b124707b-23fc-4002-aac3-4d52a71c5d6b', 'SQ') IS NOT NULL)
            DROP QUEUE [SqlQueryNotificationService-b124707b-23fc-4002-aac3-4d52a71c5d6b];

        DROP PROCEDURE [SqlQueryNotificationStoredProcedure-b124707b-23fc-4002-aac3-4d52a71c5d6b];
    END

    COMMIT TRANSACTION;
END
GO

Answer 1

创建一个 DDL 触发器，检查是否正在创建一个名为“SqlQueryNotificationStoredProcedure-”的过程，如果是，立即更改它WITH ENCRYPTION:

CREATE TRIGGER [TR_EncryptQueryNotificationProcedures] 
ON DATABASE
AFTER CREATE_PROCEDURE, ALTER_PROCEDURE
AS
BEGIN
    SET ARITHABORT ON;
    SET NOCOUNT ON;
    IF TRIGGER_NESTLEVEL() > 1 RETURN;

    -- For debugging purposes only
    PRINT CONVERT(NVARCHAR(MAX), EVENTDATA());

    DECLARE @DatabaseName NVARCHAR(128);
    SET @DatabaseName = EVENTDATA().value(
        '(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(128)'
    );
    DECLARE @Schema NVARCHAR(128);
    SET @Schema = EVENTDATA().value(
        '(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(128)'
    );
    DECLARE @Name NVARCHAR(128);
    SET @Name = EVENTDATA().value(
        '(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(128)'
    );

    DECLARE @Definition NVARCHAR(MAX);
    SELECT @Definition = 
        OBJECT_DEFINITION(
            OBJECT_ID(
                QUOTENAME(@DatabaseName) + '.' + 
                QUOTENAME(@Schema) + '.' + 
                QUOTENAME(@Name),
                'P'
            )
        )
    ;

    -- If the sproc is already encrypted, we can't do anything with it
    IF @Definition IS NULL RETURN;  

    SELECT @Definition = STUFF(
        @Definition, 
        CHARINDEX('CREATE', @Definition), 
        LEN('CREATE'), 
        'ALTER'
    );

    IF 
        @Name LIKE 'SqlQueryNotificationStoredProcedure-%' AND
        -- this should always be false since we can't read encrypted definitions, 
        -- but just to make sure 
        @Definition NOT LIKE '%WITH ENCRYPTION AS BEGIN%' 
    BEGIN;
        SET @Definition = REPLACE(
            @Definition, 'AS' + CHAR(13) + CHAR(10) + 'BEGIN', 
            'WITH ENCRYPTION AS BEGIN'
        );
        EXEC (@Definition);
    END;
END;
GO
ENABLE TRIGGER [TR_EncryptQueryNotificationProcedures] ON DATABASE;

免责声明:未针对实际依赖项通知进行测试，但基本思想是合理的。它非常脆弱，因为它取决于过程的确切形式，当然 - 让它更健壮是可能的，但很乏味。