Introduction
We have always
been wondering on how to protect/secure our t-sql code written in Stored
Procedures and UDF in shared hosting environment from other people who might
tweak our work. Here, I would discuss a few options I know to serve purpose.
As per my
knowledge, MS SQL Server doesn’t help much with it. The possible alternatives
which could be tried are –
1.
Making Use of WITH ENCRYPTION option
The normal way of
creating a stored procedure is -
CREATE PROCEDURE
dbo.SimpleStoredProc
AS
BEGIN
SELECT 'Some t-sql statements'
END
Creating the stored
procedure using the WITH EXCRYPTION option is -
CREATE PROCEDURE
dbo.EncryptedStoredProc
WITH ENCRYPTION
AS
BEGIN
SELECT 'Some t-sql
statements'
END
However, please make
sure that the code of the SP is backed up as a separate script file for future
references.
Now, when we try to
use the following command to get the details of the SP -
EXEC sp_helptext 'dbo.EncryptedStoredProc'
We get the following
error message –
The object comments have been encrypted.
And when we try to
open this encrypted SP using SSMS, we get the following error message –
Microsoft SQL-DMO
Error 20585: [SQL-DMO]
/******
Encrypted object is not transferable,
and script can not be generated.
******/
Pros
a. Once
encrypted, it is not possible to decrypt using SQL Server commands.
Cons
a.
We as developers will always have to keep a copy
of the SP/UDF as a script for our reference or future updates.
b.
One way I know using which this approach could
be defeated is by running SQL Profiler while
executing the stored procedure.
c.
Another way that users might use to get at your
encrypted code is by using readily available code (if any) that allows you to
break SQL Server's relatively trivial encryption algorithm.
2.
Stop creating Stored Procedures and Functions
The very first
question which comes to our mind is – if we stop creating them, then what is
the alternative. We might use an of the below stated technique –
a.
Make use of Parameterized
queries directly in our source code. As the code is deployed in the form of an
assembly, it becomes hard to directly get into our query logic. However,
de-compilation is always possible and to tackle it we can always obfuscate our
assemblies. To learn more about obfuscation please refer http://msdn.microsoft.com/en-us/magazine/cc164058.aspx
b.
Secondly, we can make use of sql-clr functions wherever possible.
This would again help us in hiding our logic from the preying eyes. Please
refer http://msdn.microsoft.com/en-us/library/ms345136(v=sql.90).aspx
for more details.
Recommendation
One should not make use of WITH ENCRYPTION
option unless it’s the last option and have a thorough knowledge of its
consequences after implementation. However, I would strongly recommend NOT TO
USE it.
