locked
SQL Server Express2008 R2 brings app down, instance problem RRS feed

  • Question

  • User-359936451 posted

    I have tried dozens of changes and modifications to many settings as I will outilne here and all to no avail.

    The short answer here though is that I uninstalled SQL Sever Express 2008 R2 and went back to the "NON" 2008 R2 and got things working. But like the fool I am wanted to run on the more current rev and updated again, all problems returned....

    SPECS

    Server:

    Win Server 2003 R2 SP2

    SQL SERVER EXPRESS 2009 R2

    VWD2008 Express (removed)

    IIS 6

     

    Workstation:

    XP Sp3

    VWD2008 Express

    SQL Server Express 2008

     

    I was orginally getting this message after updating to SQL Sever Express 2008 R2, I removed VWD 2008 Express from the web server, no good, then uninstalled SQL Server 2008 Express R2 and reinstalled SQL Server 2008 Express. worked but had issues, couldn't install MGTM Studio. Then I removed SQL 2008, cleaned folders and reg. Reloaded SQL Server 2008 Express, still flaky, couldn't get the Management Studio to install. So I removed it again and used the Web Platform Installer, which worked but loaded 2008 R2 again. Which brought back the message.

    Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.

     

    I have read dozens of fixes for this problem on workstations/development systems, but not for the production web server. Although I followed the solution at this link,  click here  it provided a new error....

    Cannot open user default database. Login failed.
    Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.

    I have explicity gone through every folder and file from my root web folder to the DB file names .mdf and .ldf in the app_data folder and applied full credentials to the NetworkService account,

    AS WELL AS, opening SQL SERVER MANAGEMENT STUDIO and confirmed that the same account has full access in the Login User folder.

    I can not figure out what this problem is caused by.

    Any solutions for the server environment would be greatly appreciated, I am running out of time!!!

    Thanks,

     

     

    A quick update:

    I am noting the following: it appears that the User Account name for Network Service, in SQL Server Express 2008 R2,  is what maybe causing me a great deal of grief.  

    My web sites are all trying to authenticate with NT Authority/Network Service but the Express 2008 R2 is requiring NT Authority/NetworkService.

    NO SPACE IN THE USER NAME!!!!

    If you look at the SQL Configuration Manager, and compare them from a Desktop to a Production Server installation, you can see the issue. SQL Server 2008 Express R2 removes the space in the Network Service name, I believe this is causing the User authentication error when trying to logon from a web site.

     

     

     

     

    Tuesday, August 10, 2010 9:28 AM

Answers

  • User470413944 posted

    Hi,

    As far as I know Visual Studio 2008 R2 still use NT AUTHORITY\NETWORK SERVICE, since it's a build-in account.

    For example:

    http://msdn.microsoft.com/en-us/library/ms144259.aspx

    You can use SQL Server Profiler to track what exactly cause login fails.

    I hope this can be helpful for you.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 12, 2010 7:11 AM
  • User-359936451 posted

    Although the authentication issue was never resolved the solution was to rebuild the databases in SQL Express, rather than using the DB's in the app_data folder.

    I wanted to complete the posting on this problem prior to closing or indicating that the issue is resolved. In my opinion it was not truly fixed, but the configuration was changed.

    After over a week of teleconferences with two MS support persons, one from the IIS group and one from  SQL Server group, the solution we ended up with was to move the two DB's from the App_Data folder directly into SQL Server Express. A process that took awhile to complete in itself.

    The two database, aspnetdb.mdf and my transaction DB we'll call it mydb.mdf, were first exported creating the schema sql files to build the new DB's within SQL Express. We then exported the data and imported into the new DB's. The two MS technicians both believe that the orginal ASPNTDB.mdf was some how corrupted. Rather than figure out how to fix that, the course of action was as stated above, to move the DB directly into SQL Express.

    One issue we incurred was that the permissions on the new DB were restricted. For example, I was not able to import data into the new tables with the Identity setting On and it had to disbaled, set to non unique, in order to pull the data in. When I went back to reenable it for auto numbering of new records inserted in to the tables. I was not able to as a result of a check box that prevented saving that type of change to the table. The setting was under Tools/Options/Designers/Table and Database Designers/ look for the check box that says "Prevent saving changes that require table recreation".

    Once unchecked all was well. This issue included adding fields to the tables.

    This change also presented an issue with some of the coding with regard to Connection Strings that were built directly into some of the code behind pages. Once I understood the changes that needed to be made each time the site was rebuilt in order to be copied to the server, things seem to be just fine.

    MS was never able to figure out why authentication against the aspnetdb.mdf in the app_data folder was not possible. We tried a number of processes from creating entirely new and unique user accounts with full privledges and access to every folder on the system, to confirming that the SQL sa account had full rights. None of these efforts worked. So if you are facing this type of an issue, change course, it does not appear to be solveable trying to give access to user accounts or even creating new ones to access the DB.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 25, 2010 8:48 AM

All replies

  • User470413944 posted

    Hi,

    As far as I know Visual Studio 2008 R2 still use NT AUTHORITY\NETWORK SERVICE, since it's a build-in account.

    For example:

    http://msdn.microsoft.com/en-us/library/ms144259.aspx

    You can use SQL Server Profiler to track what exactly cause login fails.

    I hope this can be helpful for you.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 12, 2010 7:11 AM
  • User-359936451 posted

    UPDATE: I now have over 4 hours in tech support with Microsoft on this issue and it has not yet been resolved. Although we were able to establish a connection, it has yet to be completed with local account.

    I will update further once a solution has been confirmed.

    Initial thoughts by MS focused on an IIS issue, but I believe they now are convinced that it is a SQL problem.  

    Thursday, August 12, 2010 9:43 AM
  • User-359936451 posted

    Although the authentication issue was never resolved the solution was to rebuild the databases in SQL Express, rather than using the DB's in the app_data folder.

    I wanted to complete the posting on this problem prior to closing or indicating that the issue is resolved. In my opinion it was not truly fixed, but the configuration was changed.

    After over a week of teleconferences with two MS support persons, one from the IIS group and one from  SQL Server group, the solution we ended up with was to move the two DB's from the App_Data folder directly into SQL Server Express. A process that took awhile to complete in itself.

    The two database, aspnetdb.mdf and my transaction DB we'll call it mydb.mdf, were first exported creating the schema sql files to build the new DB's within SQL Express. We then exported the data and imported into the new DB's. The two MS technicians both believe that the orginal ASPNTDB.mdf was some how corrupted. Rather than figure out how to fix that, the course of action was as stated above, to move the DB directly into SQL Express.

    One issue we incurred was that the permissions on the new DB were restricted. For example, I was not able to import data into the new tables with the Identity setting On and it had to disbaled, set to non unique, in order to pull the data in. When I went back to reenable it for auto numbering of new records inserted in to the tables. I was not able to as a result of a check box that prevented saving that type of change to the table. The setting was under Tools/Options/Designers/Table and Database Designers/ look for the check box that says "Prevent saving changes that require table recreation".

    Once unchecked all was well. This issue included adding fields to the tables.

    This change also presented an issue with some of the coding with regard to Connection Strings that were built directly into some of the code behind pages. Once I understood the changes that needed to be made each time the site was rebuilt in order to be copied to the server, things seem to be just fine.

    MS was never able to figure out why authentication against the aspnetdb.mdf in the app_data folder was not possible. We tried a number of processes from creating entirely new and unique user accounts with full privledges and access to every folder on the system, to confirming that the SQL sa account had full rights. None of these efforts worked. So if you are facing this type of an issue, change course, it does not appear to be solveable trying to give access to user accounts or even creating new ones to access the DB.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 25, 2010 8:48 AM