SQL SERVER Authentication

You can choose between Windows Authentication and SQL Server Authentication. Windows Authentication will always be available, even if you configured it as SQL Server Authentication. Logins using usernames and passwords that are local to SQL Server (not part of a larger Windows network) are acceptable to the system only if you specifi cally turn on SQL Server Authentication.
Windows Authentication
Windows Authentication is just as it sounds. You have Windows users and groups. Those Windows users are mapped into SQL Server logins in their Windows user profi le. When they attempt to log in to SQL Server, they are validated through the Windows domain and mapped to roles according to the login. These roles identify what the user is allowed to do.
The best part of this model is that you have only one password. (If you change it in the Windows domain, it’s changed for your SQL Server logins, too.) You don’t have to fi ll in anything to log in; it just takes the login information from the way you’re currently logged in to the Windows network. Additionally, the administrator has to administer users in only one place. The downside is that mapping this process can get complex, and to administer the Windows user side of things, you must be a domain administrator.
SQL Server Authentication
The security does not care at all about what the user’s rights to the network are, but it does care about what you explicitly set up in SQL Server. The authentication process doesn’t take into account the current network login at all; instead, the user provides a SQL Server  specific login and password. This can be nice because the administrator for a given SQL Server doesn’t need to be a domain administrator (or even have a username on your network, for that matter) to give rights to users on the SQL Server. It even works across domains, or where domain trust relationships don’t exist. The process also tends to be somewhat simpler than under Windows Authentication. Finally, it means that one user can have multiple logins that give different rights to different things.