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:
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
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.
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.
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.
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.
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:
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:
Can anyone suggest what's gone wrong?
Thanks, Alistair
Try to configure an SQL Server user credential in your database.
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.
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
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.
Hi,
File -> Connection Object Explorer
Change the authentication from Windows to SQL and use the db user details.
Steve
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.
Hi, Perhaps it's the port in Josué's conn str try this one.
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:
and
but no dice.
I think this was the one that should have worked. I'll accept it as the solution.
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
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
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...
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
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!
is working on a reply...