what is the differences between sql server authentication and windows authentication..?

SQL Server has its own built in system for security that covers logins and roles. This is separate and parallel to Windows users and groups. You can use just SQL security and then all administration will occur within SQL server and there's no connection between those logins and the Windows users. If you use mixed mode then Windows users are treated just like SQL logins.

There are a number of features of each approach -

1. If you want to use connection pooling you have to use SQL logins, or all share the same Windows user - not a good idea.

2. If you want to track what a particular user is doing, then using the Windows authentication makes sense.

3. Using the Windows tools to administer users is much more powerful than SQL, but the link between the two is tenuous, for instance if you remove a Windows user then the related data within SQL isn't updated.

Ideally, Windows authentication must be used when working in an Intranet type of an environment.

Whereas, SQL Server authentication can be used in all the other type of cases.

Here is a link which might help.

Windows Authentication vs. SQL Server Authentication

If you wish to authenticate the users against windows system users [created by Administrator] then in that case you will go for Windows Authentication in your Application.

But in case you want to authenticate the users against set of users available in your application database, then in that case you will want to go for SQL Authentication.

Precisely if your application is an ASP.NET web-app, then you can use standard Login controls which depend on Providers like SqlMembershipProvider, SqlProfileProvider. You can configure your login controls and your application whether it should authenticate against windows users or app-database users. In the first case it will be called Windows Authentication and the later will be known as Sql Authentication.

I don't know SQLServer as well as other DBMS' but I imagine the benefit is the same as with DB2 and Oracle. If you use Windows authentication, you only have to maintain one set of users and/or passwords, that of Windows, which is already done for you.

DBMS authentication means having a separate set of users and/or passwords which must be maintained.

In addition, Windows passwords allow them to be configured centrally for the enterprise (Active Directory) whereas SQLServer has to maintain one set for each DBMS instance.

I think the main difference is security.

Windows Authentication means that the identity is handled as part of the windows handashaking and now password is ever 'out there' for interception.

SQL Authentication means that you have to store (or provide) a username and a password yourself making it much easier to breach. A heap of effort has gone into making windows authentication very robust and secure.

Might I suggest that if you do implement Windows Authentication use Groups and Roles to do it. Groups in Windows and Roles in SQL. Having to setup lots of users in SQL is a big pain when you can just setup the group and then add each user to the group. (I think most security should be done this way anyway).