Click here to Skip to main content
12,356,612 members (67,392 online)
Click here to Skip to main content
Add your own
alternative version

Stats

54.3K views
646 downloads
56 bookmarked
Posted

Encrypting all the Stored Procedures of a Database

, 18 Nov 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.

You may also be interested in...

Comments and Discussions

 
GeneralMy vote of 5 Pin
Umesh AP27-Apr-16 2:32
memberUmesh AP27-Apr-16 2:32 
QuestionEncrypting all the Stored Procedures of a Database Pin
Fatenight2-Jun-15 20:51
memberFatenight2-Jun-15 20:51 
AnswerRe: Encrypting all the Stored Procedures of a Database Pin
qadirv8-Jun-15 5:01
memberqadirv8-Jun-15 5:01 
QuestionCan you provide an example Pin
Member 1168373711-May-15 23:12
memberMember 1168373711-May-15 23:12 
AnswerRe: Can you provide an example Pin
qadirv11-May-15 23:17
memberqadirv11-May-15 23:17 
QuestionNot sure how to run this program Pin
Greg@AP14-Mar-15 5:54
memberGreg@AP14-Mar-15 5:54 
AnswerRe: Not sure how to run this program Pin
qadirv16-Mar-15 2:32
memberqadirv16-Mar-15 2:32 
GeneralGood One Pin
Abhishek Kumar Goswami19-Nov-14 4:45
professionalAbhishek Kumar Goswami19-Nov-14 4:45 
SuggestionA grt one Pin
Arkadeep De18-Nov-14 23:26
professionalArkadeep De18-Nov-14 23:26 
GeneralMy vote of 5 Pin
Humayun Kabir Mamun18-Nov-14 18:53
memberHumayun Kabir Mamun18-Nov-14 18:53 
Question+5 Pin
Sibeesh Venu4-Aug-14 18:07
professionalSibeesh Venu4-Aug-14 18:07 
GeneralMy vote of 5 Pin
Sibeesh Venu4-Aug-14 18:06
professionalSibeesh Venu4-Aug-14 18:06 
QuestionGreat Article, But Pin
TcJoshJohnson1-Aug-14 7:17
memberTcJoshJohnson1-Aug-14 7:17 
GeneralRe: Great Article, But Pin
PIEBALDconsult1-Aug-14 8:05
memberPIEBALDconsult1-Aug-14 8:05 
GeneralRe: Great Article, But Pin
qadirv3-Aug-14 21:31
memberqadirv3-Aug-14 21:31 
GeneralRe: Great Article, But Pin
PIEBALDconsult4-Aug-14 4:34
memberPIEBALDconsult4-Aug-14 4:34 
GeneralRe: Great Article, But Pin
qadirv4-Aug-14 4:54
memberqadirv4-Aug-14 4:54 
GeneralRe: Great Article, But Pin
PIEBALDconsult4-Aug-14 5:03
memberPIEBALDconsult4-Aug-14 5:03 
GeneralRe: Great Article, But Pin
qadirv4-Aug-14 22:19
memberqadirv4-Aug-14 22:19 
QuestionIs an Excutable available? Pin
Member 785663930-Jul-14 13:46
memberMember 785663930-Jul-14 13:46 
AnswerRe: Is an Excutable available? Pin
qadirv31-Jul-14 23:40
memberqadirv31-Jul-14 23:40 
GeneralMy vote of 5 Pin
Hernán Hegykozi22-May-13 10:18
memberHernán Hegykozi22-May-13 10:18 
GeneralRe: My vote of 5 Pin
qadirv23-May-13 20:08
memberqadirv23-May-13 20:08 
GeneralReason for my vote of 5 Great Dude..!!!! Nicely Worked...Tha... Pin
Member 78893633-Dec-11 0:05
memberMember 78893633-Dec-11 0:05 
GeneralReason for my vote of 5 Beautiful! After making the referenc... Pin
Marcio_Coelho25-Aug-11 8:15
memberMarcio_Coelho25-Aug-11 8:15 
GeneralReason for my vote of 4 It seems a great artcile. Only missi... Pin
Marcio_Coelho25-Aug-11 8:04
memberMarcio_Coelho25-Aug-11 8:04 
GeneralWe open each procedure and paste 'WITH ENCRYPTION' in each a... Pin
Bana52024-Aug-11 14:14
memberBana52024-Aug-11 14:14 
GeneralReason for my vote of 5 Nice work dude... Very helpful...... Pin
Pravin Patil, Mumbai17-Aug-11 5:26
memberPravin Patil, Mumbai17-Aug-11 5:26 
GeneralRe: Thank You!! Pin
qadirv21-Aug-11 20:15
memberqadirv21-Aug-11 20:15 
Generalvery helpful... thank you for your work on this. -- Regardin... Pin
blubcode16-Aug-11 0:59
memberblubcode16-Aug-11 0:59 
GeneralRe: Hey, First of all I'm glad you liked it. This was my first ... Pin
qadirv16-Aug-11 10:01
memberqadirv16-Aug-11 10:01 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    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 | Terms of Use | Mobile
Web02 | 2.8.160621.1 | Last Updated 18 Nov 2014
Article Copyright 2011 by qadirv
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid