Is SQL Server/Windows integrated security good for

Question

The distinctions among Windows user permissions and any set of SQL Server GRANTs seem like unrelated concepts. As often as not, it seems to actually be implemented with pseudo-logins for database roles; but that doesn't map usefully back to Windows permissions. Assuming single-login identity verification, why not just go with the simplest possible database roles?

EDIT:
So far we've picked up the single benefit that you don't need to store a password in your application; but that seems more like a trivial beneficial consequence than a design goal; there are lots of other more direct ways to achieve that, without closely coupling the entire security apparati of both universes.

EDIT AGAIN:
Doesn't anyone else have any benefit to suggest, other than single login and ability for SD to maintain groups, thereby duplicating the capability for groups (based on the same user login) already existing in SQL Server?

The group issue has several flaws, including the assumption that the AD manager is assumed to be equally qualified to maintain both; and it excludes any network connections that aren't part of AD (thereby locking you into MS technology.)

And to put it in best-practice terms, you've built in coupling of systems, which is generally conceded to be a Bad Thing.

Answer 1

I think the integrated security is good if it is used properly. For some reason I can't understand, a lot of companies I have worked in don't utilize the AD, the SQL permissions and the IIS security model very much.

If you had to design the SQL Server permission system, with the key requirement that it was integrated into AD, you would probably come up with something very similar. IMHO.

I like to group users into AD groups and then create group logins in the SQL Server with the various permissions. People should not have more access to data just because they have tools to connect to the database. They should have the same permissions on the data no matter how they connect.

Guest users (as in anonymous web-users) should be in an AD group of themselves, as per recommendations on IIS configuration. Giving this group only access to what they should have access to in the database could one day save the data from disaster. It is hard to read source code to find out if data is protected, much easier to survey the database permissions and the security configuration.

Also, non-integrated security is bad because the passwords always gets distributed, put into config files etc.

Answer 2

yes of course, If you have your application level data access layer running as a service, you can use integrated security to talk to the databasem using an Application "Service Account" to log in to the server... Then you don;t have to store user passwords in the applications config file, and you are not passing passwords over the network fir each new connection made to the database

Answer 3

Integrated security gives greater flexibility in user access IMO. For example if my organization wants to limit the hours during which the developer group can access the server, integrated security is my best bet.

But it's not right for everything.

[EDIT] Also it's great for logging access. If I had to create a SQL login for each new developer in a large organization...it would probably stop happening and logins would get shared, then I'd never have confidence in the ability to point the finger at the knucklehead who dropped a table.

Answer 4

Since everyone else has discussed the benefits of Windows Authentication, I guess I'll play Devil's Advocate...

Allowing the account 'Joe User' to have access to the server means that not only can be connect with your app, but he can also connect via any other means (SQL Tools, Excel, malware, etc.).

With SQL Authentication, your app can run as a certain user and then the app can handle the database access. So when 'Joe User' runs your app, the app has SQL access... But 'Joe User' himself doesn't, which means that the aforementioned apps wouldn't be able to have implicit access to the database.

Answer 5

When using integrated security your app doesn't need to know anything or handle anything about security, also, if the user is already logged in to your domain they don't need to log into your app as well (assuming you've got impersonation set up correctly).

The biggest advantage has to be using an AD group as a login in SQL Server. This way you can give access to everyone in the "Accounts" group access to a set of sprocs, tables etc and everyone in the "Managers" groups access to a different set, all with AD. Your system admins don't have to jump into Management Studio to give users access rights to your app.

Answer 6

Integrated security is only really useful for intranet apps. The pseudo logins I've seen are mostly for internet web applications.

Anyway, It's more than just not storing a password in your app, since hopefully you'd be salting and hashing your password anyway. There's also:

1. The user doesn't have to log in, which is a big deal, if you are jumping into a webapp sparadically throughout the day, or work somewhere that has multiple internal webapps.

2. User management is free, since the IT admin only has to edit the user in Active Directory.

3. User names and Role names are consistent throughout the organization.

4. User impersonation is a more secure method when accessing an internal webservice. (for example; an internet website accesses an intranet webservice)

5. The web application doesn't need to do anything extra user authorization on the database, since it's all handled seamlessly.

6. [EDIT] You also know the user in your database objects. So you can have a view only return rows associated to them. (unless you create a new SQLServer user for each app user, this would be impossible, and creating a new SQLServer user for each app user is also unreasonable)

Integrated security isn't right for everything, but for the enterprise, there's a lot of value add.