| Author |
Topic |
|
Josh Lindenmuth
Starting Member
6 Posts |
 Posted - 2005-10-25 : 19:39:48
|
| We are considering a SQL Server deployment (mostly Express, but possibly some full SQL Server installs too) to many clients who do not have the technical staff to maintain file system permissions (most login as an Administrator and leave their systems on). Is there a way to add security settings to a database file in such a way that someone could not access the database if they copied it to another computer where they had the server's sa password? How can we ensure that an unauthorized user cannot copy the mdf file to another computer to access the data (we want to embed a user name and encrypted password within our application, such that there is no possible way to access the database other than via our application).Thanks,JoshPayce Payroll - www.paycepayroll.com |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-10-25 : 23:58:45
|
| secure the server, windows account, service account where the database is to be locatedand always lock the server automatically after n-minutes of being idle like password protected screensaverotherwise if you have no control over these, you may want to exploreEFSHTH--------------------keeping it simple... |
 |
|
|
activecrypt
Posting Yak Master
165 Posts |
Posted - 2005-10-26 : 00:19:56
|
| Hi All,* Set up SQL Box with strong SA password * rename SA account* change the port nomber* SET your filesystem with NTFS partition* If possible always create ROLEs with appropriate privilages and map logins to it* Disable NETBIOS on the SQL BOXMust Read Article about SQL SERVER SECURITY :[url]http://vyaskn.tripod.com/sql_server_security_best_practices.htm[/url][url]http://msdn.microsoft.com/library/en-us/adminsql/ad_security_46av.asp[/url][url]http://www.microsoft.com/technet/security/prodtech/dbsql/sql2kaud.mspx[/url]Top 10 Security Enhancements in SQL Server 2005[url]http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1132767,00.html?track=NL-464&ad=525298[/url]HTH-----------------------------------------------------------MSSQL Server encryption software http://www.activecrypt.com |
|
|
|
Josh Lindenmuth
Starting Member
6 Posts |
Posted - 2005-10-26 : 13:14:24
|
| Thanks for the suggestions, but unfortunately we have little to no control over these options. Almost all of our clients will be deploying our application AND the database on the same computer, namely, their desktop. They will always be logged in as the local administrator ... meaning anyone on that desktop (or any worm that finds their computer) will be able to access the .mdf file. We have too many users with minimal technical knowledge to attempt training them on Windows permissions administration. If we setup ROLEs with appropriate priveleges, would someone be able to overwrite these roles if they copied the database (.mdf) to a separate computer (and logged in as sa)? Our only good alternative option at this point is MS Access. It seems rather ridiculous that Access would provide better file level security than SQL Server or SQL Server Express. Any thoughts?Thanks,JoshPayce Payroll - www.paycepayroll.com |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2005-10-26 : 13:20:25
|
| Once a customer buys it, you don't have control. Honda has never called me to ask if I have tampered with anything on my car. This is what warantees, and support contracts cover. You have to trust the admins in a company at some level. Otherwise, if you think the admins are peeking at salary data, let the business know, and let them make a decision as to whether they want to fire the admins or not.EDIT: One other thing to look at is there are several articles about how to encrypt/decrypt individual fields in SQL Server. It requires some code, but it can certainly be done. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-10-26 : 13:24:46
|
| If someone can get access to the MDF, then they can copy that file to a machine where they have local admin and gain sa privileges to the SQL Server. They then can attach the database to the SQL Server and see anything in that database. Your only option is to encrypt the data. I don't know enough about Access to make any comments about it.Tara |
|
|
|
Josh Lindenmuth
Starting Member
6 Posts |
Posted - 2005-10-26 : 13:35:25
|
quote:
Originally posted by mcrowley
Once a customer buys it, you don't have control.
Sorry, maybe I wasn't clear. This application is deployed to many small companies that don't have technical staff, and sometimes only have 1 computer (such as a restaurant). Unfortunately, we can't expect these companies to follow exacting security policies. Also, we are not selling the application to them, we are providing them the software as part of a service license, so they are paying us on a regular basis in order to use software that they expect to be secure. Is there a way we can encrypt ALL the data without using EFS, but still be able to send the database SQL statements from our application? Ideally, the process would be: 1. Connect to database 2. Send private key to database from application to unencrypt database 3. Run exact same SQL statements from application to insert/update/delete/select from databaseThanks,JoshPayce Payroll - www.paycepayroll.com |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-10-26 : 13:43:06
|
| Here's one option in SQL Server 2000 that I saw while at the PASS conference this year:http://support.microsoft.com/default.aspx?scid=276553I don't know if SQL Server 2005 provides better support.You also can do this via your application where you have the key hard-coded in your application.Tara |
|
|
|
MuadDBA
628 Posts |
Posted - 2005-10-26 : 16:07:18
|
| Another option is to make sure your application only uses stored procedures to commnicate with the database, and then encrypt all the stored procedures. Yes, the encryption isn't super-duper secure, but it's usually enough to keep a newb from copying your DB and then slicing through the data himself/herself. then again, if your tables are named and contrsucted fairly transparently, this won't help, because they will be able to piece together what you're doing. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-10-26 : 22:15:32
|
| data is owned by clients, you can't protect it from them, unless you provide them with one database each and lock that down for them onlyyou can do this by:1. removing the builtin administrator account in the sql logins2. provide each of the users dbo rights on their own database only3. do not provide them with SA privileges4. for backups, be sure to password protect them, so even if they are copied over, restore will be difficult5. And finally, since users are DBO on their respective databases, they are responsible for their accounts.ORcreate interfaces for accessing the data, this will prevent them from gaining direct access to the databasein essence, you're the only one administering the server |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-10-26 : 22:39:40
|
| I would say it's not worth the effort trying to secure the database file(s), ultimately your clients are responsible for their own computers. You can install locks and alarms on every door in a building, but they're worthless if someone always leaves the doors open. It's impossible for you to manage their security when you don't have control of their systems.Even if you do encrypt the data, or files, it will have a negative impact on performance, and could interfere with administrative functions like backups. And too much security could lock the client out. As jen pointed out, it's their data: if their password is lost or the only person who knew it is gone, they've got much bigger problems than someone possibly stealing it. (shit, copying the file might be the only way for them to recover the data)BTW, if the clients are not technically savvy, then who is supposedly going to make unauthorized copies and install them to another SQL Server? I'm not saying it's impossible, just that the reasoning sounds pretty illogical. |
|
|
|
Josh Lindenmuth
Starting Member
6 Posts |
Posted - 2005-10-27 : 09:41:56
|
quote:
Originally posted by robvolkBTW, if the clients are not technically savvy, then who is supposedly going to make unauthorized copies and install them to another SQL Server? I'm not saying it's impossible, just that the reasoning sounds pretty illogical.
My primary concern isn't a malicious/curious user copying the database, it's a worm or script. For our largest clients, we can easily deploy SQL Server to a properly secured database server. However, the vast majority of our clients are small businesses where our app will be running on a local machine where securing it just isn't practical. We can use Access for these systems, which would prevent the data from being accessed (at least via Access/ODBC), but I was hoping that we could provide the same level of security with SQL Server Express.Payce Payroll - www.paycepayroll.com |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2005-10-27 : 11:08:03
|
| To guard against worms, it will probably be sufficient to put a strong sa password in place, and remove builtin\administrators as stated above. After that, you can probably require that automatic updates is turned on, but that requires an internet connection, which almost defeats the purpose. Is there any chance these machines are not hooked up to the internet? I doubt many restaurants need the ability to surf. At least they did not when I was washing dishes. |
|
|
|
Josh Lindenmuth
Starting Member
6 Posts |
Posted - 2005-10-27 : 11:15:35
|
| This application is for all businesses, restaurants was just an example. And would disabling the built in administrator prevent someone from copying the mdf file to a separate machine then logging in as sa? If so, great! That's my concern with a worm. It would be VERY easy to write a quick script that finds all mdf files then copied them to a remote location ... the ease of something accessing our data in this fashion makes me a bit nervous ...Thanks,JoshPayce Payroll - www.paycepayroll.com |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-10-27 : 11:29:50
|
quote:
And would disabling the built in administrator prevent someone from copying the mdf file to a separate machine then logging in as sa?
No, that only keeps them from accessing the database through Query Analyzer and such. The filesystem is still open to an admin.quote:
It would be VERY easy to write a quick script that finds all mdf files
Change the file extension. There's no law that says you have to use MDF and LDF. The only way for the worm to know is to query the sysfiles table, which requires them to log in to SQL Server.quote:
then copied them to a remote location
If you run SQL Server under the LocalSystem account (the default, and the likely account that your clients use) then it will have no network access whatsoever, so the files cannot be copied off the server. Also, database files that are in use cannot be copied, they would have to be detached first. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-10-27 : 12:39:03
|
quote:
Change the file extension. There's no law that says you have to use MDF and LDF. The only way for the worm to know is to query the sysfiles table, which requires them to log in to SQL Server.
That's an excellent idea! All of the other ideas that have to do with sa, BUILTIN\Administrators, dbo, etc... have to do with securing the database inside SQL Server. If anyone gets a hold of the MDF, they can attach it to their SQL Server where they are a sysadmin and do whatever they want with it. Usually you can do the attach even when the MSSQLSERVER service was running on the source server which meant it was accessing the MDF file.I would extend Rob's recommendation to include putting the database files in a obscure directory. Don't use MSSQL\DATA or anything like that because even if you rename the extension on the MDF and LDF, people with SQL Server knowledge would be able to figure it that the files in that directory were database files.Tara |
|
|
|
Josh Lindenmuth
Starting Member
6 Posts |
Posted - 2005-10-27 : 13:11:08
|
| I agree, great idea! Thanks Rob.Payce Payroll - www.paycepayroll.com |
|
|
|
elmasrya1
Starting Member
3 Posts |
Posted - 2008-06-03 : 05:12:02
|
| Hello, how can i rename the extension of the MDF file!!??? if i did that the SQl won't see the DataBase which related to this MDF file!!! i tried that and i figured that the sql first see the database but as an empty folder in the Object Explorer. second, when i try to run an application use this database it makes an exception because it can't connect to this database and this logic because the sql itself did't see the MDF itself. plz. i'm not Expert in DBA, but you siad that i can change the extension of MDF file, SO can you explain how can i do that?????ElmasryA1 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-03 : 12:30:29
|
| You can change the extension if you detach/attach. But why would you want to do this!?If you aren't an "expert DBA", then you shouldn't be touching this.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
|
|
|
tripodal
Constraint Violating Yak Guru
259 Posts |
Posted - 2008-06-06 : 17:23:57
|
| Encode your application to encrypt the data before it goes into the database. Use a long encryption key, to difficult to bruteforce to be worthwhile. You can this key to specific hardware on the machine. Much like windows activation does. If a customer copies your software, it will not run on a new machine unless you generate a new key for them. And if the customer runs a backup/restore of your database, the data will be encrypted. |
|
|
|
|
How to secure a database file?