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 90 posts 144 karma points
    Apr 02, 2021 @ 02:14
    Petras Surna
    0

    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 1702 posts 5999 karma points MVP c-trib
    Apr 02, 2021 @ 06:17
  • Sebastiaan Janssen 5044 posts 15475 karma points MVP admin hq
    Apr 02, 2021 @ 07:06
    Sebastiaan Janssen
    1

    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 1702 posts 5999 karma points MVP c-trib
    Apr 02, 2021 @ 09:28
    Huw Reddick
    1

    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 90 posts 144 karma points
    Apr 02, 2021 @ 09:45
    Petras Surna
    0

    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:

Draft