SQL Server 2008: should I be using Windows auth or SQL Server auth?

I have an 开发者_Python百科MS-Access 2007 front end. I will have multiple users on it. They are all going to be on the network company domain. Should I be using Windows authentication or SQL Server authentication to be connecting to SQL Server 2008 via ODBC?

---

Windows auth will allow a more seamless authentication process, single sign on!

From the below article:

Disadvantages of SQL Server Authentication

• If a user is a Windows domain user who has a login and password for Windows, he must still provide another (SQL Server) login and password to connect. Keeping track of multiple names and passwords is difficult for many users. Having to provide SQL Server credentials every time that one connects to the database can be annoying.
• SQL Server Authentication cannot use Kerberos security protocol.
• Windows offers additional password policies that are not available for SQL Server logins.

Advantages of SQL Server Authentication

• Allows SQL Server to support older applications and applications provided by third parties that require SQL Server Authentication.
• Allows SQL Server to support environments with mixed operating systems, where all users are not authenticated by a Windows domain.
• Allows users to connect from unknown or untrusted domains. For instance, an application where established customers connect with assigned SQL Server logins to receive the status of their orders.
• Allows SQL Server to support Web-based applications where users create their own identities.
• Allows software developers to distribute their applications by using a complex permission hierarchy based on known, preset SQL Server logins.

Here is a good article on the pro's/con's for each. http://technet.microsoft.com/en-us/library/ms144284.aspx

---

Dustin hit on a good list of pros/cons. I know nobody wants to make a decision like this for you, but based on your criteria (multiple users, all on company domain); I can't think of a single reason to use SQL Server Authentication instead of Windows Authentication. It was designed primarily for scenarios like this.

---

If you have a Domain, and Active Directory, you should not even think of something else than Windows Authentication .... not a single second!

---

Like many things when it comes to security implementations what you should do depends on your goals.

I am not sure from your question if you intend to pass each user's credentials to SQL Server or if you plan on having a single login using either Windows Authentication or SQL Server Authentication. If you choose the later option SQL Server Authentication is probably the way to go as it limits network accounts. If you choose the former Windows Authentication is probably better for your users as it provides single sign on.

As an aside my personal preference is to have each application use its own set of credentials for database interaction. This limits users to only having the privileges provided to them by the application. In this manner your users needn't worry about authenticating to the database as your application will take care of that for them.

---

Seeing as you have a desktop front end that is connecting directly to database...your best option is to go with windows auth. Keep in mind by doing this, the user can bypass the frontend and access the database directly. You'll need to grant them the minimum db permissions required. Also, you'll want to use a AD security group instead of adding each user.

Sql auth is best for when you control the config file...for example a web app.