Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

To Encrypt all stored procedures in a database. Expand / Collapse
Author
Message
Posted Thursday, January 7, 2010 4:25 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, September 29, 2014 3:05 AM
Points: 500, Visits: 741
Hi all,

To Encrypt all stored procedures in a database.


Regards,
Saravanan


Regards,
Saravanan
Post #843425
Posted Thursday, January 7, 2010 4:32 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:33 AM
Points: 2,128, Visits: 5,559
Saravanan T (1/7/2010)
Hi all,

To Encrypt all stored procedures in a database.


Regards,
Saravanan


Is there any question in this statement? If there is can you try to explain what you are asking? How to encrypt stored procedure? Should you encrypt stored procedures? Why whould anyone encrypt a stored procedure?

Adi


--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #843429
Posted Thursday, September 23, 2010 10:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 23, 2010 10:47 PM
Points: 1, Visits: 0
Hi All,
I need to know how to encrypt all stored procedures at a time, instead of encrypting one by one. Is there any qury/shotcuts to encrypt all stored procedures of a database together.
Thanks.
Post #992473
Posted Friday, September 24, 2010 4:11 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:23 AM
Points: 40,438, Visits: 36,893
Nope. Need to run an ALTER for each one.

Do note that the encryption is not hard to reverse. At best it'll slow someone down, nothing more.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #992605
Posted Thursday, December 2, 2010 7:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, May 18, 2014 10:52 AM
Points: 1, Visits: 27
A VB program to do just that:
http://blogs.msdn.com/b/mosharaf/archive/2005/09/11/encryptstoredprocedures.aspx
with link to sources at the end of the article.
Post #1029284
Posted Monday, December 6, 2010 6:41 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, November 14, 2014 8:26 AM
Points: 2,894, Visits: 3,278
The stored procedure 'encryption' process is just obfuscation. It uses a standard algorithm and is relatively easy to reverse. If you Google 'sql server stored procedure decrypt' the top two entries at the time of this post give you methods to decrypt your procedures.

You need to think about what you are trying to guard against. If you are trying to safeguard your server against a determined attack, then encrypting the stored procedures should be near the bottom of the list - if a determined attacker has has got enough access to look at your stored procedures then almost certainly they will have the tools to undo the obfuscation.

If you just want to let someone with DBA access know the procedure contains something sensitive that should not be looked at unless really necessary, then 'encryption' is a reasonable way to solve this. Maybe not the best way, but a reasonable way.

An important thing to remember is that if 1% of your procedures are encrypted, then most people will not bother with getting a decrypt tool. If 100% of procedures are encrypted then all DBAs will need a decryption tool as part of their troubleshooting toolkit.


Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 18 October 2014: now over 31,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #1030568
Posted Monday, December 6, 2010 8:04 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:16 PM
Points: 31,284, Visits: 15,748
I completely agree with Ed here. Not really a useful tool, IMHO.

If this is because you are selling software, you would be better off writing a "do not touch" clause into your license and explaining this to your clients. Most software out there doesn't bother to encrypt stored procedures because there isn't a lot of value. Even if you do, your clients can decrypt them, and you would need to decrypt them yourself for troubleshooting, just to be sure the client hasn't altered them.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1030637
Posted Monday, March 7, 2011 6:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 6:24 AM
Points: 111, Visits: 335
What is the best way for encrypt ?
as the gentel men said above really with normal encrypt option stored procedure is not secure enough?
Post #1074083
Posted Monday, March 7, 2011 6:28 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, November 14, 2014 8:26 AM
Points: 2,894, Visits: 3,278
There is a similar thread active on SSWUG.org, and Marc has given an excellent response on 4 March:

I’m assuming the person who posted the question has built an application and database that is to be installed outside the domain of the application vendor, at a customer’s site.
Even in this scenario, where the database is installed at a customer’s location, I think it would be a rare case, if ever, where you would want to completely prevent access to your database. It can negatively affect customer relationships in several ways and attempts to use security features for a purpose other than a true concern for the security of the customer’s data.
Do you not want your customers to see your bad database design? Are you trying to ensure that you are the only one who can help them when things run slowly? Are you trying to prevent your customers from writing their own queries or reports? Do you think they’re not smart enough to write good SQL? (Maybe they’re not, but is restricting database access really how you would solve that problem?) You don’t want to insult your customers; you want to empower them and work with them, ultimately improving their success.
Except for the tiniest of databases or those of the simplest design, there is no scenario where you want to cut off complete access to a database, because every database requires maintenance and if it’s located at a customer’s site, then they’ll need access to the database for maintenance. This can be done by the SA account, but should also be allowed by the dbo – the person(s) responsible for maintaining that database – under least-privilege best practice.
If your application is smart enough to replace a DBA by monitoring and performing necessary maintenance on the database, then you’re smart enough to make and use your own proprietary database management software.
If the real concern is about customers reading your “proprietary” data and not about them seeing the design of the database, then consider using password-protected encryption within your application – read and write only encrypted data.
If the concern is about reading the logic (the SQL statements) within a stored procedure or user-defined function, then consider using the CLR equivalent and obfuscate the .NET code. But that’s only make it difficult, not impossible, to read.



Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 18 October 2014: now over 31,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #1074105
Posted Monday, March 7, 2011 12:31 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:10 AM
Points: 13,230, Visits: 12,709
Another point about maintaining a system with encrypted stored procedures. If you do not keep the original script in source control you run the risk of not knowing what the actual content of the stored proc is. I encountered this a few years where the client had literally thousands of stored procs and every single one of them was encrypted to "protect" it internally. The client was continually reintroducing resolved bugs because of the inability to know what the proc was doing. Most the time whatever logic is really not groundbreaking, it is just retrieving data (or maybe some manipulation). I have also encountered some third party apps where their stored procs were encrypted and after decrypting I assumed they encrypted them to cover up the fact that they had no business writing a stored procedure in the first place.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1074400
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse