Wednesday, 30 March 2011

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 –

Making Use of WITH ENCRYPTION option

The normal way of creating a stored procedure is -

CREATEPROCEDURE dbo.SimpleStoredProc

AS

BEGIN

SELECT'Some t-sql statements'

END

Creating the stored procedure using the WITH EXCRYPTION option is -

CREATEPROCEDURE dbo.EncryptedStoredProc

WITHENCRYPTION

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 -

EXECsp_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

Saturday, 12 March 2011

The most awaited feature has arrived - "Table-Valued parameters in SQL Server 2008". Here, I would discuss on how to use this new feature with .net.

Problem StatementBefore to the introduction of table-valued parameters in SQL Server 2008, the options for passing data from multiple rows of a DataTable to a stored procedure or a parameterized SQL command were limited.Some of the possible options for a developer until now were:1. Make multiple parameters, with one parameter representing one column of the DataTable2. Create XML string & then parse it in the SP3. Create a delimiter separated string & then parse it in the SP

Also, the above stated options had to be repeated for each row....hhuuuuuuuuhh.......just imagine the round trips to the server for large DataTables or the complex logic to be implemented for parsing the parameters.

Limitations of Table-Valued parameter1. User-defined functions do not support Table valued parameters.2. Table-valued parameters can only be indexed to support UNIQUE or PRIMARY KEY constraints.3. SQL Server does not maintain statistics on table-valued parameters.4. Table-valued parameters are read-only in Transact-SQL code. You cannot update the column values in the rows of a table-valued parameter and youcannot insert or delete rows. To modify the data that is passed to a stored procedure or parameterized statement in table-valued parameter, you mustinsert the data into a temporary table or into a table variable.5. You cannot use ALTER TABLE statements to modify the design of table-valued parameters.6. If you are using Entity Framework 1.0, DBType enum does not support Structured type.

As a work around, create a SQLParameter & then typecast itinto DbParameter.

Search This Blog

About Me

Experience of more than 11+ years in developing & designing Client-Server based applications, smart client applications, web portals and small utility applications using Microsoft technologies.
Strong in designing the Application Architecture, providing out of box solutions & a very good trouble shooter.