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:
- Secure way to connect: Windows Authentication or SQL Server Authentication and why?
- Does the PCI standard forbid the use of Windows Authentication?
- Any security related reasons to prefer Windows Authentication over Sql Server Authentication?
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)
Best Answer
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.