SQL Windows Authentication

We currently use SQL 20开发者_如何学Go08 with Windows Authentication disabled, we only allow SQL Mixed for accessing SQL.

We are wanting to switch our version control software to Team Foundation Server, which requires Windows Authentication.

Which ended up being a bit of a problem, I've talked to our DBA and Windows Authentication is not an option, nor is putting SQL on the server where TFS will reside.

Issue is, from what he is telling me, you are unable to just enable Windows Authentication just for a single user, its either on or off for all users.

Is this correct? What other options are there?

You need to have another talk with the DBA.

There are 2 options for authenticating to SQL Server:

• Windows Authentication (only)
• Both Windows Authentication and SQL Server Authentication

Yes it is true that if you turn on the latter option also known as Mixed Mode, and it applies to the server (which will now accept Windows Auth) - it does not mean that suddenly all valid Windows users will get access to SQL Server!

The Windows logins still have to be added as SQL users (SQL login != SQL user[principal]) and they can then belong to roles etc, so you would only need to add the users for TFS.

In fact, you cannot possibly disable Windows Auth - so I don't see why the DBA should be complaining about "turning on" Windows Auth.

• Add a new domain group "sqltfs"
• Create a SQL user out of the Windows security Group "domain\sqltfs"
• Grant all the access required to the new user (or indirectly through a role)
• add all the TFS users into the domain group "sqltfs"

This way you can all continue to use SQL logins where it matter (existing code), while still gaining access via TFS -> SQL Server through the domain group membership

This probably belongs on superuser, but I'll take a stab at it. From my SQL 2008 R2 management console, the DB server can run in either pure Windows Auth mode, or mixed Windows auth + SQL auth. It can't run in SQL auth only.

Since you say you're in mixed mode, I can't see why he can't add another user to the DB that uses a windows domain account, rather than a local SQL server account. Mixed mode allows both.