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

SQL Logins: Obfuscated cleartext passwords over the wire

Windows Integrated Login with NTLM: Hashes passed over the wire

Windows Integrated Login with Kerberos: Proper secure authentication.

There is only one choice if you care about security.

Answer 2

Disregarding the grant table/etc side of things, the login side of things is very useful, because your app can connect to SQL server using windows authentication, which means

You don't have to put your database username and password in a file in your application somewhere

Any time you put a password in a plain text file, that's a security risk. Avoiding that is great.

Answer 3

How about the fact that if you use sql server authentication, the password information is sent accross the network as clear text. Integrated security doesn't have that problem.

Answer 4

Many of these have been said or are similar to previous answers... With AD integration:

a) I don't have to worry about the users who have access to any given application, I can pass that off to the security guys.

b) I can restrict access at a table by table level based on groups that already exists, as well as forcing standard users to only have the ability to call stored proc's.

c) When a developer leaves my group, we don't have to change all the DB passwords (i.e. if you care about data security...)

d) It's easy to do custom logging based on the user who makes the change. There are other ways to do this, but I'm all about being lazy.

e) Integrates with IIS authentication. If you're already using IE & IIS for your intranet, this just makes life a lot easier.

Note: There are far more reasons not to use it, and I never used it before my present position. Here where everything is lined up in AD already... It's just the easiest time I've ever had with database security.

Answer 5

Database may have a fine grained access with lots of settings different for each user. It is not only scenario where you have one user for application access and that is all data security.

If we are talking about team development then probably there will be user group granted development access to database. Each user in this group will be member of your internal domain and have own passwords which database admin not supposed to manage and even know to allow access to database.

Answer 6

For an enterprise application which will run in an AD environment, using Windows integrated security is definitely the right approach. You don't want users who are already authenticated in the environment to have to manage a separate set of credentials just for your app. Note we are talking about authentication... for authorization you would still use SQL server's role based security.