Copied to clipboard

Flag this post as spam?

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

  • Petras Surna 84 posts 135 karma points
    Apr 02, 2021 @ 02:14
    Petras Surna

    Umbraco connection string - cant swap between integrated security and specifc sql user

    In Umbraco 8.12.2, when I install Umbraco with a specific sql user it generates this connection string and eveything works fine

    <add name="umbracoDbDSN" connectionString="Server=DESKTOP-04UJ7MO\SQLEXPRESS;Initial Catalog=skautai1;User Id=skautai;Password='skautai';" providerName="System.Data.SqlClient" />

    However, when I change the string to use a specific sql user, Umbraco tries to reinstall

    <add name="umbracoDbDSN" connectionString="Server=DESKTOP-04UJ7MO\SQLEXPRESS;Database=Skautai1;Integrated Security=true" providerName="System.Data.SqlClient" />

    The reverse is also true. If I install Umbraco with Integrated Security, I can't swap the connection string to a specific sql user without Umbraco requiring a reinstallation.

    I have a feeling I am doing something incredibly stupid as the toughest problems always have simple fixes but I can't for the life of me see it.

  • Huw Reddick 429 posts 1173 karma points
    Apr 02, 2021 @ 06:17
  • Sebastiaan Janssen 4899 posts 14654 karma points MVP admin hq
    Apr 02, 2021 @ 07:06
    Sebastiaan Janssen

    Looks like your user skautai creates a schema dbo.owner in the database, where as your integrated user uses dbo. Umbraco doesn't know this so it happily tries to find all the dbo.owner.* tables, which don't exist, so it will do a fresh install (if you would continue, you would see that you get 2 sets of umbraco tables in that same database).

  • Huw Reddick 429 posts 1173 karma points
    Apr 02, 2021 @ 09:28
    Huw Reddick

    It is related to your users permission in the database.

    I can swap between a user and integrated security without causing umbraco to recreate it's tables.

    both these connection strings work quite happily

    <add name="umbracoDbDSN" connectionString="server=DESKTOP-BM5D2ET;database=umbraco;Integrated Security = true" providerName="System.Data.SqlClient" />
    <!--<add name="umbracoDbDSN" connectionString="server=DESKTOP-BM5D2ET;database=umbraco;user id=DevUser;password=testdevpassword" providerName="System.Data.SqlClient" />-->

    So check how your user is setup in SQL as it is a permission/user mapping issue. You probably need to map your sql user to use dbo as it's default schema.

  • Petras Surna 84 posts 135 karma points
    Apr 02, 2021 @ 09:45
    Petras Surna

    Yes, that was it.

    This issue came from a Plesk deployment where the limited SQL Server interface was not creating the correct schema for the users. Thanks for all the answers, I should have seen it myself.

Please Sign in or register to post replies

Write your reply to: