3

Some background first:

The problem described below wouldn't exist at all if the database in question would have been built with a DBA's mindset:
Data access only through views and stored procs --> the user is able to use the application without having any permissions on the table.

But the database in question was built with a "developer's mindset", so the app sends INSERT, UPDATE and DELETE queries to the server --> it needs an account with sufficient permissions to be able to access the database.

Apparently everyone agrees that Windows authentication is more secure than SQL Server authentication, for example here:

My question:
Is this still valid when the database was built with a developer's mindset, like described above? (when the users needINSERT/UPDATE/DELETE permissions on the tables)

Why I'm asking this:
Our main app is an MS Access frontend with a SQL Server database. At the moment, we are using Windows authentication, but I'm considering switching to SQL authentication.

The problem I'm seeing:
Everyone connects to the database with his own Windows account.
So in order for the application to work, the Windows accounts of everyone and their dog need to have INSERT/UPDATE/DELETE permissions on the tables.

This means that literally EVERYBODY here is able to just create a new Access database, link a few tables from our main database and edit (or delete) them.
And yes, we have a few power users who really know how to do this.

From that viewpoint, it's hard for me to understand why this security threat is apparently not considered when recommending Windows authentication over SQL authentication.

To me, the possibility that anyone can just edit or delete tables with his Windows account screams insecurity.
On the other hand, the hypothetical danger that someone outside the IT department could get a clear-text SQL Server auth password from a config file is quite unlikely.
(given that there are no clear-text config files lying around on the users' machines - which is the case at our company)

As I said in the beginning - I know that this problem only exists because our app is written in a way so that the users need INSERT/UPDATE/DELETE permissions on the tables.
But then, there are a lot of databases like this out there...and I still see only recommendations that Windows auth is always more secure than SQL Server auth.
IMO, the answer should be (as always) "it depends", because you can't really tell without knowing more about the database in question.

So, does no one else see this problem or am I missing something?
Or is there a way to use Windows auth without all users being able to delete data?
(besides changing all data access to stored procs)

Improve this question
3
  • 2
    I fail to see how the stored procedure would prevent anything. If the user connecting to the database can call the stored procedures through the MS Access, then he/she can very well call them "manually" as well (and I don't see why this would be related to Windows Auth - if you had a SQL Server user for each and everyone, you'd have exactly the same situation). If a user shouldn't be allowed to delete data, then revoke the DELETE privilege.
    – user1822
    May 13, 2013 at 22:21
  • 1
    Your 'app' should have a windows username/password which sends the commands to the DB, not each user using the app. Unfortunately it's a design flaw.
    – Ali Razeghi - AWS
    May 14, 2013 at 5:37
  • @a_horse_with_no_name: Of course the user could call the stored procs manually, but he can't DELETE * FROM Table when there is no stored proc that deletes the whole table. And I'm not talking about creating a SQL Server login for each user...I'm talking about creating one login for the app which would be used by all users.
    – anon
    May 14, 2013 at 14:29

1 Answer 1

Reset to default
6

In my view Windows Authentication is still more secure. Just.

With windows authentication you can allow a group of people (via a Windows Group) to have access to the database. Only those users can use an application, any application, to connect to the database.

If you use a SQL Server account then anyone who knows the credentials (and invariably these always leak out) can connect to your database. Again with any application.

You are absolutely correct that with Windows authentication can connect directly to the database and issue any query they see fit - due to the model your app requires.

A third option

What I would suggest in this situation is the use of an application role: http://msdn.microsoft.com/en-us/library/ms190998.aspx

The problem you have here is you have to be able to code the application to be able to use the application role - that's not always easy. Afraid I'm not an Access expert to be able to advise here specifically.

The advantage of an application role is that you can restrict which applications can perform certain commands. Its similar to a database role but has a password assigned to it that is needed before the rights of the role can be inherited. Using an application role with a Windows group you could limit the users who can connect to a database and the application that they can use to query the database.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.