Last post Nov 20, 2010 12:28 AM by reinux
May 30, 2010 03:12 PM|TheNutCracker|LINK
I just recently decided to uninstall SQL Server 2008 Express Edition in favor of installing SQL Server 2008 R2 Express Edition. Now I can't access the "security.aspx" page via the ASP.NET Configuration icon. Clicking it yields the following error. "Unable
to connect to SQL Server database." My guess is that the account that Visual Web Developer Express 2008 edition is launching to "attempt" to create the default ASPNETDB.MDF file in the App_Data folder does not have sufficient permissions to execute the command
through the local SQL Server instance.
This is one of the most frustrating issues I have ever had to deal with. I have no idea what is going on behind the scenes when I click on that "ASP.NET Configuration" icon because of the incredibly abstract error messages that Microsoft likes to dole out.
It would be nice of Microsoft to tell me the exact name of the account that SQL Server is refusing. That would be kind of them. Maybe in a future version? Anyone who can explain the relationships to me between
1)Visual Web Developer Express Edition 2008
2)IIS Manager 7.0 and
3)SQL Server Express Edition
What is the name of the user account that Visual Web Developer uses to try and create the ASPNETDB.MDF file in the App_Data folder of a project? Is it "DefaultAppPool"?
I created an account in SQL Server 2008 Management Studio under Security->Logins named "IIS APPPOOL\DefaultAppPool". Was this a necessary thing to do? When I looked at the "Server Properties->Permissions->Effective" of the "IIS APPOOL\DefaultAppPool" login
it shows the message, "Could not obtain information about Windows NT group/user IISAPPOOL\DefaultAppPool". Error Code: 0xffff0001.
Is this account supposed to be created in the SQL Server->Security->Logins node automatically upon installation of another development product? And if so, what are the consequences of installing SQL Server R2 "after" the other product has already been installed?
What is the "Security->Logins->NT SERVER\MSSSQL$SQLEXPRESS" used for? Is that account ever used by IIS 7.0 or VWD2008 EE?
Any guidance and advice that can be given to help resolve this issue is much appreciated.
"There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled."
It pains me to think that programming would be reduced to nothing more than an exercise in trial-and-error. Maybe Microsoft will find the time to produce more specific error messages in the future that don't encompass a multitude of possible resolutions.
May 30, 2010 06:08 PM|andrewjboyd|LINK
Any chance your connection is connecting via "NT Authentication"? If so, it may be on IIS user account has not got access, but running through VS it uses your logged in account (probably the same that setup SQL Server) so it would have access
You will see in IIS what user it is using. Sounds like you're using "NT Authentication", I would highly recommend using SQL Server User Accounts (depending on how your site is setup, you risk being hacked and if you've given the IIS user permissions to
do things it really should you could find you lose your database or have private information accessed)
May 30, 2010 07:13 PM|TheNutCracker|LINK
If by NT Authentication, you actually mean, "Windows Authentication", the answer is yes.
This is the default configuration mode in every default "web.config" file when I create a new "web site" in VWD2008
I actually found out what the name of the user is that Visual Web Developer is using to try and connect to SQL Server. Its NT AUTHORITY\IUSR, at least thats what is displayed in my default.aspx web page when I check the principal identity. I expected it
to be "DefaultAppPool", not "NT AUTHORITY/IUSR".
I created an account(or verified it exists, cant remember) with name NT AUTHORITY/IUSR in SQL Server Management Studio and confirmed it had "public" and "sysadmin" roles attached to the account. Still, it will not connect.
In IIS 7.5 my application pool identity is "ApplicationPoolIdentity" and the name is "DefaultAppPool". Yet, the default.aspx reports NT AUTHORITY\IUSR as the principal identity when the page is viewed in the browser from VWD2008.
This is so convoluted I just cant seem to connect the dots.
May 30, 2010 09:35 PM|andrewjboyd|LINK
Can you connect to it via the "Server Explorer" window in VS?
May 31, 2010 01:40 AM|TheNutCracker|LINK
No, in newly created web sites there is nothing to connect to. Clicking on the ASP.NET Configuration icon in the solution explorer causes VWD2008 to "attempt" to connect to an already existing ASPNETDB.MDF file in the App_Data folder of the current loaded
project. If its a new project, or if the ASPNETDB.MDF file doesn't exists for any reason it attempts to create the file. On my machine, it gets so far as creating the App_Data folder but fails to create the virgin ASPNETDB.MDF file.
May 31, 2010 11:36 AM|TheNutCracker|LINK
Well, I have solved my own problem by scouring the infinite knowledge of the internet.
Its not the way I wanted to resolve the issue but given my limited understanding of the relationships and inner workings of Windows 7 Security, ASP.NET Security, IIS 7.5 and SQL Server Security, it was necessary to do it this way I suppose.
I simply deleted all the .MDF files in my C:\Users\<USERNAME>\AppData\Local\Microsoft\Microsoft SQL Server Data\SQEXPRESS directory. Then when I reloaded VWD2008EE and clicked on the ASP.NET Configuration, all the new required .MDF files were copied back
into the folder.
The ASPNETDB.MDF is once again being automatically created in the App_Data folder with no connection issues.
The .MDF files in the \Users\... folder must have either became corrupt or simply were no longer configured appropriately to work with the new installation of SQL Server 2008 R2 Express Edition.
Nov 20, 2010 12:28 AM|reinux|LINK
Deployment really is way, way, way too complicated on Windows.
Add WCF into the mix (which returns the error message "File not found" for any exception thrown by the service) and you have a cozy place in hell.