Encryption

Here is my goal please let me know if it is possible.

I have installed sql express on clients machines. I don't want them to be able to view the sp's or the functions. I would like to go as far as not allowing them to see the tables. I tried with encrption but this is still breakable by the user using the DAC.

Thanks

Will the users have be in the sysadmin role on their servers?

If so, there is little you can do, for sysadmin has full access to everything. You can use WITH ENCRYPTION when you create Stored Procedures and FUNCTIONs, and the IP will be somewhat 'safe' from prying eyes. (Just be sure to keep a copy of the code saved somewhere in case you ever have to make changes.)

|||

Ya only problem is if the user wants to they could use DAC to access the encrypted sp's.

My application will be downloaded via Click Once it will install sql express on the users machine if they don't already have it installed. So there is really nothing that I can do to the physicall machine.

Is there a way to hide DSQL from profiler? I was thinking if I made it all DSQL and hide it from profiler I would be ok then.

|||

Any code that executes on SQL Server will be viewable with Profiler -including dynamic sql.

If the client owns the database (that is, sysadmin role), guess what, they 'own' the database, and there is little you can do about it.

|||

Is it possible to disable the sysadmin role if I am installing a new instance of sql express?

|||No, you cannot, and do NOT want to disable the sysadmin role.|||

Why not? If i disable the sysadmin role then there would be no way for them to view the sp's or functions.

|||

Hi Jonathan,

You can't remove the built-in security roles. Besides, SQL Server depends upon the SysAdmin role to do many things; upgrade the instance, manage the server, etc. If you remove it, you will break the server. No matter what you do with the server security elements, the data file can always be moved to a different server where the user does have rights.

There are a few things you can do:

You can use the WITH ENCRYPTION command when you create objects to help obscure your sources. You can encrypt the actual data in your database to protect that. You can license/copywrite your application, including the data structures so that if anyone steals them you can seek a legal settlement. (To be honest, this is probably the most important thing you can do to protect you IP, but most people don't do this.)|||

"You can license/copywrite your application" This is a very good point and something that I will look into for sure.

What about doing CLR sp's?

Is there a easy way for those to be decrypted/cracked?

Thanks

|||

You 'could' use an obfuscator to 'hide' the CLR code -but you are introducting an additional element of 'slowness' to the performance equation. As well as additional barriers to code maintenance/upgrades.

Is it really worth it?

Sell you product with the full protection of IP/Copyright laws, sell only to reputable clients (ones that have something at risk should you ever need to litigate), and realize that someone else may come up with the exact same code solution you did. Would you want to have to 'vet' all of your code to make sure someone else hasn't done it before?

That's life -we protect ourselves the best we can, hope that there is such a thing as 'karma', and move on.

|||

I concure with Arnie regarding CLR objects, they are not a security measure. CLR Stored Procedures are there to address certain types of operations that are better performed using a procedural language, or that are not possible using T-SQL. They are very powerful, but there is a trade-off (there always is). In that they don't necesarily perform as well as T-SQL for standard data operations because they introduce extra layers.

You'll introduce new problems into your programming if you try to go down the CLR route as a way to "secure" you code. Once that happens, you spend a bunch of time and money solving the wrong problems. For what it's worth, I see these needs your bringing up and we're talking about ways to meet the need to offer better protection over IP in the client scenario in a future release of SQL Server.

Mike

|||

Is it possible to disable the DAC?

|||

Actually, DAC is disabled in SQL Express by default, but there is no way to prevent DAC from being enabled and used by someone with the appropriate permissions on you computer.

Mike

|||

What about using SQL Compact and embedding the sql database?

What issues would i have with security with this.

Also what is the max db size and are indexes supported?

|||

There is a forum dedicated to SQL Compact Edition, so you should direct your specific questions to that forum. You may also want to read the information available about SQLce on MSDN, start at http://msdn2.microsoft.com/en-us/sql/bb204609.aspx. Right now SQLce has the same db size limit as SQL Express, 4 GB. The security model is different, you can specify a single password for the entire file and that password must be supplied in order to access the file. When you password protect the database file, it is encrypted, but I'm not sure what level of encryption is used. I believe they support a subset of the same types of indexes available in SQL Server.

Mike

sql