Member Avatar for Ycefire

Hello,
I'm using Windows 7 Ultimate 64bit. I'm on admin account and all I want is to develop my ASP.NET pages on my main system - not only on virtual machine with XP istalled.
The problem is that I cannot access the database on Win 7. IIS is working, ASP.NET pages are working fine until database is used.
(XP is working fine on admin account without any additional setup)

So I'd like to ask If any of you know how to setup SQL server or how to modify IIS setup to get it working. I've used google to find out solution, there was many advices but non of them worked for me.

So any link|advice would be appreciated.

Thank you.

  • 1 Contributor
  • 1 Reply
  • 226 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by Ycefire

Dani AI

Generated

For — quick diagnosis and two reliable paths to finish the IIS7 + Win7 x64 + SQL Server 2008 Express setup.

The short diagnosis: those errors come from two related issues — SQL Server Express “user instances” expecting a per-user profile, and IIS application pools running under identities that don’t have a loaded local user profile by default. On Windows 7 / IIS 7.x the app pool identity often won’t have the profile SQL Express needs, which triggers the “failed to generate a user instance” behavior. (learn.microsoft.com)

Two practical fixes:

  • Quick/dev fix (keep AttachDbFilename/User Instance): set the website’s Application Pool → Advanced Settings → Process Model → Load User Profile = True, or run the pool under NetworkService or a specific local user that has a profile. That lets SQL Express create the user instance. See Microsoft’s guidance for details. (learn.microsoft.com)
  • Recommended (robust) fix: stop using User Instance/AttachDbFilename in production. Attach the .mdf to the SQLEXPRESS instance with SQL Server Management Studio, remove the attach/user-instance keys from the site’s connection string, and point to the database by name (Initial Catalog). User instances are a per-user/development feature and are not recommended for deployed sites. (learn.microsoft.com)

Permissions and the “CREATE DATABASE permission denied” error: that happens when the SQL login that IIS is using tries to create/attach a database but lacks server-level rights (CREATE DATABASE / dbcreator / sysadmin). The safest approach is to attach the DB as an administrator and then grant the IIS app-pool identity access to that database. Example SQL (run as an administrator in SSMS) to give the pool identity access:

CREATE LOGIN [IIS APPPOOL\MyAppPool] FROM WINDOWS;
USE [MyDatabase];
CREATE USER [WebDbUser] FOR LOGIN [IIS APPPOOL\MyAppPool];
EXEC sp_addrolemember 'db_owner','WebDbUser';

See docs for CREATE LOGIN and CREATE USER for exact syntax and caveats. (learn.microsoft.com)

Checklist before retesting: attach the MDF with SSMS (don’t leave it only in App_Data if using a shared instance), ensure SQL Server service account has filesystem access to the MDF/LDF, ensure the app pool identity has DB access or use a specific service account, restart IIS/SQL services. For understanding which server roles control CREATE DATABASE, see the server-level roles documentation. (learn.microsoft.com)

Helpful references:

Member Avatar for Ycefire

Errors are:

For:
<connectionStrings>
    <add name="ApplicationServices" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true"
      providerName="System.Data.SqlClient" />
    <add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=true;User Instance=true"
      providerName="System.Data.SqlClient" />
  </connectionStrings>

Error is:

Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed.




For:
  <connectionStrings>
    <add name="ApplicationServices" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true"
      providerName="System.Data.SqlClient" />
    <add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=true;User Instance=false"
      providerName="System.Data.SqlClient" />
  </connectionStrings>

Error is:

CREATE DATABASE permission denied in database 'master'.
An attempt to attach an auto-named database for file C:\inetpub\wwwroot\pokus\App_Data\Database.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.








SQL server setup is on shared memory and running as local
screenshot of the setup
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.