SQL Server Authentication
First, I would explain SQL Server Authentication. SQL Server takes care of user management, means that users and their passwords are managed by SQL Server. You can access the user management functionality in SQL Server through the Enterprise Manager (for SQL Server 2000) or the SQL Server Management Studio (for SQL Server 2005).
To connect to a SQL Server instance that uses SQL Server authentication, you need to pass a user name and password in the connection string of your application, ex:ASP.NET Web Application. Connection string usualy looks like this:
Next, Windows Authentication, the OS (Windows) takes care of user management. All interaction with the database is done in the context of the calling user so the database knows who's accessing the system, without an explicit user name and password being passed in the connection string. You still need to map a Windows account to a SQL Server account so SQL Server can determine whether the account has sufficient permissions.
Connection string using Windows Authentication usualy looks like this:
Data Source=YourServer;Initial Catalog=YourDatabase;
Windows or SQL Server Authentication?
In general, it's recommended to use Windows authentication. The fact that you don't need to use a password in the connection string, means your application will be a bit safer. You don't need to send the password over the wire, and there's no need to store it in a configuration file for your application where it can be viewed by anyone with access to that file.
However, SQL Server Authentication is a bit easier to use. Since you specify your own user name and password, you don't need to know the final user account that your application runs under.
Sty - Knowledge is Free