Login failed for user ‘(null)’. Reason: Not associated with a trusted SQL Server connection.

Another of „It should just work” problems… resulting in:

Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
Number: 18452
Procedure:
State: 1
Severity: 14
LineNumber:0
System.Data.SqlClient.SqlException : Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

I have a perfectly working ASP.NET application working on IIS 5.1:

• Separate boxes for application server and MS SQL 2000 Server
• Web config set as usual:
  • <authentication mode="Windows"></authentication>
  • <identity impersonate="true"></identity>
  • <connectionstrings><add name="Default" connectionstring="Data Source=SQL_HOSTNAMED_INSTANCE;Initial Catalog=DB_NAME;Integrated Security=True;Min Pool Size=5;Max Pool Size=50" providername="System.Data.SqlClient"></add></connectionstrings>
• Authorization is made on two levels, first on web application then on database
• Application deployed as a newly created WebSite

When I switch to a test machine with IIS 6.0 and locally running SQL Server it still works fine. Problems arise when I deploy the application i a production alike environment with separate boxes (both placed in a domain).

A far I have investigated, problem is connected with Kerberos authentication and it’s failure to establish trusted connection due to inability to use delegation. Kerberos need’s to be sure that service performing delegation is authorised to do that, and both sides of connection can be trusted to establish connection in name of the „impersonated by delegation” user.

I have found a lot of very lame solutions, that are just… lame:

• Set your SQL Server on the same machine as your app…
  Oh right, mys DB Admin would like that very much :p
• Connect to your DB by means of SQL Server authentication with username and password set in connection string…
  This one is particulary popular. Funny, all those articles look like copy-paste ;). What a good idea, disregard your finely tuned DB ACLs, and replace them with DB wide one user access, just to be sure that when your app fails during user action authorisation, your app will not fail and you’ll never know there is a problem.

There is a statement on MSDN that I particularly like :p : If your application runs on a Windows-based intranet, you might be able to use Windows integrated security for database access. Integrated security requires: That SQL Server be running on the same computer as IIS. […]. WTF! In my knowledge it’s one of the most commonly used deployment scenarios, it must work.

Solution to this problem is tricky one, I’m not sure if this is the minimal set of actions one need to take for this to work, please feel free to correct me or provide other solution.

1. First we need to setup SQL Service to probably work with Kerberos, that means setting its SPNs, this can be done in two ways:
   1. Run SQL Server in LocalSystem account and let register SPN by itself, from security point of view – bad solution,
   2. Register SPN manually as a Domain Admin,:
2. Now set in Active Directory on application server computer properties set „Trust computer for delegation”
   
3. Then, setup application server IIS website configuration:
   1. Create a new WebSite
   2. On „Directory Security” tab in „Authentication and access control section”, Turn off „Enable anonymous access” and ensure that „Integrated Windows authentication” is on.
   3. If you want for your web application to work inside a custom service account sandbox:
      1. Crate an application pool service account:
         1. Enable it for delegation.
         2. Add it to the local application server Windows group IIS_WP – so it can be used as application pool identity.
         3. Mark the account as „trusted for delgation”
            
         4. Create SPN’s for the account on domain controller:
            setspn -A HTTP/<WEBSERVER_HOSTNAME> <APP_POOL_IDENTITY>
setspn -A HTTP/<WEBSERVER_FQDN> <APP_POOL_IDENTITY>
      1. Create new application pool, based on default one and set it’s identity to the account created above.
         1. For website created above set the application pool. On „Home directory” tab.

One more thing. Kerberos session between you and your web application, must be Negotiated after you have configured everything, if you miss one instruction element, connect, fail then correct your setup, then you’ll fail anyway. Try to log-off and log on again to negotiate new Kerberos session. I lost more that a day because of this 🙁 . Same case applies to Kerberos session between your web application and database, if accidentally run application before application pool account was setup or database spn was setup, you must correct the setting then wait for a few minutes for everything to sync.

I have noticed that there are basically two most probable point’s of failures:

1. Between your browser and your web application server.
   To eliminate this run a browser locally on the application server and access your web application. If you’re getting data then, read pt. 2
2. Between your application serwer and your database service.

I have tested the solution above on environment consisting of:

• Domain with functional level: Windows 2000 mixed.
• Application server: clean Windows 2003 Standard SP1 with:
  • Application server role added (ASP.NET enabled, no frontpage extensions)
  • .NET 2.0 Framework installed via dotnetfx.exe or through Windows Update. No other updates, even critical ones.
• Database server: Windows 2003 R2 SP1 with:
  • MS SQL Server 2000 SP4 installed as a named instance

Things that I found set by default and they are required for above to work:

• Local policy Impersonate a client after authentication by default has IIS_WPG which in turn has your application pool identity.

Reference:

1. Login failed for user
2. Local Policy set Impersonate a client after authentication
3. Impersonate a client after authentication
4. Kerberos
5. Troubleshooting Kerberos Delegation
6. Security Briefs: Credentials and Delegation
7. Administering SQL Server (SQL Server 2000) Security Account Delegation
8. Using Kerberos with SQL Server
9. Accessing SQL Server Using Windows Integrated Security
10. SetSpn.exe a part of Windows 2000 Resource Kit Tools
11. Connection String