Encrypt the stored procedure created by SqlDependency

Question

I created a SqlDependency so that an event would fire when the results of a particular query change.

// 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;

When this code executes, a stored procedure is automatically created with a name like

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

This procedure is unencrypted, which violates requirements I have been given. I have two options:

Convince the customer that it doesn't matter that the auto generated procedure is unencrypted because it only does cleanup work and contains no real information (thanks to ScottChamberlain for pointing this out).
Find a way to encrypt the stored procedure generated by SqlDependency.

How can I accomplish option 2?

Contents of the stored procedure in question:

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

What is the procedure doing that requires encryption? I don't have my dev environment in front of me but I think all that proc is for is cleanup in the event of a disconnect. It contains no information that needs encrypting (but as I said I don't have a test environment in front of me so I could be wrong). — Scott Chamberlain
– Scott Chamberlain, Commented Dec 3, 2014 at 18:52
@ScottChamberlain I added the contents of the procedure to the question so you could see it. I understand that the stored procedure doesn't need to be encrypted, but I have a requirement stating that it must be encrypted. I could push back, and the customer might make exceptions for me, but that isn't the point of the question. — Rainbolt
– Rainbolt, Commented Dec 3, 2014 at 20:04
@idstam I didn't create the procedure, so I have no way of specifying that it should be created with encryption. ASP.NET created the procedure automatically. In other words, if you run the code snippet at the top of the question, you will suddenly see a procedure with a similar name appear in your database. — Rainbolt
– Rainbolt, Commented Dec 17, 2014 at 14:51
Is it acceptable that the procedure is created unencrypted, then immediately encrypted so only people with profiler access could see its definition? If so, you can do it with a DDL trigger. — Jeroen Mostert
– Jeroen Mostert, Commented Dec 17, 2014 at 15:02

Solomon Rutzky · Accepted Answer · 2014-12-18 19:39:48Z

4

Create a DDL trigger that checks if a procedure with a name like "SqlQueryNotificationStoredProcedure-" is being created, and if so, immediately alter it WITH ENCRYPTION instead:

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;

Disclaimer: not tested against an actual dependency notification, but the basic idea is sound. It's quite brittle because it depends on the exact form of the procedure, of course -- making it more robust is possible, but tedious.

edited Dec 18, 2014 at 19:39

Solomon Rutzky

49.3k11 gold badges141 silver badges185 bronze badges

answered Dec 17, 2014 at 15:10

Jeroen Mostert

29k2 gold badges60 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Rainbolt Over a year ago

This is really neat. I had no idea that you could alter SQL while it was in transit. I would consider removing the @Name LIKE 'SqlQueryNotificationStoredProcedure-%' condition because it is redundant. I'll accept this answer when I have time to test.

Jeroen Mostert Over a year ago

@Rainbolt: this doesn't exactly alter it while in transit -- the stored procedure really is created first, then a separate ALTER is issued to change the stored procedure we just created. The net effect is the same, but the difference is observable for things like auditing, replication and profile traces.

Jeroen Mostert Over a year ago

@Rainbolt: the check on the name is not exactly redundant, because we're doing textual replacement on a procedure that we're assuming to have a specific form. It's always a good idea to make that as robust as possible (that is: be as specific as possible). In particular, this trigger fails if the stored procedure contains "AS\nBEGIN" anywhere else in the text, which is improbable but not impossible. But maybe I'm just overly conservative with these things. :-)

Rainbolt Over a year ago

Ah ok. So an outside observer would actually be able to see the procedure in its raw, unencrypted form for a short time. That's still fine, but probably good to know. Thank you for the lesson!

Solomon Rutzky Over a year ago

@Rainbolt Not likely that an outside observer would see the raw definition as this trigger, like DML triggers, is within a transaction that has not yet committed. Trying to select from sys.sql_modules by adding WITH (NOLOCK) won't help as sys.sql_modules is really just a view. As Jeroen mentioned, a SQL Profiler trace would be able to see it, though I am not sure replication would pick it up since it hasn't committed yet.

Collectives™ on Stack Overflow

Encrypt the stored procedure created by SqlDependency

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related