Is SQL Server/Windows integrated security good for anything?

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.

Braze

You cannot avoid systems coupling. If you develop server solution it supposed to be deployed and server normally supposed to be configured to handle it. There is nothing abnormal. – Armament

Its only a bad thing if you don't know your client base, which is also a bad thing. – Calces

After reading all your comments (yes I did) it seems more than asking for opinions you want a confirmation of your own preconceived notion. Have you really tried to grok the points others are making? – Thickset

Yes. What do you think my preconceived notions are (other than as stated)? Other than user verification, what are the benefits of integrated security? (So far we have "it's easier for me to hand off responsibility to someone else", which is a legitimate goal for some, esp. in 100% windows org.) – Braze

Thanks for reading the comments, by the way. Here's a list of my preconceived notions (I prefer "preferences" :)) that might apply here: Decoupling or lightly coupling between systems; matching responsibility with authority; avoiding proprietary vendor lock-in; – Braze

Microsoft technology in general (when sufficiently interoperable); AD for network administration; SQL Server; MVC from whichever vendor working against your choice of RDBMS. – Braze

So you can discuss which (if any) of those, or any other of your notions (preconceived or otherwise), gets crossways with Integrated Security. – Braze

Answer 1

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.

Steck

Answer 2

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.

Calces

!st paragraph - see all previous objections. Second paragraph - SQL Server has groups too, and they are very unlikely to coincide with AD groups. And why should AD administration be coupled to SQL Server administration? – Braze

It doesn't need to be but the reallity is that in a Microsoft shop everyone is going to be in groups already, so why not save yourself (or your whining admins) the hassle of maintaining another set of groups? – Calces

Answer 3

I guess it basically down to "not reinventing the wheel" and taking advantage of the "many eyes" effect.

Using Windows authentication you leverage the power of Windows integrated security, on top of which you can add your own stuff if so you wish. It's an already matured system which has been tested millions of times, sparing you the effort (and on your clients, the cost) of making your own mistakes and discovering/solving them later.

And then plenty of people are constantly scanning the Windows authentication process, checking it for vulnerabilities, exploring ways to bypass it, etc. When a vulnerability is openly disclosed and a fix for it gets created, your application just got a "free" security enhancement.

In my current work we have AD groups as SQL logins, so we assign SQL permissions based on membership to AD groups. So all members of the sys engineering group have some permissions, the DBAs have other, normal users others, supervisors others, etc. Adding new users or changing their permissions is a simple thing to do, only done once at AD and they immediately get the permissions they should get at the database.

Post Edit:

Expanding a bit on the "reinventing the wheel": To an AD account I can deny the right to login to a specific machine - or lock it out of everymachine save one or two. I can stop them from loging in at more than 2 workstations at the same time. I can force them to change passwords after some time, plus enforcing some minimal strenght in them. And some other tricks, all of which improve security in my system.

With SQL S. users, you've got none of this. I've seen people trying to enforce them with either complicated SQL jobs or a sort of home-brewn daemon, which in my opinion is reinventing a wheel already invented.

And then you can't stop user SA (or a privileged user) loging in from any machine. I was told once of a clever way to stop a brute-force attack over a SQL S. which had its port for remote login open over the Internet - administrators of the site implemented a job that changed SA's password every half an hour. Had it been SQL + Windows, they could've simply said they wanted administrator to login only from certain boxen, or outright use only the Windows authentication, thereby forcing anyone to go thru the VPN first.

Thickset

No wheel is being reinvented. SQL Server permissions already exist; and a structure of permissions is going to need to be constructed in any case, since it's highly unlikely (and dangerous to assume) that existing AD permission structures coincide with yours. – Braze

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.

Kostroma

" but he can also connect via any other means " - true, but if your app is written defensively, this wont matter. "Security In Depth" - every part of your system should assume that all other parts or stupid, dangerous and untrustworthy – Calces

Another way to solve this problem is thru a 3-tier architecture: client app uses Windows authentication, then communicates to a middle tier which does query SQL Server. Mid-tier can be either SQL S. or Windows account - you've already minimized exposure of the server. – Thickset

Plus, being a DBA, I often think of the ability to easily log to the server thru Query Analyzer, SSMS, Excel, perl, etc. to be a feature, not a bug ;) Of course, I have good reasons to need such a feature. – Thickset

You can run your app as a user account and then give that user account access to the database -- no password for the app to keep track of. – Joellejoellen

Answer 5

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

Portend

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.

Tumpline

Except for the common user-name verificati0n, which I've granted, it seems like trying to map database permissions with the AD roles normally appropriate within a corporate environment just don't match up very well. Might as well use SQL Server roles, no? – Braze

I wouldn't think you'd want corporate security administration mingled with database GRANT administration. They're pretty much unrelated; so the AD person would need to receive guidance from an IS database person anyway. Why not cut out the overhead? – Braze

Answer 7

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.

Philippopolis

Please read the question again. The question is not about login authentication. – Braze

"Is SQL Server/Windows integrated security good for anything?...why not just go with the simplest possible database roles?" answer: Security. The question was not worded all that great, but I think he was getting at both authentication and authorization. I talked about authentication. – Philippopolis

If someone is intercepting the wire between your app server and your database server, you have bigger problems. The data are not encrypted by default, for performance reasons. Your data are almost certainly more important than your db credentials. – Rochette

As someone who intercepts data between app servers and database servers, I can tell you that I would much rather have your credentials than a stream of data, none of which may be interesting at any given point in time. – Philippopolis

Answer 8

It has been my experience that the response time of queries run against a database from most applications are significantly faster when connecting via SQL authentication. Removes the delay that occurs from constantly asking AD for security validation.

Performance wise SQL authentications >> windows integrated security.

Herakleion

Answer 9

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.

Fletcherfletcherism

Interesting observations, but they don't address the question. – Braze

Thanks. And agreed. And I've now made a new effort. Better? – Fletcherfletcherism

The database permissions is where I'm suggesting the responsibility should lie, without coupling it to AD. – Braze

I like coupling to AD. Single signon. Single password management. Users don't share passwords if they only have one. Solid challenge response protocols. AD can be distributed to avoid SPOF. AD can authenticate with biometric or code generators. I think AD might be underrated. – Fletcherfletcherism

Answer 10

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.

Doss

I agree that's a resulting benefit, but that problem could have been addressed more directly. Is it good for anything else? – Braze

@Orion Edwards: it has the big downside of not using connection pooling. – Tibia

Answer 11

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.

Armament

Answer 12

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.

Abortive

But that's a user network login/access issue, and doesn't require integrated security. – Braze

There is a scenario however that the user might not be logged into the domain, but have the tools on his/her computer. In that case if they are outside the permitted hours of operations, they can't connect. If they are using SQL login they can. – Abortive

While I'm not thrilled about the coupling to AD, for development purposes it's easy to manage...for application deployment I prefer SQL Logins. – Abortive

Answer 13

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.

Stoltz

Answer 14

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.

Kaenel

We've already conceded authentication, right from the start. You are who you are. The question is about authorization. No mark-down because you've clarified the fundamental question (and, I think, answered it with "No, other than single login." – Braze

Not a markup for the answer yet, either - but you've crossed the threshold for discussion. – Braze