Introduction

Let us discuss the "With Encrytion" statement in SQL Server. When we publich an application that is based on a SQL databse, we have to append our database files into a project. What I mean is, we have to include MDF and sometimes LDF files with projects. However, we know that with the existance of this file every one can attach them with management studio and see our database objects.

Fortunately, there are some solutions to hide our database sturctures from end user. For example we can name tables and columns with a special format such as numbers, so that users cannot guess what they mean, and save content of cells in binary format, but really all of this solutions reduce performance.

Let us say that Encryption in SQL Server 2008 is inherently solved. You can search Microsoft for this article for more information about encryption: quot; Database Encryption in SQL Server 2008 Enterprise Edition".

OK. So we can encrypt stored procedures and functions and also views with the "With Encryption" statement. Stored procedures and functions can be encrypted so that nobody can see their source code. You just need to create or alter the function using the "With Encryption" the option. When you create a stored procedure using With Encryption, the code of the stored procedure is encrypted and then saved in the database.

SQL Server will be able to use the encrypted version of the source code to recompile the stored procedure when needed, but none of the users will be able to obtain it. In reality, you should not count on SQL Server encryption to protect your code.

It is possible to find on the Internet the means to defeat SQL Server encryption, but sometimes we have to use encrytion because there is no copyright protection in some countries. Keep in mind that you will not be able to change a stored procedure if you create the procedure using With Encryption. You must get backup somewhere else .

Background

Say in our company we had a project that involves more than 500 stored procedures and functions. Every time we want to publish a version of that, we had to encrypt all procs and functions handily. SQL Server does not have any procedures or any tools to encrypt them one at a time or if it does, at least I do not know it.

I Googled for this problem and fortunately I found an article from " Chris Morton" that really helped me, but nevertheless, still had some problems.

Some of its problems;

It gets the source code of procedures directly from sys.syscomments. The source code of the stored procedure is recorded in the sys.syscomments system table, unless the stored procedure is encrypted. The source code is stored in a field named 'text.'

The data type of this field is nvarchar(4000). Fortunately, this does not mean that stored procedures are limited to 4,000 characters. If the stored procedure is larger than 4,000 characters, SQL Server allocates additional records with an incremented 'colid' field.

So when the source code of procedures was more than 4000 an error occurred for the next record that contains the next part of the procedure. To solve this problem i used the SQL Server function object_definition(object_id) returns full source code of procedure.
I said that all procs save in sys.syscomments exept encrypted procs, but it does not include this condition in the main select query that returns the list of procedures, so after executing it the next time when it is executed it got an error.
```
//
// SELECT [name], object_definition(object_id) as [text] from sys.objects where [
//      type]='P' and object_definition(object_id) is not null 
//
```
Its strategy to encrypt proc and function was as follows:
For each procs in sys.syscomments firstly it was to get the header part of procs, for getting the header part of proc

It parses from the Create Proc statement to the Begin statement of proc and after getting the index of last As statement in the header part, replace the with encryption + char(13) + As with As statement and alter the final proc again.

But we know that some procs do not include a Begin End statement, so what we should do?

To solve this problem I perform an operation based on the As statement, because every proc or function or view has to start with As statement.

Using the Code

I have included source code. I includes three SQL script files that contain functions and proc that I used them to encrypt. You can use them directly to do encryption without running the application. But I developed a small VB.NET application to do it more easily.

Because you cannot use the source code of procs after encryption, the application backs them up them in the table that you set its name with the application.

//
// SELECT [name], object_definition(object_id) as [text] from sys.objects where [type]=
//     'P' and object_definition(object_id) is not null 
//

SQL Server Procedures And Functions Encryptions