October 26, 2008 at 11:51 pm
Dear Forum Members,
Our application is desinged on .Net Framework, which uses SQL Server 2000. During installation our application creates the required database and connects the SQL Server instance using 'SA' credentials. The system on which our application runs is basically either Windows 2000 server/professional OR Windows 2003 Server OR Windows 2003 SBS. All the user logs in this system with Windows Administrative credentials. We dont have any central server and once our application is installed we don't have any control over it. The user uses the same system for several other purpose, hence we cannot block him from logging into the system without an Administrative power.
Our requirement is to protect the database completely from the end-users. The goal is; whatever modification is made in the tables (addition/deletion/updation of records), it should strictly happen only from our application, not from anywhere else (Query analyzer, Enterprise manager etc.). However the user should be able to view the data (or even query it) from Query analyzer. He should also be able to take a backup of the database.
We created few users with bare minimum permission (through which only viewing the data is possible) and had a very strong password for SA too (which is not revealed to the users). We have encrypted the SPs and other programming units, so that they should not be viewed by the user. But the only problem is with the Mixed Authentication And Windows Authentication Modes. As I told earlier, since the user logs into the system with Administrative priviledge he can use Windows Authentication Mode to edit the data, which we strictly want to restrict.
My question is "How can I restrict the end-users from not editing the data even if they logged in the system using Windows Administrative priviledge and connecting the SQL Server Instance using Mixed Mode Authentication OR Windows Authentication alone?". I cannot see a option through which I can set the Mode to "only and only to SQL Server Authentication Mode" (I can understand it is kept for only backward compatibility).
Please advice me.
PL Note: I cannot change the whole architecture right now as the software is running at more than 500+ places without any problem. Whatever solution you should provide should be feasible :).
Waiting for your response!
Ayan Mitra
Please consider environment before printing this message 🙂
October 27, 2008 at 11:38 am
If the user can log on with System Administrator rights then you cannot restrict their access to data using either SQL or Windows security features.
You can try some other approaches but they probably won't be easy or straight forward. For example, you could install an instead of trigger on every table and check the logged on user before allowing the DML change. If the logged on user is your app the apply the change otherwise discard it.
Of course a user with System Administrator access can find and remove the triggers.
Another approach would be to encrypt all of the data in the database and keep the key secret. This has performance implications and depending on the approach may still have holes.
Good luck.
BTW, if you were my vendor (or I your customer) and you hijacked MY SA password on MY Server, then you would not be my vendor much longer. You application really should play nice with all the other children.
October 28, 2008 at 3:59 am
Remove the users windows account. Supply them with the SQL account information (Should have read-only access). Have them use this account to query. Create a ODBC connection using the SQL Account information for persistent users if required. A windows account is not needed in mixed mode to access the server.
Create a SQL account and add it to backup administrators group.
the other way is to assigned access to a windows group and allow that group network access and log-on rights to the server (This is done in the local policy for the server). Make sure the group only has read only rights on the DATABASES in question. This is the best practice method.
October 28, 2008 at 8:13 am
Hi Dennis,
Perhaps I am reading too much between the lines but, as I read the original post, I don't think your approach will resolve his issue. His database is on a shared server at a client site. I assumed that meant that there are other databases/applications on the server over which he has no control. In fact he probably does not even know which apps or databases are there.
If this is the situation then he cannot reasonably expect to deny the customer System Administrator access to the machine (and I mean System Administrator in every sense, Domain Admin, Local Machine Admin, and SQL System Admin).
I don't know why Ayan Mitra is so adamant about denying the customer direct access to their own data (our customers would never tolerate such an attitude) but I don't think there is any mechanism in either Windows NT or SQL Server security that will ensure the customer cannot access the data when the customer has Domain or Local Machine Admin access.
In a prior job, I struggeled with a very similar issue with an in house HR/Payroll application. The CIO was adament that only one or two people have Admin access to the database, but we had at least 10 or 12 domain admins. The ultimate resolution was to create a separate domain to which only two of us had acceess. The SQL Server was moved to the new domain and dedicated to the single application (what a waste!) Then we had to go through the hassel of setting up and maintainng cross domain trusts, etc. for the application middle tier and other users. It was a real hassle.
October 28, 2008 at 8:37 pm
Have you considered removing the BUILTIN\Administrators login to the SQL server? This will stop any users with local or domain admin from connecting to the database server with sa rights. First implement an AD group, give your users' windows IDs membership of that group and give the group login access to the SQL Server and db_datareader role in the database. That lets the users connect and query via query analyser etc.
If the app was programmed according to security best practice, then all data access would be done via stored procedures, and you just need to grant execute on the stored procs to the users who need to run them as part of their jobs. Best of all, write it as n-tier and have the application server connect to the database on behalf of the end users and they would have no possible reason for requiring update access. Also, give them reporting services and they will need no direct database access at all.
My guess as to why it's a bad idea to let the users have update access to the data is lack of accountablility/audit and quite possibly a lack of referential integrity within the database (guesswork on my part because the reasons aren't stated). I am deeply thankful that I don't have to support this database :hehe:
Finally before anyone says you can't remove builtin\administrators because admin access may be needed for other databases (aaarrrggghh!), repeat the same analysis for other databases and reduce access to the minimum required; if there are any particularly 'stubborn' ones which really do require 'sa' then consider isolating them in their own instances and not doing business with their vendors again until the vendors have learned to practice the principle of least privilege. And of course do all the analysis and put the new groups in place before you take out builtin\administrators 🙂
I work for a bank; one of our hardest and fastest rules is that we don't accept applications which 'require' sa privileges to operate them. We have been known to force vendors to fix their security model before we will take such an application. In that way we avoid a lot of grief.
October 28, 2008 at 11:38 pm
He is only concerned about events outside the application. You can control access for users using groups. If the member is a member of any group that has a denied access then he will not have access it does not matter if he is in the administrators group (domain, local or not) access will be denied because deny trumps everything. Since the application connects with sa credentials the application will work. As for others users they will remain unaffected because they are not in the group that is being restricted.
I control all access to databases with groups (read group, RW group, Read write execute group) for each database. These groups are for the one offs that run queries outside of the databases application. No users has access to the server but they do have access to the database. This reduces the chances of a users deleting a file and or unwanted applications installed on the server.
In god we trust all others must sign.
October 29, 2008 at 12:24 am
Dear Forum Members,
First let me thank everybody for your overwhelming response for my post. Special thanks to Julie and Dennis as it seems to be the kind of workout they have provided, is suitable for our project.
Basically I am least bothered about what are other the applications which are running and connecting to SQL Server in the end-user’s System. However in practical circumstances there will be only and only our application which will be using SQL Server (and even in the worst case nothing else). So this way, I am quite sure that if someway I can protect my database the rest is done. As Julie and Ray have asked the reason for protecting the database, the sole reason is “integrity of data”. You can trust the client, who is the owner of the application as well as the database (and of course he has rights to check the data), however you cannot trust the people who are getting affected by the application. Most of the places where our application is deployed have a small business setup where the end-user and owner is the same person, so they have to switch there roles frequently :D.
At last but not the least, I wanted to know whether there is any adverse effect (known OR unknown) on the SQL Server Instance, if I remove the BUILTIN/Administrator from the instance?
Please consider environment before printing this message 🙂
October 29, 2008 at 1:59 pm
You need to test thoroughly before removing builtin\administrators. If any service accounts have been given local or domain (ouch!) admin but no specific access to the instance, then they will be getting in as part of builtin\administrators and if you remove that without first finding those accounts and giving them the level of access which they need (which may not be admin) via other explicitly-defined AD groups then you can expect something nasty to happen.
An example from our experience; we discovered the hard way when we removed builtin\administrators from a cluster that the cluster service account required signon to the SQL server in order to test that it was still up. It only needed public access but was getting sa via builtin\administrators. To further add to the fun, there was no immediate effect when the builtin\administrators was removed, but 2 weeks later when the server was taken down for maintenance there was a lot of 'excitement' at 3am when the instance failed to come up again......
It's not uncommon for accounts to be given local admin access to save the time etc of working out exactly what they need, and it's when you try to tighten security that these things crawl out of the woodwork. Far better to do the analysis thoroughly at development time and avoid granting admin access to your server or to SQL unless there really is no alternative.
Good luck!
October 29, 2008 at 3:57 pm
Not something i would recommend. tread lightly.
October 29, 2008 at 4:08 pm
I was personally not keen on it either but it is standard practice in our company and we had a tried and tested procedure to follow. I cannot emphasise too highly, analyse and test thoroughly before you do it, but it is worth doing if you are serious about securing your server. You will probably need an AD expert as well as a DBA.
(In case you're wondering, the reason we had problems despite having a 'tried and tested procedure' is that there was a misunderstanding between the DBA who did it and the one he consulted, and the message didn't get through that we were working on a cluster. Murphy's law strikes again)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply