Encrypting all the Stored Procedures of a Database

• Download Executable - 2.7 KB

Introduction

If you ever decide that you need to protect your SQL Stored Procedures, and thought encrypting was a good idea, BE VERY CAREFUL!!!

Encrypting database stored procedures SHOULD NOT be done without having backup files or some sort of source control for the stored procedures. The reason I say this is because, once they are encrypted, there is no turning around. (Yes, there are third party tools that will decrypt your code, but why go through that trouble.)

This trick is something I developed because my company needed to host the application on a different server, and we were concerned about our code being compromised. So, to deliver the database, we decided to encrypt all our stored procedures. Having over a hundred procedures written, I didn't want to open each procedure and paste 'WITH ENCRYPTION' in each and every stored procedure. (For those of you who do not know how to encrypt, refer to How Do I Protect My Stored Procedure Code[^]). So I decided to make my own little C# application that did the same.

This application is a console application made using Visual Studio 2005 and SQL server 2005. The input parameters are database name, server address, database username and password. Once you are able to provide these details, you are ready to have all your stored procedures encrypted.

I have put the code of my application here as is. For this code to work, you will need to add an "Microsft.SQlserver.SMO" reference to the application, so that the classes such as "Database" and "StoredProcedure" are accessible.

BEFORE YOU DO THIS, TAKE A BACKUP!!!!!!!

//Connect to the local, default instance of SQL Server. 
string DB = "";
ServerConnection objServerCOnnection = new ServerConnection();
objServerCOnnection.LoginSecure = false;
Console.WriteLine("Enter name or IP Address of the Database Server.");            
objServerCOnnection.ServerInstance = Console.ReadLine();
Console.WriteLine("Enter name of the Database");
DB = Console.ReadLine();
Console.WriteLine("Enter user id");
objServerCOnnection.Login = Console.ReadLine();
Console.WriteLine("Enter Password");
objServerCOnnection.Password = Console.ReadLine();
Console.WriteLine(" ");
Server srv = new Server();
try // Check to see if server connection details are ok.
{
   srv = new Server(objServerCOnnection);
   if (srv == null)
   {
      Console.WriteLine("Server details entered are wrong,"
         + " Please restart the application");
      Console.ReadLine();
      System.Environment.Exit(System.Environment.ExitCode);
   }
}
catch
{
   Console.WriteLine("Server details entered are wrong,"
      + " Please restart the application");
   Console.ReadLine();
   System.Environment.Exit(System.Environment.ExitCode);
}
Database db = new Database();
try // Check to see if database exists.
{
   db = srv.Databases[DB];
   if (db == null)
   {
      Console.WriteLine("Database does not exist on the current server,"
         + " Please restart the application");
      Console.ReadLine();
      System.Environment.Exit(System.Environment.ExitCode);
   }
}
catch
{
   Console.WriteLine("Database does not exist on the current server,"
      + " Please restart the application");
   Console.ReadLine();
   System.Environment.Exit(System.Environment.ExitCode);
}
string allSP = "";
                
for (int i = 0; i < db.StoredProcedures.Count; i++)
{
   //Define a StoredProcedure object variable by supplying the parent database 
   //and name arguments in the constructor. 
   StoredProcedure sp;
   sp = new StoredProcedure();
   sp = db.StoredProcedures[i];
   if (!sp.IsSystemObject)// Exclude System stored procedures
   {
      if (!sp.IsEncrypted) // Exclude already encrypted stored procedures
      {
         string text = "";// = sp.TextBody;
         sp.TextMode = false;
         sp.IsEncrypted = true;
         sp.TextMode = true;
         sp.Alter();
 
         Console.WriteLine(sp.Name); // display name of the encrypted SP.                        
         sp = null;
         text = null;
      }
   }
}

P.S. Please do leave some feedback about this code. Positive, negative... doesn't matter, and if it helps you, you're welcome!!

This is my first post on CodeProject.