SQL 2008 + IIS Application Pool: Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path

1
votes

I´m having trouble connecting to SQL Server 2008 R2 Express with Northwind database using ApplicationPoolIdentity in IIS 7.5, resulting in the following error message:

Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. The connection will be closed.

notes:

  • I´m using Windows 7/VS 2010/IIS 7.5/SQL Server 2008 R2 Express (all in the same machine).
  • my site is using "ASP.NET v4.0" application pool;
  • ASP.NET v4.0 application pool is using ApplicationPoolIdentity as Identity;
  • the SQL Server 2008 R2 express has the Login "IIS APPPOOL\ASP.NETv4.0" with the server role sysadmin granted;
  • the Northwind database has the user "IIS APPPOOL\ASP.NET v4.0" configured with the following permitions: db_accessadmin, db_backupoperator, db_datareader, db_datawriter, db_ddladmin, db_securityadmin;
  • the w3wp.exe process is running with the user "IIS APPPOOL\ASP.NETv4.0"
  • I can see that the user profile folder is created (C:\Users\ASP.NET v4.0);
  • the same problem occurs using the application pool "DefaultAppPool".

If I change the ApplicationPoolIdentity user to another user that has rights to access the database it works.

Additional informations:

connection string: 

<connectionStrings>
    <add name="NorthwindEntities" connectionString="metadata=res://*/NorthwindModel.csdl|res://*/NorthwindModel.ssdl|res://*/NorthwindModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=NOTEBOOK\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />
</connectionStrings>

Could anyone help me to solve this issue?

Thanks in advice.

1
asp.netsql-server-2008iis-7.5sql-server-2008-r2
Can you post your connection string (make sure you XXXX out any passwords). – Kev

1 Answers

1
votes

It looks like you're trying to use SQL Server Express's "user instancing" feature, i.e. your connection string looks like:

AttachDBFilename=|DataDirectory|xxxxxx.mdf;User Instance=true

This won't work for ApplicationPoolIdentity accounts in IIS7.5. For more information see this MS Knowledgebase article:

Problems with SQL Server Express user instancing and ASP.net Web Application Projects

Web applications built with Visual Studio 2005, Visual Studio 2008, or Visual Studio 2010 and that rely on user instancing with either SQL Server Express 2005 or SQL Server Express 2008 do not work with the new application pool identity. These products were developed and tested against application pools running with the older NETWORK SERVICE account.

Also Microsoft discourage using this feature now:

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

If you've not already done so, I would attach the database to SQL Express and use a regular SQL connection string instead, just like you'd do in a production environment. You may need to enable TCP/IP for your SQL Express instance using the SQL Server Configuration Manager if it's not already enabled:

enter image description here

And ensure that port 1433 is configured as well on the next tab of that dialogue.