locked
SQL Express connection issue RRS feed

  • Question

  • User-210067823 posted

    Hi,

    I recently got a new server running Windows 2012 Server.

    I am trying to migrate the sites from my old server running 2003 Server to the new one.

    I've sorted out the legacy ASP.NET framework 2.0 classic on the new server, it seems to be running aspx pages with no issues.

    My last hurdle is a connection issue with a fresh install of SQL Server 2016, managed by SQL Manager 2014.

    The error is:

    "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."

    If I crank up Web Express 2012 on the server and run the application from there it all works perfectly.

    At the moment I am thinking its an access or permissions error.  I attached the database to SQL manager when logged in as an administrator.

    That's what the owner is showing as in SQL manager.  Do I need to change it to ASPMACHINE (that didn't work), is there a modern equivalent?

    I have six ASP.NET websites running for customers that need migrating, the server rental company (1and1) is turning the old server off at the end of the month.

    Thanks for any pointers that anyone has (and I know it's my own fault for running the same server for 10+ years without upgrading)

    Saturday, October 10, 2015 6:26 AM

Answers

All replies

  • User364663285 posted

    Try to put this

    	<connectionStrings>
    		<add name="conn2" connectionString="Data Source=192.?.?.?\your_db_instance;Initial Catalog=your_db_schema;Integrated Security=False;User ID=your_db_login;Password=??????" providerName="System.Data.SqlClient"/>
    	</connectionStrings>
    

    within Web.config/app.config file in your project, to ensure the project will work fine with the database.

    Saturday, October 10, 2015 9:18 AM
  • User-210067823 posted
    Thanks, am travelling, will try it tomorrow and let you know the outcome.
    Kind regards
    Mike
    Saturday, October 10, 2015 1:45 PM
  • User-210067823 posted

    Hi,

    Thanks for your response.  I've been playing about with it for about 20 hours now, and it's not working.

    The error is:

    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 notice you've posted the same answer in a number of forums.  It would be helpful if you could check the following line from the web.config?

    In particular, I am not sure if I have formed the database location path properly.

    <add name="conn2" connectionString="Data Source=82.xx.yy.zz\D:\PRODUCE\APP_DATA\ASPNETDB.MDF;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>

    Also how important is it to use a specified username and password (it still doesn't work even if I do that in the SQL manager properties for the db)

    Thanks

    Mike

    update:  It's a Server 2012 issue, I deleted SQL server 2016 and installed SQL 2008.  That works on my desktop, but not on the server.

    Tuesday, October 13, 2015 4:57 AM
  • User364663285 posted

    Can you try to adjust the codes in the way I suggested? Thanks

    Tuesday, October 13, 2015 10:15 AM
  • User1634740479 posted

    I think this problem come because you are using Windows authentication in connection string

    Solution 1:
    change your connection to SQL Server login adding user name and password in connection string

    Solution 2:

    1--> Open IIS Manager
    2--> Select your site application pool
    3--> Click "Advanced Settings" from the Actions menu on the right or from the right-click menu
    4--->Under Process Model change the setting for "Load User Profile" to True
    5--->Click OK and restart your website.

    Friday, October 30, 2015 1:55 PM
  • User364663285 posted

    Hi,

    BTW, are you able to connect to the database using SQL server management studio? If not, you should check/re-start the relevant SQL server services on the server, using "services.msc".

    And also try to put this

    	<connectionStrings>
    		<add name="conn2" connectionString="Data Source=192.168.?.?\your_db_instance;Initial Catalog=your_db_schema;Integrated Security=False;User ID=your_db_login;Password=??????" providerName="System.Data.SqlClient"/>
    	</connectionStrings>
    


    within Web.config/app.config file in your project, to ensure the project will work fine with the database.

    Saturday, October 31, 2015 4:53 AM
  • User-210067823 posted

    Thank you to wmec everyone that responded.  I've spent a lot of time solving this.  The learning curve has been very steep - new OS to new SQL server setup.

    Using SQL server 2012 and MS Server 2012 to run legacy ASP.NET 2.0 sites and SQL server 2005 databases.

    There is an excellent paper here that explains how to set up the framework properly: http://www.iis.net/learn/get-started/whats-new-in-iis-8/iis-80-using-aspnet-35-and-aspnet-45

    Having the correct application pool selected is important.  I used ASP.net v2.0 classic.

    Malik was correct, the SQL server connection issue was related to authentication.

    What was required was to grant the machine user called 'Network Services' access to the databases AND to directory housing the application.

    There is a good description of how to do that here: http://knowhow.visual-paradigm.com/hibernate/solving-sql-server-connection-problem/

    WMEC sorry, but I never got your TCP-IP- based solution to work for me :(

    Related to moving the legacy sites but not the SQL connection issue, this is great for circumventing the locked assembly in server 2012 if you have legacy DLLs: http://social.technet.microsoft.com/wiki/contents/articles/22618.how-to-install-a-dll-to-the-gac-on-windows-server-2012-using-only-powershell.aspx?Sort=MostRecent&PageIndex=1

    I mention that only in case someone else has the same issues as I did and finds this posting.  It's all running now :)

    Thanks again for all the suggestions.

    Mike

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 19, 2015 10:04 AM