Click here to Skip to main content
Click here to Skip to main content

Encrypting all the Stored Procedures of a Database

, 1 Aug 2014 CPOL
Rate this:
Please Sign up or sign in to vote.
Quick C# console application to encrypt all your database stored procedures

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

qadirv
Software Developer Gravitas India
India India
Completed BE from Mumbai University in 2008.
 
Loves travelling and discovering new places.

Comments and Discussions

 
Question+5 PinprofessionalSibeesh Venu4-Aug-14 18:07 
GeneralMy vote of 5 PinprofessionalSibeesh Venu4-Aug-14 18:06 
QuestionGreat Article, But PinmemberTcJoshJohnson1-Aug-14 7:17 
GeneralRe: Great Article, But PinmemberPIEBALDconsult1-Aug-14 8:05 
GeneralRe: Great Article, But Pinmemberqadirv3-Aug-14 21:31 
GeneralRe: Great Article, But PinmemberPIEBALDconsult4-Aug-14 4:34 
GeneralRe: Great Article, But Pinmemberqadirv4-Aug-14 4:54 
GeneralRe: Great Article, But PinmemberPIEBALDconsult4-Aug-14 5:03 
GeneralRe: Great Article, But Pinmemberqadirv4-Aug-14 22:19 
QuestionIs an Excutable available? PinmemberMember 785663930-Jul-14 13:46 
AnswerRe: Is an Excutable available? Pinmemberqadirv31-Jul-14 23:40 
GeneralMy vote of 5 PinmemberHernán Hegykozi22-May-13 10:18 
GeneralRe: My vote of 5 Pinmemberqadirv23-May-13 20:08 
GeneralReason for my vote of 5 Great Dude..!!!! Nicely Worked...Tha... PinmemberMember 78893633-Dec-11 0:05 
GeneralReason for my vote of 5 Beautiful! After making the referenc... PinmemberMarcio_Coelho25-Aug-11 8:15 
GeneralReason for my vote of 4 It seems a great artcile. Only missi... PinmemberMarcio_Coelho25-Aug-11 8:04 
GeneralWe open each procedure and paste 'WITH ENCRYPTION' in each a... PinmemberBana52024-Aug-11 14:14 
GeneralReason for my vote of 5 Nice work dude... Very helpful...... PinmemberPravin Patil, Mumbai17-Aug-11 5:26 
GeneralRe: Thank You!! Pinmemberqadirv21-Aug-11 20:15 
Generalvery helpful... thank you for your work on this. -- Regardin... Pinmemberblubcode16-Aug-11 0:59 
GeneralRe: Hey, First of all I'm glad you liked it. This was my first ... Pinmemberqadirv16-Aug-11 10:01 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.141022.1 | Last Updated 1 Aug 2014
Article Copyright 2011 by qadirv
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid