While working on an ASP.NET 4.0 web application that uses the built-in Membership tools, I recently encountered the above two errors which prevented the site from working until sorted.
I suspect the errors were triggered by the SP1 update to SQL Server 2008, as the application was working fine prior to my finally applying the SP1 update, months after its release.
In this post I will explain how I resolved the errors.
First error: “Failed to generate a user instance of SQL Server”
Apparently ASP.NET 4.0 uses a different approach to connecting to the aspnetdb.mdf database that holds the Membership data: the User Instance. A quick look at the connection string for the aspnetdb.mdf database in web.config illustrates this:
Long story short, after much Googling and reading, the solution that worked for me was to delete the following directory: C:\Documents and Settings\[USERNAME]\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS—obviously you replace [USERNAME] with the name of the currently-logged-in user—and then re-booting the computer. On Vista/7, you may need to delete C:\Users\[USERNAME]\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS instead; I suspect this will depend on whether your install of Vista/7 was an upgrade or clean installation.
Note that these steps fixed my particular issue; it appears that there are a few different causes of this error message. If the above steps don’t work for you after you’ve re-booted your PC, have a look at this ASP.NET Forums post and this Forum Post, too for some other things to try, but be sure to read all the comments: some suggested “fixes”, such as setting User Instance=false in the database connection string in web.config, throw the baby out with the bathwater, causing unwanted side effects, which you will see if you read the responses to earlier comments.
So, of course, immediately upon getting past the first error, I am greeted with:
Second error: “Login failed for user ‘NT AUTHORITY\NETWORK SERVICE’”
Wow, this is frustrating. Particularly as everything was working fine before I installed the Service Pack! Thank you, Microsoft!
At any rate, a bit of Googling turned up the likely culprit: permissions issues.
The fix was pretty simple: open up SQL Server Management Studio, or whichever tool you use to run SQL queries against the database, and run the following command on your membership database (usually aspnetdb.mdf unless you’ve changed something):
sp_grantlogin 'NT AUTHORITY\NETWORK SERVICE'
That fixed it for me.
If you are still having issues, it’s likely your problem was different and will have a different solution. You could take a look at this ASP.NET Forums post, but take to heart what tommy123456 says in his post: if you start changing security settings without knowing the repercussions of what you’re doing, you could be opening serious security holes in your application!
Hopefully this will help someone who is struggling as I was.