SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


To Encrypt all stored procedures in a database.


To Encrypt all stored procedures in a database.

Author
Message
Saravanan T
Saravanan T
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1010 Visits: 801
Hi all,

To Encrypt all stored procedures in a database.


Regards,
Saravanan

Regards,
Saravanan
Adi Cohn
Adi Cohn
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10355 Visits: 6596
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/
jackuline.savarimuthu
jackuline.savarimuthu
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (285K reputation)SSC Guru (285K reputation)SSC Guru (285K reputation)SSC Guru (285K reputation)SSC Guru (285K reputation)SSC Guru (285K reputation)SSC Guru (285K reputation)SSC Guru (285K reputation)

Group: General Forum Members
Points: 285086 Visits: 46636
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, MVP, M.Sc (Comp Sci)
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


ze-608185
ze-608185
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 28
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.
EdVassie
EdVassie
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17850 Visits: 3914
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 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist. - Archbishop Hélder Câmara
Steve Jones
Steve Jones
SSC Guru
SSC Guru (178K reputation)SSC Guru (178K reputation)SSC Guru (178K reputation)SSC Guru (178K reputation)SSC Guru (178K reputation)SSC Guru (178K reputation)SSC Guru (178K reputation)SSC Guru (178K reputation)

Group: Administrators
Points: 178070 Visits: 19497
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
My Blog: www.voiceofthedba.com
Rajat Jaiswal-337252
Rajat Jaiswal-337252
SSC-Addicted
SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)

Group: General Forum Members
Points: 497 Visits: 398
What is the best way for encrypt ?
as the gentel men said above really with normal encrypt option stored procedure is not secure enough?
EdVassie
EdVassie
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17850 Visits: 3914
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 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist. - Archbishop Hélder Câmara
Sean Lange
Sean Lange
SSC Guru
SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)

Group: General Forum Members
Points: 78698 Visits: 18035
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. :-P

_______________________________________________________________

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 Modens 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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search