asp.net via kerberos integrated windows authentication to sql server

Answer 1

What you are describing should work without issue.

Your connection string should look like:

<add name="IntegratedAuthConnectionString" connectionString=
 "Data Source=DATABASEINSTANCE;Integrated Security=True"
 providerName="System.Data.SqlClient"/>

Your web.config should also specify windows authentication

<authentication mode="Windows"/>

Create a test page which dumps everything from the Request.ServerVariables collection and ensure that you are seeing the authenticated username and domain. Something along the lines of the code below

foreach (string s in Request.ServerVariables)
{
    Response.Write(s + ": " + Request.ServerVariables[s] + "<br />");
}

Answer 2

Download the Troubleshooting Kerberos Delegation. Its a life saver.
At page 17 you'll find the Active Directory Checklist. Follow the checklist step by step.
At page 30 you'll find the Client Application Checklist. Verify the checklist step by step.
At page 35 is the Middle Tier Checklist. Verify it step by step.
At page 48 is the Back End Checklist. Verify it step by step.

If you still have problems, the document contains a detailed list of troubleshooting tools (kerbtray, klist, ldifde etc), detailed explanation on how to enable logging and auditing of authentication errors in the system event log, detailed explanation of all error codes in the event log entries from the authentication audit and so on and so forth.

Once you figure out what is wrong, it will be much easier to fix.

Answer 3

The answer ultimately DID lie in the SPNs.

This system was set up on our test site, which is test.ourcompany.com, so i needed to add a new SPN for that reference. So in addition to the following:

HTTP/server 
HTTP/server.ourdomain.com

I also needed to add an additional one for:

HTTP/test.ourcompany.com

Geez, what a battle! At least it's over! :)
Thanks for the help guys!