UPDATE: Since the time of the post, I have determined that my original suspicions were incorrect. So here is an update with what I've learned. The original post is at the bottom. I always try to make my articles as short as possible, but this is just one of those ones that requires a lot of explanation.
I had a problem using an Integrated Security connection string to connect to SQL Server 2008. This was in a SharePoint farm in which I was developing a custom SharePoint solution, but it applies to any ASP.NET application. The connection happened to work when I switched from "Integrated Security=SSPI" to "Integrated Security=True", but that was only coincidence. Those two values have the same effect.
I was actually experiencing two problems at the same time. First, Internet Explorer's Enhanced Security Configuration (IE ESC) was interfering with authentication in one situation. And second, I was experiencing a "double-hop" problem in another situation. If you don't know about NTLM double-hop, learn about it, because it's very important in Windows environments, especially for web apps. Basically, with NTLM, which is your standard Windows authentication, you are only allowed to transfer your Windows credentials out to one machine - a single hop from one machine to another. If an app attempts to transfer your credentials to another machine - a double-hop - you get the dreaded "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'".
So, here was the setup. I had three machines involved. Server A was a SharePoint WFE hosting Central Administration. Server B was an SQL Server (2008) hosting the SharePoint databases, and I was also using it for my custom SharePoint solution's database. And finally, I had a desktop client that I used just to browse/test SharePoint.
Now, most are probably having trouble with the ANONYMOUS LOGON thing, so I'll explain why that failed first. Let's say I'm on the desktop client. I browse to SharePoint (or some ASP.NET web app), and I log in - that's one hop. Now, the custom aspx page is doing a connection to the database. In this scenario, it's connecting as the logged-in user, me, using Windows Integrated Security and NTLM. So when it initiates the connection, there is a hop to access the SQL Server. You can't send Windows credentials on a second hop in NTLM, so the connection is made using ANONYMOUS LOGON. And of course, he has no permission to anything, and so the login fails.
Again, this same issue is likely to occur in any web app connecting to an SQL Server database using Windows authentication under NTLM, which is your default Windows authentication protocol. You either have to change to Kerberos protocol (hard, more secure, more flexible), or switch to SQL authentication on your database (easy, less secure).
As for the IE ESC issue, that was making things really tricky, and it is part of what led me to believe that there was a difference between "Integrated Security=SSPI" and "Integrated Security=True". On this one, I'm not very sure what is going on, but if you have ESC enabled, it seems to block the delegation of the Windows credentials to the second machine. You see, I was on Server A, the SharePoint WFE w/CA, and I was testing my custom add-in there. Over time, I found that if I disabled ESC, everything worked just fine. I couldn't understand why I didn't get a double-hop here too, but I think it's because I was on the same machine as the web app. So watch out for IE ESC, and it's probably best to test on a different machine anyway, because that's how the security and everything is designed to work.
ORIGINAL POST BELOW
I've been experiencing a problem using an Integrated Security to connect to SQL Server 2008. On server A, I have an aspx page that is trying to connect to an SQL server on server B. The connection is created via the SqlConnection object. When I use an SQL authentication mode connection string, everything works fine. But when I use a Windows authentication connection string, using "Integrated Security=SSPI", I get the error "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Token-based server access validation failed with an infrastructure error."
What finally solved this problem is changing the connection string to "Integrated Security=True". Once I did that, everything worked fine.
Now, I did a lot of digging, and I could not find any explanations about why this happened. According to the SqlConnection documentation, "Integrated Security=SSPI" is equivalent to "Integrated Security=True", but obviously that's not true.
One thing I would like to mention is that these servers are part of a SharePoint farm. That makes this a different situation from the usual "login failed for NT AUTHORITY\ANONYMOUS LOGON" problems. The two WFEs are connecting to the SharePoint databases just fine, and the application is a custom application page running in the Central Administration site. The service accounts for everything including the web application identities are domain user accounts, and so we know that those user accounts are able to connect to the remove SQL server, and this application page should be able to as well. This information is what made me decide to try twiddling the Integrated Security value.
I wonder what's really going on here... SqlConnection must be handling the two options differently, but why is it failing with the one? Perhaps something is misconfigured, and it just happens to work with the method that SqlConnection uses for the "Integrated Security=True" connection string.
Comments