September 22, 2015 at 1:45 pm
Hi,
I am a SQL DBA for the middle size Company in Florida.
We are a software Company running SQL Server 2008 and 2012 (mostly 2012) on the backend and .NET application on the frontend.
Our Management Team is very concerned about the security of our intellectual property.
So it was suggested (not by DBA's) to encrypt the objects in the Company's SQL Server databases (including stored procedures, functions, views, etc....)
I personally do not thing, that this is a good idea, because of many reasons, i.e.:
1. I do not see how we would be able to use the Performance Monitoring Tools (internal and external).
2. I'm not sure how the Backup and Restore processes would be affected since we are utilizing the external tools for these purposes.
3. I can see the option of TFS Server failure and a risk of loosing the latest scripts.
4. I would think that there should be a performance hit to the application due to the encrypted values.
5. I also can see that with some effort the experience folks can decrypt the scripts if needed.
6. And many other small problems, that I'm not mentioning here.
That's why I wanted to hear the opinion of the SQL Server community.
I would be very interested to know if this strategy has been ever used in the Corporate or any other environments, and what are the cons and pros.
Your attention to this question is greatly appreciated.
Thank you.
Alex
September 22, 2015 at 1:58 pm
because you said intellectual property, i'm thinking that your database is given to your clients, and a different DBA than yourself administers that database. if you control the db, you'd control who has access to see those definitions.
in SQL Encrypting procs, views and functions is simply obfuscation. it's still possible for the DBA to decrypt them and get their original text. it keeps casual users and folks without sysadmin access from getting to them if access is granted accidentally though, so it certainly might a value.
for encrypting those items,i'd just shrug my shoulders and tell em, you want, you got it.
the execution plans of those procs, views and functions /objects are what is cached not the text, so there's no relevant performance impact when they are used by queries.
you get performance impacts when you encrypt /decrypt data in the actual columns, however, but you also get the significant advantage that the data is unusable by anyone without the decryption mechanism.
if the decryption happens in the SQL layer, ie with EncryptByCertificate or something, the DBA would still be able to access the data.
if the encryption is handled in an application, then pretty much only developers with the source code could do anything , and mass updates to the data have to go through a business layer
Lowell
September 22, 2015 at 5:12 pm
Hi Lowell,
I'm a DBA who administer all the Company databases. By 'intellectual property' I meant the scripts that are written in the databases.
The problem is that our Client requested a 'sysadmin' role in the SQL Server and it looks like we are going to give it to them.
So, we wanted to protect our scripts one way or another.
The idea to encrypt all the SPs came from the Developers (actually from data architects).
They proposed to encrypt the SPs using the 'WITH ENCRYPTION' clause. I believe that utilizing the 'EncryptByCertificate' utility would be a pain for the databases of our size.
We do have the SPs stored in TFS as well as in SSMS. So, they were proposing, if we need to do any changes, retrieve the source code from the TFS, unencrypt the SP by removing the 'WITH ENCRYPTION' clause; make changes, and then encrypt it back.
I also believe that the 3rd party Performance Monitoring and Data Modeling tools still use the scripts for their analyses.
My biggest question was, if any Company are using the overall encryption of all the database scripts and what are advantages and disadvantages.
Can anybody please answer this question.
Thank you in advance.
Alex
September 22, 2015 at 9:11 pm
The obfuscation obtained by using the "WITH ENCRYPTION" clause on T-SQL objects is easily bypassed. Just search google for how to do it. Heck, 3rd party tools will even show you the decrypted text. Since it's so easy to bypass / circumvent, I'd be of the opinion of why bother? But, like Lowell alluded to, I would not fight it, and just go ahead with it. Just tell them that it's easily bypassed.
If you really want to make it hard for clients, maybe you can create SQLCLR routines, so that you only need to supply a DLL and not actual code.
Performance monitoring tools mostly use DMVs. Only those that return the text of the objects (plan cache, etc.) would have an issue - those would return a null for those. Everything else would be okay. Of course, if the tool has the proper rights, then even those will return the proper values. Data Modeling tools would also fall into this category.
As far as pros and cons on using WITH ENCRYPTION:
pros - it will slow down folks from reading the code.
cons - because it is easily circumvented.
pro: it will give your company some level of comfort
con: this is a false feeling since it is so easily circumvented.
If you really don't want the clients to be able to see it, then don't give them the access to do so.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 23, 2015 at 3:32 am
We do a few things to our databases that are released to Clients (i.e. our database is a 3rd party APP on their servers)
We keep all Sproc, View, Trigger, etc. code in separate, individual, files. We never change an SProc by RightClick-Edit in SSMS. (Number of reasons for this, but one of them is because we then store the source code in a Revision Control System. Absolutely no way that I would give that up! but there are no doubt other ways to achieve it, other than individual files per object)
SProcs etc. that we want to encrypt have
/* WITH ENCRYPTION */
in the code.
We build "release scripts" by concatenating all relevant Sproc etc. files into a single "release script"
We do a Find & Replace for /* WITH ENCRYPTION */ to "activate" encryption on the relevant objects.
We have an inhouse-built minifier (compressor) which strips out white-space and comments. It leaves behind certain flavours of comment - e.g. our copyright notice. (I have seen SQL minifiers that also obfuscate local variable names etc. so one could do some of that too - I think there is one such at poorsql.com ??)
(Aside: I don't know if removing whitespace improves performance of anything, pe se. It certainly makes the monster release script run faster, particularly over the WAN to a remote server, and perhaps the compressed code uses less buffer space when SProcs etc. are executed)
That gets us slightly obfuscated code, with the additional hassle that the user has to de-crypt the SProcs etc. to see the original source.
Our license agreement prohibits decryption and reverse-engineering of our code. Its only a piece of paper to wave, but if we hear that a client is doing work with some other vendor who will have access to the server(s) we do take the opportunity to remind them of the threat to their data, and our code.
We know its a thin layer of protection, but we think it is enough to foil a casual attempt to get a leg-up from looking at our (brilliantly crafted, of course 🙂 ) intellectual property.
A determined attack would succeed and then require us deciding to have to engage expensive lawyers and drag our clients' names through the mud - not sure that one would ever fly!!
Having the source code in individual files, with the WITH ENCRYPTION commented out, means that on in-house server, in DEV etc., we are running with "plain text" code so profiling etc. is "as normal"
September 23, 2015 at 10:09 am
With the obfuscation easily bypassed (SQL Compare Pro[/url] does it), what is the protection? What do you think clients will do with the intellectual property? In fact, what code could you write to transform data that someone couldn't duplicate?
IMHO, this is silly. The encrpytion of objects provides minimal deterrent to someone in finding the code. If they want to, they'll find it. If they don't, then why bother? However this does ensure that it is difficult to determine what version of your code is deployed on which client's system.
You have copyright, and likely, a license agreement. Use that and don't worry about encrypting procs.
September 23, 2015 at 10:32 am
Thanks you all for your responds. It really brought a light to my question.
Alex
September 23, 2015 at 10:50 am
Good luck and if you have other reasons why, happy to debate. I'd be curious what you do.
September 23, 2015 at 11:29 am
Steve Jones - SSC Editor (9/23/2015)
With the obfuscation easily bypassed (SQL Compare Pro[/url] does it), what is the protection? What do you think clients will do with the intellectual property? In fact, what code could you write to transform data that someone couldn't duplicate?IMHO, this is silly. The encrpytion of objects provides minimal deterrent to someone in finding the code. If they want to, they'll find it. If they don't, then why bother? However this does ensure that it is difficult to determine what version of your code is deployed on which client's system.
You have copyright, and likely, a license agreement. Use that and don't worry about encrypting procs.
100% in agreement.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 23, 2015 at 11:37 am
Steve Jones - SSC Editor (9/23/2015)
With the obfuscation easily bypassed ... what is the protection?
I think a programmer, from XYZ Vendor, visiting our client might take a look at our source code. They could RightClick Edit an SProc in SSMS. They might even cut and paste it to USB Stick / Email ...
It would be mostly curiosity, or perhaps hoping to take a look at a particular piece of code (e.g. because it has an obvious looking name).
I think it is slightly more difficult (time consuming and greater chance of either getting caught, or leaving an audit trail of their activity), for such a person [sat in our client's office] to script the whole database, using some 3rd party tools to reverse the encryption, or take a full database backup and copy that to USB Stick or email it out of the building to script it out later.
So I think, for the minimal effort it takes us, it is worth doing. It also stops any "well meaning client staff member" from fixing an SProc with RightClick Edit 😀
I don't know how many people know about reversing Encryption, probably not the world and his wife, albeit that most well seasoned regulars would. I'm at the small-database / low-end, so I think there is a good chance that people looking at one of my databases, on a client site, would give up when the did RightClick Edit and saw "Sorry, no-can-do, the Object is encrypted"
But I may be dreaming of course ...
... and for a determined thief it is no deterrent, but they will have to go the wrong side of the law to help themselves to a copy of our code. That may, or may not?, be worth something or nothing.
September 23, 2015 at 1:33 pm
AER (9/22/2015)
Hi Lowell,I'm a DBA who administer all the Company databases. By 'intellectual property' I meant the scripts that are written in the databases. The problem is that our Client requested a 'sysadmin' role in the SQL Server and it looks like we are going to give it to them. So, we wanted to protect our scripts one way or another.
...
You simply can't hide or deny access to objects from a member of the sysadmin role.
However, one option is to encrypt the body of your stored procedure in such a way that it's usable only if the application supplies the correct "pass phrase".
For example, let's assume we want to use the pass phrase '8E8F7BBB-6D24-46DE-B32B-F6BC9DB9D6FF'. We use the EncryptByPassPhrase() function like this to get the encrypted varbinary representation of the T-SQL statement(s).
print EncryptByPassPhrase('8E8F7BBB-6D24-46DE-B32B-F6BC9DB9D6FF',
'
SELECT BusinessEntityID, NationalIDNumber, JobTitle, LoginID
FROM AdventureWorks2012.HumanResources.Employee
WHERE BusinessEntityID = @BusinessEntityID
');
0x01000000E7CECFA9C2A00B8322B084D1 ...
We then create a stored procedure that accepts @PassPhrase as an input parameter, in addition to whatever parameters the procedure would normally have. Notice that the encrypted text is passed to the DecryptByPassPhrase() function along with @PassPhrase, thus returning the unencrypted T-SQL text into a variable called @sql which we then execute dynamically.
create procedure sp_EmployeeList
(
@BusinessEntityID int,
@PassPhrase varchar(8000)
)
as
declare @sql varchar(8000) =
cast( DecryptByPassPhrase( @PassPhrase,
0x01000000E7CECFA9C2A00B8322B084D107FDBDD8DB1245E2FBC29B133EA44FE479177DEF8127454B1BD1FBD237CE1315366585107C80A8F4CBB9B36F507EB84A34B150D6206FED750B6CCAFE528EFBF49E7941F8E73993CCDB403A02DB02ADFD035E8EE3A32EB456ED5A4FCDD64C9842F9FB934E9E755BC1F83F91C694D70CC48A73B2FB470B0F507FE9ECDA497E032924BF565418FF7B251AAFE1D04A6A24783D73E25D131F68CE9B8940097C2EBD0BC610E781
) as varchar(8000));
EXECUTE sp_executesql
, '@BusinessEntityID tinyint'
, @BusinessEntityID = @BusinessEntityID;
go
The procedure is called like so and functions with no special considerations other than what you would expect with dynamic sql.
exec sp_EmployeeList
@BusinessEntityID = 5345
, @PassPhrase = '8E8F7BBB-6D24-46DE-B32B-F6BC9DB9D6FF';
The encryption used by EncryptByPassPhrase() is TRIPLE DES with a 128 key bit length.
However, keep in mind that the DBA, or anyone in sysadmin role, can still see the clear text of the executed T-SQL using a trace or querying plan text from the DMVs. For obvious reasons, SQL Server isn't designed in such a way to conceal T-SQL execution from someone in the sysadmin role.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 23, 2015 at 1:39 pm
Though profiler, XE and the various dm_exec DMVs will still contain and show the plain text of the executed statement.
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
September 23, 2015 at 2:12 pm
I understand the support case, but to me, why? I'd charge for those incidents, but there's nothing to stop me from taking a copy of the database from a "friend", erasing their data and then using it for mine. There just isn't.
I think most people consider their code to be more valuable as IP than it really is. I haven't really seen a case in a database platform where this is the case.
However I do get that encryption prevents people from messing with code, but it doesn't. They could certainly "alter proc" and put any code in there if they wanted to. Granted most won't, but most won't mess with procs either. Those that do, charge them to fix things.
September 24, 2015 at 7:10 am
Regarding the Why of all this; if you are an ISV, and you don't want the business logic of the stored procedures to be in clear text, and the method by which deployments are done is that you distribute .sql scripts to the client for they run manually or via an install package, then in that scenario stored procedure encryption might make sense.
However, simply adding the "WITH ENCRYPTION" keyword to the CREATE PROCEDURE statement won't accomplish anything, because the procedure is only encrypted after it's compiled, and you're still sending them the scripts with business logic in clear text. You would instead have to leverage something like the technique I described earlier where the business logic is coded in encrypted binary format and then decrypted and executed dynamically at runtime using an external key supplied by the application.
Yes, someone in sysadmin role can still derive the text of the T-SQL statements at runtime, but at least you're not distributing the .sql scripts wholesale with the business logic in clear text. In the event that you're proprietary SQL algorithms, or whatever it is you're intending to protect, do become public, then you know that it was the result of intentional "hacking" on the part of the client and not simply the result of your original deployment scripts getting passed around. It's a moderate level of protection that prevents casual disclosure.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 24, 2015 at 7:21 am
As a side note, it appears that SQL Server does take at least some measures to respect the intent of stored procedure compiled using the "with encryption" option, by blocking the T-SQL at runtime from appearing in SQL Profiler traces and execution plan display.
http://stackoverflow.com/questions/15840543/sql-server-profiler-suddenly-says-encrypted-text
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply