BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

Obfuscating SQL Server Code

In some deployment scenarios, it is necessary to obfuscate the code for stored procedures, views, triggers and similar elements of a SQL Server database. This is useful to protect intellectual property or to hide implementation details.

WITH ENCRYPTION Clause

When you deploy a software solution that contains code held in a SQL Server database, it can be important to obfuscate, or obscure, the code to prevent copyright infringement or intellectual property theft, or to prevent unauthorised changes being made to the code. Transact-SQL provides a means to achieve this with the "WITH ENCRYPTION" option. When applied to a stored procedure, trigger, user-defined function or view, the database object is encrypted. Encrypted objects cannot be scripted to see their contents and are not published by replication.

The WITH ENCRYPTION clause does not provide perfect obfuscation of code. If a debugger is attached to a process that calls an encrypted procedure, the debugger can be used to see the original code. It is also possible to obtain this information by directly accessing the database files or by connecting to the SQL Server instance using a dedicated administrator connection. However, the obfuscation still provides a beneficial first line of defence.

Example Encrypted Stored Procedure

When encrypting database objects, the WITH ENCRYPTION clause is used immediately before the "AS" keyword that indicates the start of the code. We can demonstrate this by executing the following script, which creates a new obfuscated stored procedure.

CREATE PROCEDURE SampleProcedure
WITH ENCRYPTION
AS
BEGIN
    PRINT 'Hello, world'
END

Once created, the stored procedure can be called as normal. However, if you attempt to generate a script to create or alter the procedure, the following error is generated.

Script failed for StoredProcedure 'dbo.SampleProcedure'.
8 April 2010