January 22, 2008 at 4:22 pm
We have an application that uses Windows authentication to the database. Logins are created for every new user added. External users will also be accessing this application (we have an external domain). External users will access the application through ISA via the web. I'm trying to consider all the factors (security implications/maintenance/etc) and I was hoping someone could help.
I'll give three examples:
1) When you use Windows authentication, users receive direct access to the database. When you use a service account, users have no access to the database unless they are using the application.
2) If you don't use AD groups, the logins could get cluttered with accounts (and then you have to factor in how to maintain those IDs and delete them when users don't exist anymore, etc)
3) When you move the database from one server to another, you need to create all the logins before the database is restored or else they will be orphaned.
What other implications are there or am I worrying over nothing?
January 22, 2008 at 5:23 pm
i'll use windows authentication and create AD groups. grant access to groups not individual accounts
August 7, 2013 at 6:17 am
Hope this helps give some insight to your dilemma:
In my opinion it is always best to use Windows Authentication unless it is absolutely necessary.
Better still, I like to create AD user groups per system/application/database. Usually it involves creating 3 groups. Namely: Admin (db_owner), Read only and Read/Write. In this way if a user needs access to a database they can be allocated into the correct group in AD and nothing further needs to be done inside SQL Server.
The beauty of this is that it reduces your SQL Server maintenance quite dramatically, for example: if a person leaves the company they are removed from AD thus the user is automatically removed from the relevant databases meaning less maintenance to clean up old users etc.
This method also helps to reduce the amount of users that are listed in your database or within the SQL Logins again creating an environment that needs less support.
Another advantage is your SQL Admins do not have to get involved when new users need to be given access to the databases. The AD Admins add the new users into the relevant AD Group, nothing needs to be done within SQL Server at all.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply