Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Alistair Jenkins 92 posts 315 karma points
    Jan 26, 2018 @ 16:56
    Alistair Jenkins
    0

    Connection string works in VS but publish locally and it doesn't work.

    I have a solution in Visual Studio 2017 that works fine and it uses the following connection string to connect to a local database served by SQL Server 2017 Developer:

     <add name="umbracoDbDSN" connectionString="Server=myPcName;Database=myDbName;Integrated Security=True" providerName="System.Data.SqlClient" />
    

    I was able to successfully build and publish the website locally to be hosted by IIS Express 10. I assumed that the connection string should still work as it is still being served in exactly the same way. But I get this error:

    [UmbracoStartupFailedException: Umbraco cannot start. A connection string is configured but Umbraco cannot connect to the database.]
       Umbraco.Core.CoreBootManager.EnsureDatabaseConnection() +221
       Umbraco.Core.CoreBootManager.Complete(Action`1 afterComplete) +87
       Umbraco.Web.WebBootManager.Complete(Action`1 afterComplete) +157
       Umbraco.Core.UmbracoApplicationBase.StartApplication(Object sender, EventArgs e) +364
    
    [HttpException (0x80004005): Umbraco cannot start. A connection string is configured but Umbraco cannot connect to the database.]
     System.Web.HttpApplicationFactory.EnsureAppStartCalledForIntegratedMode(HttpContext context, HttpApplication app) +535
      System.Web.HttpApplication.RegisterEventSubscriptionsWithIIS(IntPtr appContext, HttpContext context, MethodInfo[] handlers) +166
      System.Web.HttpApplication.InitSpecial(HttpApplicationState state, MethodInfo[] handlers, IntPtr appContext, HttpContext context) +169
      System.Web.HttpApplicationFactory.GetSpecialApplicationInstance(IntPtr appContext, HttpContext context) +372
      System.Web.Hosting.PipelineRuntime.InitializeApplication(IntPtr appContext) +329
    
    [HttpException (0x80004005): Umbraco cannot start. A connection string is configured but Umbraco cannot connect to the database.]
      System.Web.HttpRuntime.FirstRequestInit(HttpContext context) +525
      System.Web.HttpRuntime.EnsureFirstRequestInit(HttpContext context) +118
      System.Web.HttpRuntime.ProcessRequestNotificationPrivate(IIS7WorkerRequest wr, HttpContext context) +708
    

    Can anyone suggest what's gone wrong?

    Thanks, Alistair

  • Josué González 17 posts 110 karma points
    Jan 26, 2018 @ 17:21
    Josué González
    0

    Try to configure an SQL Server user credential in your database.

    <add name="umbracoDbDSN" connectionString="server=myPcName,1433;database=myDbName;user id=YourUserName;password=YourUserPass123" providerName="System.Data.SqlClient" />
    
  • Alistair Jenkins 92 posts 315 karma points
    Feb 02, 2018 @ 11:09
    Alistair Jenkins
    0

    Hi Josue,

    Thanks for replying, sorry for my late reply, I'm only in once a week.

    Tried your suggested connection string but it didn't connect. What's confusing is that now neither string works in the VS version either.

    Tried changing the user password in SSMS in case I had that wrong, but that didn't help.

  • Steve Morgan 1349 posts 4458 karma points c-trib
    Feb 02, 2018 @ 11:21
    Steve Morgan
    0

    You're trying to use integrated security - this will use the Windows user - under VS it's yours. When you deploy locally it will be using the IIS user rather than you / the admin user.

    Josué is correct but it sounds like you've messed up your db user setup. Try to connect using your SQL Management tools using the SQL user you've created with a password in the connection. Ensure you've turned off the password requirements to change password at next login and make sure your SQL user has the relevant access to the DB (probably just set it to db owner to start with). If you can connect in Management tools then you should be able to use the same credentials in the web.config.

    HTH Steve

  • Alistair Jenkins 92 posts 315 karma points
    Feb 02, 2018 @ 11:45
    Alistair Jenkins
    0

    I'm using SSMS to manage the server and I'm not very familiar with it. I can't see how to connect to the database using the user credentials as you suggest. I am connected to it in the Object Explorer, I can run queries on the tables there, but that is using my Windows login presumably.

    Sorry, I'm probably being a bit dim here.

    Integrated Security isn't working now on the VS side either.

  • Steve Morgan 1349 posts 4458 karma points c-trib
    Feb 02, 2018 @ 12:17
    Steve Morgan
    0

    Hi,

    File -> Connection Object Explorer

    Change the authentication from Windows to SQL and use the db user details.

    Steve

  • Alistair Jenkins 92 posts 315 karma points
    Feb 02, 2018 @ 12:53
    Alistair Jenkins
    0

    Thanks Steve,

    So I changed the server authentication to 'Windows and SQL' and used the db user details to successfully connect to the server following your instruction, and look in the database to see the tables there.

    But when I use the same user details in the connection string, as suggested above, and try to open default.aspx (we're back in VS now btw) I get the same error.

  • Steve Morgan 1349 posts 4458 karma points c-trib
    Feb 02, 2018 @ 13:00
    Steve Morgan
    100

    Hi, Perhaps it's the port in Josué's conn str try this one.

    <add name="umbracoDbDSN" connectionString="server=[[YOURSERVER]];database=[[YOURDB]];user id=[[YOURUSER]];password='[[PASS]]'" providerName="System.Data.SqlClient" />
    
  • Alistair Jenkins 92 posts 315 karma points
    Feb 02, 2018 @ 13:08
    Alistair Jenkins
    0

    OK, tried that. Also noticed that you have apostrophes around the password, which Josue's didn't, so added those in too. Still didn't work.

    Also tried the server name as:

    server=.\MSSQLSERVER
    

    and

    server=MyPcName\MSSQLSERVER
    

    but no dice.

  • Alistair Jenkins 92 posts 315 karma points
    Feb 02, 2018 @ 15:31
    Alistair Jenkins
    0

    I think this was the one that should have worked. I'll accept it as the solution.

  • Steve Morgan 1349 posts 4458 karma points c-trib
    Feb 02, 2018 @ 14:44
    Steve Morgan
    0

    Hi,

    Do you have another string in the < connectionStrings> section?

    Are you definitely editing the web.config in the root of the website and not some backup somewhere?

    Steve

  • Alistair Jenkins 92 posts 315 karma points
    Feb 02, 2018 @ 14:50
    Alistair Jenkins
    0

    Hi,

    I have some commented out lines, but that's all. I am definitely editing the web.config in the root of both the VS and local versions,

    Cheers, Alistair

  • Steve Morgan 1349 posts 4458 karma points c-trib
    Feb 02, 2018 @ 14:54
    Steve Morgan
    0

    Well if you can connect via Management Studio using the exact same details and the site either from VS or IIS is not working I'm at a total loss.

    To confirm - break your web.config and you'll hopefully see a different error (a 500 probably).

    Try spinning up a clean, blank install of Umbraco with a new db user and rule out things one by one....

    Also - ensure your sql password is alphanumeric - I have a vague recollection of certain chars causing issues...

  • Alistair Jenkins 92 posts 315 karma points
    Feb 02, 2018 @ 15:29
    Alistair Jenkins
    1

    Oh well, this is embarrassing. I found a typo in the connection string for the local site that I must have looked at a hundred times and definitely wasn't there when I started so must have snuck in in one of the many rewrites.

    Then I found a typo in the name of the database copy that I made to point the VS version to.

    So now they both work. The port number wasn't necessary though.

    Thank you Steve for all your help. Apologies for leading you a merry dance that could have been over a lot quicker.

    Cheers, Alistair

  • Steve Morgan 1349 posts 4458 karma points c-trib
    Feb 02, 2018 @ 15:30
    Steve Morgan
    1

    I'd love to tell you I've never done exactly this merry dance myself.

    But I have, on more than a few occasions!

    Glad you sorted it!

Please Sign in or register to post replies

Write your reply to:

Draft