January 7, 2010 at 4:25 am
Hi all,
To Encrypt all stored procedures in a database.
Regards,
Saravanan
Regards,
Saravanan
January 7, 2010 at 4:32 am
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/
September 23, 2010 at 10:50 pm
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.
September 24, 2010 at 4:11 am
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
December 2, 2010 at 7:55 am
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.
December 6, 2010 at 6:41 am
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: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
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
December 6, 2010 at 8:04 am
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.
March 7, 2011 at 6:03 am
What is the best way for encrypt ?
as the gentel men said above really with normal encrypt option stored procedure is not secure enough?
March 7, 2011 at 6:28 am
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: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
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
March 7, 2011 at 12:31 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 7, 2011 at 3:01 pm
Sean Lange (3/7/2011)
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. 😛
I had a company that did this, and had different versions checked out of source control on different developer's machines. This was a nightmare of a project to decrypt the code and fix source control.
March 7, 2011 at 3:03 pm
Steve Jones - SSC Editor (3/7/2011)
Sean Lange (3/7/2011)
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. 😛I had a company that did this, and had different versions checked out of source control on different developer's machines. This was a nightmare of a project to decrypt the code and fix source control.
Did we do work at the same place??? Sounds exactly like what I had to deal with.
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply