What are the pros and cons using Windows Authentication in SQL Server 2005 and SQL Server 2008?

I'm considering changing a .NET application from using SQL Authentication to Windows Authentication. I have thought about using Application Role but decided not to use it since the application I'm changing is using multiple SQL databases and Application Role is limited to one database. I want to know what are the pros and cons using Windows Authentication. Also, I have the following specific questions:

1. How does Windows Authentication work when the application is not in the same domain of the SQL Server?
2. What are the common reasons for using Windows Authentication instead of SQL Authentication?
3. Is it recommended that I provide users a GUI to manage the Windows Logins of the SQL Server used by the application instead of requiring users using Management Studio?

I'm guessing since you didn't get a response right away that maybe everyone is using SQL Authentication. We moved to Windows Authentication when we migrated to 2005. Here is what found. Keep in mind our environment has only two environments. One environment is in a DMZ where machine mainly IIS are not in a domain. The second one is in a domain and contains all the SQL boxes, as well as some IIS machine. There is no trust between the two environments. So here is what I know:
1) We where able to use Windows authentication from our DMZ environment to our Domain environment by doing the following. Define a local account on the DMZ machine with a password, then define the a similar local account on our SQL Server machine with the same name and password, then grant the local account on the SQL box to SQL Server. This allowed the DMZ IIS machine to connect to the SQL Server box using Windows Authentication. The IIS web site needed to be set up to use the local account.
2) We went to Windows authentication to improve our security model. With Windows Authentication we defined Windows Groups for every different slice of security needed, and Windows USER Accounts where placed in the appropriate Windows groups to gain access to SQL Server. This allowed people to be dropped in groups or taken out of groups to manage SQL Server Security, allowing no changes in SQL Server to manage these permissions, except to give the Windows Group access. Also when people leave they are just removed in one place AD, and they automatically lose their access to SQL Server.
3) All users and groups are managed through AD so no new interface was needed.
Problems we encountered:
1) Programmers had problems changing their application to not display login screens, and connection strings to use Windows Authentication.
2) Ran into the double hoop authentication problem. This was resolved by creating SPN where needed. Getting this to work was not an easy task. I think if you look out there many people abandon Windows Authentication because of this. This is because there are so many peices that can trip you up. We ran into a number of these, and fixed them all.

Good luck, if you have more questions feel free to drop me a note.

Hi Greg,
Thanks for the reply. In order to understand your reply better, I need to figure how to register SQL Server 2005 to Active Directory. If you can provide me some information or links in how to do that, it will save me some times.

I didn't register SQL Server 2005 to AD. Possibly first post was a little misleading. My comment basically was stating your can trick connections across domains to use windows authentication by using local accounts. So say you have two server SERVERA and SERVERB, where SERVERA is in one domain and SERVERB is in another domain, and SERVERB is running SQL Server. If you setup a local account on SERVERA named "SERVERAUSER1" with password "$$Passw0rd" and then setup an local account on SERVERB with name "SERVERBUSER1" with password "$$Passw0rd". So now you have two local accounts one each machine with the same login name "USER1" and the same password. Now go ahead and add a logon for "SERVERBUSER1" on SQL Server on SERVERB. If you then log in to SERVERA with the local windows account "SERVERAUSER1", and try to connect to SERVERB using Windows Authentication it will work. Keep in mind this is only allowing local users to use windows authentication this way. I'm guessing if you want to really pass domain accounts across then this will will require something totally different then I was discuss.
Here is an article I wrote about needing SPNs to support linked servers when using Windows authentication.
http://www.databasejournal.com/features/mssql/article.php/3696506

Does this help at all?

Hi Greg,
Thanks for the detail explanation.
Here is my understanding about the AD and SQL Server in your environment:

1. AD is used to manage Windows Groups/Users.
2. Windows Groups/Users are added to SQL Server as logins.
3. Access Controls of the logins are managed in SQL Server.

The confusion that I had earlier is that I thought your SQL Server boxes are registered to AD and you manage the access controls of the logins in AD instead of in SQL Server. Since I have never registered SQL Server to AD, I don't know what one can do to SQL Server thru AD.

Your summation is accurate.

1) The windows authentication would not allow your application to connect to SQL Server across different domains. If you need to connect across domains SQL Server authentication would be a better choice.
2) Most common reason is ease of administration for DBAs.
3) Yes, that would be better.

You can "kind of" use Windows Authentication across domains, see my prior post how we did it.