December 2, 2011 at 10:48 pm
Hi Guys
Please help me with this
In Short:
I need to lock a sql database with my own password, such that it can't be opened through windows authentication mode also.
Detailed:
I have a small applicattion that uses sql server2005 database.
So far I used to supply the application with online database and all sort of security so that no competitor of mine can steal it.
Now for one of my clients, I need to install the application to work in offline mode. i.e. I need to put the database also on his local machine.
Now If someone wants to copy the db and use it develop his own application similar to mine, He can follow following steps
1) If I only install sql express edition and provide mdf ldf files,
He can copy it and attach to his another machine with Windows authentication mode and see my stored procedures and table schemas
2) If I install sql server and create a new user say ABCwith a pwd and delete all the other users including windows authentication .
He cannot copy the db as its attached to sql server and cant open sql server to detach this and then copy,
But if he uninstalls sql from his machine and re installs it, he will have sa password reset to whatever he wants and then use it.
December 3, 2011 at 4:30 am
There is no way whatsoever to 100% secure a database so that NOONE can "re-engineer" it.
Usually, someone with server based administrative privileges can add a new user. Or the backup can be restored on a different system.
All you can do is make it more complicated. But not impossilbe (btw: the same holds true for .NET applications and most other software, too).
All you can do is to claim ownership and make it part of the contract.
Edit: if someone has acces to the presentation layer and knows the business logic, it is still possible to "reinvent the wheel".
Regarding the data inside the database: the data itself are owned by the people entering those as part of there duty, not by you. The database schema you developed should be normalized and follow Codds rules (at least to some extent.... ;-)). Unless the logic itself is something absolutely new, there usually is no reason to hide it. Some of us would even be proud to show how we did it... (let it just be for the sake of learning a better way).
Btw: How do you handle backups? What is your DR plan in case someone would need to do a point in time recovery? Are you available 24/7 on site?
December 4, 2011 at 9:10 pm
I understand sql doesnt allow us to put pwdd easily
but cant we encrypt it the way we do it for data, thru certiificates and passwords?
For Backups I have made simple exe which just print the insert statements for db, it doesnt publish schema or stored procs.
In case you see few inventory applications available online for demo, they have sql db which is not at all visible to you or your sql studio
December 4, 2011 at 10:03 pm
hitechimp (12/4/2011)
I understand sql doesnt allow us to put pwdd easilybut cant we encrypt it the way we do it for data, thru certiificates and passwords?
For Backups I have made simple exe which just print the insert statements for db, it doesnt publish schema or stored procs.
In case you see few inventory applications available online for demo, they have sql db which is not at all visible to you or your sql studio
You can create the procedures & views with encryption to encrypt them. Also, as you are using SQL Server 2005 you can encrypt the table's data as well.
If you don't want the client to know about the tables & schemas names even, then possibly you can change the tables name to something generic like "TableX", "TableXX", "TableXXX" which only you understand ;-).
However, if it is a very small database then you can think of SQL Server 2005 Compact Edition which exactly provides what you want i.e. encrypting the whole database with a password. I would suggest that you go through the limitations of this edition if you are going to use it.
To read about how to create encrypted database using SQL Server 2005 Compact Edition
December 4, 2011 at 11:49 pm
If you don't want the client to know about the tables & schemas names even, then possibly you can change the tables name to something generic like "TableX", "TableXX", "TableXXX" which only you understand .
I have to disagree on it. There are better ways to do it.
Naming Convention (suggested above) will make SQL development as well as administration awful.
December 5, 2011 at 12:12 am
Dev (12/4/2011)
If you don't want the client to know about the tables & schemas names even, then possibly you can change the tables name to something generic like "TableX", "TableXX", "TableXXX" which only you understand .
I have to disagree on it. There are better ways to do it.
Naming Convention (suggested above) will make SQL development as well as administration awful.
lol :-D, obviously Dev. It was just a hint that nothing is impossible in SQL Server if we put some thought. Apparently, I believe that Matt is mature enough that he will evaluate the pros & cons always, & that he will definitely find the best way to do it starting from here ;-).
December 5, 2011 at 2:57 am
Divine Flame (12/4/2011)
You can create the procedures & views with encryption to encrypt them. Also, as you are using SQL Server 2005 you can encrypt the table's data as well.
You can, but it's unlikely to help.
WITH ENCRYPTION on views and procedures is not encryption, it's nothing more than obfuscation and it won't stop someone from getting them. In fact, it's maybe 5 minutes work or less to decrypt.
Encrypting columns, unless using ENCRYPTBYPASSPHRASE, a sysadmin will have permission on the necessary keys and certificates to be able to decode.
The only way to be completely sure that your DB can't be accessed is to host it yourself and only give minimal permissions. As soon as the DB is hosted on a client's server, they have full permissions and can be anything if they want.
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 5, 2011 at 3:57 am
GilaMonster (12/5/2011)
Divine Flame (12/4/2011)
You can create the procedures & views with encryption to encrypt them. Also, as you are using SQL Server 2005 you can encrypt the table's data as well.You can, but it's unlikely to help.
WITH ENCRYPTION on views and procedures is not encryption, it's nothing more than obfuscation and it won't stop someone from getting them. In fact, it's maybe 5 minutes work or less to decrypt.
Encrypting columns, unless using ENCRYPTBYPASSPHRASE, a sysadmin will have permission on the necessary keys and certificates to be able to decode.
The only way to be completely sure that your DB can't be accessed is to host it yourself and only give minimal permissions. As soon as the DB is hosted on a client's server, they have full permissions and can be anything if they want.
Absolutely right Gail, I forgot they can be decrypted so easily :hehe:. Thanks for clarifying.
December 5, 2011 at 4:09 am
To protect your database, SQL Server gives you the option by giving permission at login level.
Dont give access to any login with whom you don't want to share the database.
----------
Ashish
December 5, 2011 at 4:26 am
crazy4sql (12/5/2011)
To protect your database, SQL Server gives you the option by giving permission at login level.Dont give access to any login with whom you don't want to share the database.
Fine if you're hosting it on your own servers, but give it to a client to install on their servers and they can create new logins.
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply