SQL Server Express Security?
We are releasing an application built on SQL Server Express in the next few months. Our clients are mostly small companies with little technical knowledge, and they will be using our application on desktop computers where everyone likely logs in as the same user. As such, my thinking is that we setup SQL Server Express as in SQL Server Authentication mode. This way they could select an Administrator password upon first installation, and manage user names and passwords from within the application itself.
Any thoughts? Also, how would we install this scenario? We haven't been able to figure out how to customize installation packages using the Microsoft Installer (we're using VS2005).
Thanks,
Josh Lindenmuth
Here's a caveat with this design: if the user account that people use to connect to the desktop has sufficient privileges to access the database files (and I fear that the account might usually be a member of the Administrators group), then anyone with access to the machine can circumvent the SQL Server authentication and can access directly the data.
For the installation part, you can probably install SQL Server Express in unattended mode. You can try the SQL Server Setup & Upgrade forum for additional information on the setup options.
Thanks
Laurentiu
You are correct, 95% of our users will be logged in as someone in the Administrator's group, but only a small % of the users should be able to access the data within the database. I realize they could easily get to the .mdf file ... but if they didn't have the login/password, would they be able to do anything with it if we were in SQL Server Authentication mode?
Thanks,
Josh
A user could copy the database and attach it to his own server at home, where he would be an administrator, so he could access the database as dbo. This would allow him to see all information in the database, even if he didn't have access to the original server. The authentication mode of the server wouldn't matter as he is cicumventing it by accessing the files at the OS level.
The problem is that access to the box is not properly restricted. If everyone is a sysadmin, you cannot easily prevent their access to data stored on the machine.
Thanks
Laurentiu
Is there a good way around this? We cannot guarantee that our clients will have adequate file system security measures in place. Currently about 90% of our clients allow everyone to login with the same user and password (and many have no passwords). It's unreasonable to expect that
all of these clients would follow our recommendations, and the data they're using is critical enough (Payroll and Banking) that we need to secure it at a database level.
So do we have any option that would provide this level of security, short of going with another DBMS?
Thanks,
Josh
You can use encryption to protect all sensitive data, so that only authorized SQL Server users can read it. If you choose to encrypt your data, make sure that the encryption key is not stored on the machine, so that no unauthorized administrator can access the key and decrypt the data after copying the database files. You should also make sure that a malicious administrator cannot monitor the computer activity such that he would be able to retrieve the encryption key.
Thanks
Laurentiu
Encryption isn't an option. We have 1000's of data fields, hundreds of which are "secure". Plus, we are utilizing a custom reporting engine that requires SQL to be passed to the database directly ... there's no way we could execute a query like 'SELECT employee_name FROM employee WHERE salary > 30000' with encrypted data, right?
The only other option we could think of was to use Microsoft Access for all of our smaller clients, and SQL Server for the larger clients. From what we can tell, Access maintains file level security that would persist as the mdb file is copied to other systems. It seems rather ridiculous to me that Access would provide better security than SQL Server though ...
josh
My apologies, I've probably not been very clear about the extent of the problem with your scenario. If you have a machine where every user can connect as a sysadmin and then some of them connect to a database but you want to prevent the rest from connecting as well, then it doesn't matter what database system you are using, you cannot protect its data from a machine administrator. The reason is that a machine administrator can install a keyboard logger and figure out the password of any authorized database user. If you do a search on the Internet, you will find a multitude of keyboard logger products available at affordable prices - some versions are even free.
My suggestion is to explain to your customers that if the security of their data is a concern, then they should replan the way they secure access to the machines on which the data will be stored. Maybe the fact that coworkers who have access to the machine can all potentially access the database is not really an issue for your customer, but if it is, it cannot be solved at the database level, you have to change the way the coworkers access the machine.
Thanks
Laurentiu
This wouldn't be the case if no users had access to the system, only the application (via a strongly encrypted password stored within the executable), right? This way, the only product that can access the database is ours. If the database had adequate security measures in place, then only this login would be able to access data, regardless of computer.
Unless there is some work-around that I'm not aware of, I could easily lock down an Access database so only my application could access it, and if the mdb file was copied to another machine it would become worthless. At this point, am I correct in saying that only MS Access would provide this database level of security? It seems a little crazy that Access would provide better security for small businesses and personal users than SQL Server Express ...
Thanks,
Josh
If your application uses "a strongly encrypted password stored within the executable", then you must have some password to decrypt it and that password must either be stored in clear or typed in by a user, in which case, again, it can be obtained by a machine administrator, so he can use it to access the data circumventing your application.
As for the use of Access, it would not provide a protection against a machine administrator, for the same reason I mentioned in my previous email. If you still don't see why, please describe the protection that you would set up and I will tell you how I can circumvent it if I am a machine administrator.
Thanks
Laurentiu
OK, so the idea is to store an encrypted password in the executable. The password that would decrypt this encrypted password would be a long (e.g. 256 character) application generated password that would not be stored in the clear ... it would be generated via math routines. The user would never know this password or be required to enter it. In a compiled application, this would make it
extremely difficult to circumvent.
Besides ... consider the difference in effort:
SQL Server:
- Find *.mdf
- Copy mdf file(s) to new machine and start running queries ... any worm could do this easily and automatically
Access or any database that enforces database level security:
- Find *.mdf
- Copy mdf file (as above)
- Find the application that references this mdf file (difficult for a worm, easier for a hacker or malicious user)
- Parse the assembly language code to figure out password scheme (very difficult)
- Continue parsing the assembly language code to figure out how to decrypt the data (e.g. the math routines - even more difficult)
- After determining the math and building the password, login to database
Anyone with remedial technical skills could easily access the SQL Server data. Someone would need VERY advanced skills and a TON of time to break into the Access data. And if we felt this was not secure enough, we could keep the key on our corporate server and require each client PC to securely connect to our server to access the key.
I'm not looking for a circular conversation, I'm looking for a solution, hopefully one that does not necessitate us using Access. SQL Server (and SQL Server Express particularly) provides us with everything we need, except for the level of security that's absolutely necessary for a small business or individual user installation. In these situations, almost everyone logs in as an Administrator ... which means that a few lines of vb script, a short executable, or just a curious user could easily access the mdf file. In Access, you could devise a scheme where it would be nearly impossible to break into the database (but obviously not impossible).
I'm not sure why Microsoft wouldn't provide database level security ... particularly for SQL Server Express which will be deployed to many home PCs and small business PCs where users will almost never follow recommended security measures. Am I wrong in thinking this would be a fairly straightforward change to the architecture?
Thanks,
Josh
I assume that the application that connects to the database is used by an employee that connects to the machine and then to the application. Even assuming that the database file protection is hard to break and that it would be hard to break the hard-coded password that is stored in the application executable, a machine administrator can still find out easily the password with which an authorized user gains access to the application, and can use that password to gain access to the data through the application itself, so the access restriction enforced by the application is not really bypassed, but it doesn't help either against the threat of a malicious machine administrator obtaining access. You don't need very advanced skills or a ton of time to break this security scheme.
Also, in general, it's not a good security assumption to think that because something might take a week or a month of skillful work to break, then it's a secure solution. It only takes a single person that spends that time and comes up with an application that automates the process, for the security to be compromised and be made easy to compromise by anyone, without any time and skill requirements. It's a one time investment and that's what makes good protection solutions hard to design, because a single successful attack would render them useless.
I also recommend verifying your assumptions about the strength of the Access protection by posting to an Access newsgroup or by contacting customer support for Access. I am not an Access expert and I cannot comment in detail on its protection mechanisms without direct knowledge on how they work, but I believe that for your specific scenario where you want to restrict access to a person with administrative rights on the machine, regardless of what Access is doing, its protection would be pretty easy to circumvent using a keyboard logger.
My suggestion for a solution is to properly restrict access to the machine at the OS level. This is the solution I suggested earlier as well. Access will not solve the security problem with the scenario that you described. Nor will any other database that I know of. So you can either change your security requirements or you can adjust the scenario usage requirements. My suggestion is to do the latter and change the machine access security requirements.
Real database security is not easy to implement and is not a straightforward change to a database product even if conceptually it might not be hard to envision. Protection of the file contents so that it cannot be read on a different machine, if done by simple obfuscation would not confer real security, because the obfuscation algorithm would be easily broken. An algorithm that would be harder to break would unfortunately have a significant performance impact, something that cannot be ignored for a database. And in the end, no amount of database security will by itself protect from an administrator that can install monitoring software on the machine.
To conclude, SQL Server does not provide a method to protect the contents of its database files if access at the OS level is allowed, other than by using encryption inside the database. For Access, you should verify the strength of its protection mechanisms, this is not the proper forum to discuss them. A protection scheme suitable for your scenario, however, would have to be able to protect against a keyboard logger threat, and I don't think you can find a solution in a database product. Maybe by combining an application that uses technology like smart cards with a database that secures its contents you could provide a solution that may address all threats or at least make it indeed harder to break, but I don't think that an organization that allows employees to connect to the payroll machine as administrators even if they don't have access to the payroll data would consider investing into using smartcards to restrict access, when a simpler solution based on properly restricting machine access exists.
Thanks
Laurentiu
FYI:
You can easily break an access password. There are cheap programs available to buy that do it for you too.
In Access you could remove permissions to open or view a .mdb file but still access data with queries with owner access permissions - I think this is what you might want using SQL Express.
I am a beginner with this but maybe the database could be created with an owner other than an administrator, restricting viewing rights and using views stored with the database to access data.
I don't know if this is possible but owner access queries were the solution for me in a similar situation using Access.
GS
PS But then I only wanted to view the data in small chunks rather than write to it. The database involved just needed to be restricted so that users couldn't copy large amounts of proprietry data or view the table and relationship structures. It had to be sent to users every 3 months with udated material.
GS at 2007-9-9 >
if we create all our table structures and procedure objects in 'master' database itself would it be enough to restrict in copying the db and attaching it to some other sql server instance?
