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

Question

what is the difference between sql server authentication and windows authentication...Is there any specific situation of using each authentication?

Answer 1

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.