May 4, 2010 at 6:55 am
Hello,
I am software developer using DotNet as Front-End and SQL Server 2008 Express as Back-End.
I want to distribute my application and it is using SQL Stored Procedures.
How do I encrypt Stored Procedures so that my logic can not be seen by someone.
I found some tools to decrypt Stored Procedure for SQL's own encryption method (WITH ENCRYPTION clause)
So what is the solution for it.
Tools like SQL-Shield (at http://www.sql-shield.com) is costly for distribution
May 4, 2010 at 6:59 am
is it because your customer use there own sql serveur ?
May 4, 2010 at 7:38 am
with both .net and SQL server's built in tools, eventually someone can reverse engineer decrypt everything.
SQL server has to be able to compile any encrypt stored procs/functions ; the WITH ENCRYPTION prevents casual review of your code, but a determined dba will have the tools to decrypt them if he wants.
The same thing goes for all your .NET executables and dlls; Because of the way the Just-In-Time compilation works i can use RedGates Reflector to reverse engineer / view the decompile your executables and dll's back into their projects, complete with the classes, modules, forms, etc.
It's just gotta be worth my time to do it...Reflector even lets me convert it form one programming language to another.(ie C#.NET to vb.NET).
Lowell
May 4, 2010 at 12:14 pm
If the instance of SQL Server is running on the user's desktop, then by default, they are a privillaged user. Even if you embedded and encrypted the SQL statements inside your application, the user could still use SQL Profiler to log activity on the server.
Perhaps if your application setup process installs it's own instance of SQL Server Express and then locks it down with only a single SQL Server account for which your application has the credentials embedded. However, a determined user could eventually find a way to get around that. For example, they could open the .mdf file in a Hex editer and locate the clear text of the objects.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 4, 2010 at 12:18 pm
eric_russell 13013 (5/4/2010)
If the instance of SQL Server is running on the user's desktop, then by default, they are a privillaged user. Even if you embedded and encrypted the SQL statements inside your application, the user could still use SQL Profiler to log activity on the server.
Just a small point here. If the stored procedure is created using the WITH ENCRYPTION clause, then Profiler will honor it. In other words, the details won't be shown in Profiler.
K. Brian Kelley
@kbriankelley
May 4, 2010 at 12:38 pm
If someone is developing something like a desktop application for release as shareware, and they feel it's important to protect the contents of proprietary reference tables and SQL queries, then MS Access would probably provide better options than SQL Server Express edition.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 4, 2010 at 1:40 pm
Access? Good heavens I hope not.
You might look at SQL Server Compact Edition, not sure your exact requirements but it's small, light weight, and if you give your database a password it will automatically encrypt the entire database.
Some downsides: It's single user, the db cannot be stored on the lan but must be on a local drive, there's a 4gb limit, and no stored procs. However since it's all running on your desktop you can rewrite the stored procs as methods and not lose speed.
Pluses: It can be embedded entirely within your app, doesn't require admin privlidges to install, or a seperate install of any kind. It's a DLL you simply reference and it gets bundled up with the rest of your app.
It's not quite clear from your original post if you need to have multiple users on the network hitting your app, or if this will all reside on a single desktop. If it's a desktop app and the database is only used to store data, you might look at SSCE. I did quite a few posts on it, http://arcanecode.com/category/sql-server-compact-edition/ .
Depending on your needs and how far along you are in the development lifecycle it may or may not be a good fit, but IMHO much better than Access.
One other thought, you should probably balance possibilities. How likely is it that someone would actually take the time to try and decrypt the thing? It would take some effort to find the tool to decrypt "WITH ENCRYPTION", not something a casual user is going to want or be able to do. I'd only consider going beyond if you are in a highly competitive market and think a competitor might gain some big advantage over you by trying to reverse engineer your app. If so, you might then decide it's worth the investment to go farther.
May 4, 2010 at 2:03 pm
eric_russell 13013 (5/4/2010)
If someone is developing something like a desktop application for release as shareware, and they feel it's important to protect the contents of proprietary reference tables and SQL queries, then MS Access would probably provide better options than SQL Server Express edition.
I don't know if I could DISagree more.. Access is not robust enough for most cases. SQL Compact edition would be more palatable than Access..
Everything else said so far is accurate, you cannot fully secure your code, you can make it harder to decode, you can encrypt your sprocs but you can't trully prevent the clear-text from every being available again. And while the profiler my honor the encryption setting the call to the sproc can still be seen in clear text just not the internal details.
Long story short, you can't make it difficult for a casual examination of your code, both .NET and SQL, but you aren't going to really be able to prevent a determined user from getting the clear text sourcecode.
CEWII
May 4, 2010 at 2:13 pm
eric_russell 13013 (5/4/2010)
If someone is developing something like a desktop application for release as shareware, and they feel it's important to protect the contents of proprietary reference tables and SQL queries, then MS Access would probably provide better options than SQL Server Express edition.
Eric, you touched a raw nerve by suggesting Access over SQL Server, so expect a bit of hate. With that said...
Microsoft Access offers no real protection over SQL Server with respect to keeping anything secure. As an exercise, if you go to your favorite search engine and type the phrase "Crack Microsoft Access" you will see a plethorea of hits. If someone is invested enough to crack SQL Server's encryption, then they'll do what it takes to break Microsoft Access, too.
K. Brian Kelley
@kbriankelley
May 4, 2010 at 3:03 pm
Eric, you touched a raw nerve by suggesting Access over SQL Server, so expect a bit of hate. With that said...
Microsoft Access offers no real protection over SQL Server with respect to keeping anything secure. As an exercise, if you go to your favorite search engine and type the phrase "Crack Microsoft Access" you will see a plethorea of hits. If someone is invested enough to crack SQL Server's encryption, then they'll do what it takes to break Microsoft Access, too.
Yes, soon after posting my comment about MS Access, I thought about SQL Server Compact Edition. đŸ˜› If it were me, I'd probably go the SSCE route, but I don't develop desktop or shareware applications containing proprietary code that needs safeguarding from competitors, so I havn't been in a position where I actually had to lay awake at night weighing the options.
Unlike previous versions, Access 2007 introduced real 128bit encryption based on the Crypto API using it's new ACCDB file format. I believe that the techniques for "cracking" a MS Access database are either brute force password attacks using automation or for the older MDB format which simply "encodes" the file a type of weaker encryption. SSCE provides 128bit password based encryption just as well, and I totally understand why one would favor it over Access. However, I think that MS Access would still offer some advantage over SSCE in that it can also contain stored procedural code as well. With SSCE, you are limited to only views and enbedding your SQL or LINQ queries inside the .net executable, which can be disassembled or seen as clear text.
My assumption is that one would perhaps want to encrypt static reference tables that get bundled with the setup, like a job costing application for example, where the actual data itself is proprietary and might have some value to a competitor. I would not reccomend that the developer encrypt the data entered by the users themselves in a such that it would be difficult for them to access it outside the application, because I think that users own their data.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 25, 2011 at 5:25 am
i believe sql-shield.com and dbdefence.com are made by andy wu - which i believe to be a russian outfit - even though they have usa phone number, a couple of years ago i found them operating out of russia when looking to hide sql objects and i tested their blokkk aka dbdefence software until they asked for my customers database! i am very unsure as to the legitimacy of these products - although i have trialled early releases and they do appear to work. am just not sure about the credibility of company and if there is any hidden agenda in the software
does anyone have any further info to give comfort? if they are a pukka company - why no reviews out there ?
IT
August 25, 2011 at 7:15 am
Use WITH ENCRIPTION option, but remember that can wach it by profile if the user set stmnt watch. Another problem is scripting, because you can not encript scripts, only compiled procedure (in oracle you can offuscate it).
-
Lic. Andrés M. Aiello
DBA MSSQL - Oracle
August 25, 2011 at 12:40 pm
My uderstanding is that the only decryptors of stored procedures was based on sql7 and before. When sprocs are encrypted within SQL 2005 & 2008 there is no way of decrypting them (happy to be proved wrong on this if someone can provide the link).
Profiler will not decrypt the contents nor the statements as they run. All you get is "SP:StmtStarting --- Encrypted text"
If you are trying to protect your IP then it is probable the easiest way, but do be aware though that revervse engineering is always possible. Some folk like crosswords, others sudko and a few the puzzle of reverse engineering code (aka maintenance contracts). If the logic is non-simple then it will be more secure than .Net.
Word of warning only encrypt after you have clear text version backed and printed, photocopied in triplicate!!
August 25, 2011 at 12:51 pm
You're right, only appear encrypted text. new tip of the day!
-
Lic. Andrés M. Aiello
DBA MSSQL - Oracle
August 25, 2011 at 12:59 pm
This quote from MSDN CREATE PROCEDURE documentation leads me to believe that the text of the stored procedure isn't encrypted in the database, but rather SQL Server doesn't expose the text from meta data views or procedures for users who don't have sufficient permission. For example, it could be used by members of SYSADMIN role to obfuscate the text of a stored procedure from users who have VIEW DEFINITION permission and could otherwise see stored procedure text.
..Users who have no access to system tables or database files cannot retrieve the obfuscated text. However, the text will be available to privileged users who can either access system tables over the DAC port or directly access database files..
http://msdn.microsoft.com/en-us/library/ms187926.aspx
The local user typically is a SYSADMIN for any instances of SS Express Edition installed on their PC, so options are very limited about what you can hide or deny them acess to.
It would be great if SS Compact Edition could contain stored procedures, because you have the option of encrypting the entire .SDF file with the password embedded in the application. SSCE databases are also a lot easier to distribute.
"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 17 total)
You must be logged in to reply to this topic. Login to reply