How to convince a site that it hasn't been installed in a fresh DB?

Questions
1

Need to run up a local dev site on a new SQL Server DB instead of it’s current SQLite DB.

I’ve tried changing the connection string to the new DB and deleting the Umbraco/Data/Temp folder and renaming the Umbraco.sqlite.db to Umbraco.sqlite-old.db.

Have also tried removing the connection string altogether plus the other things mentioned.

It seems convinced there should be in installation to connect to in a database as I get the following error in terminal:-

Building...
[18:22:34 INF] Acquiring MainDom.
[18:22:34 INF] Acquired MainDom.
[18:22:35 INF] Umbraco Runtime is not Run/Upgrade mode, so a database connection is unlikely to be available for migrations
[18:22:36 WRN] No last synced Id found, this generally means this is a new server/install. A cold boot will be triggered.
[18:22:36 INF] Registered with MainDom, localContentDbExists? True, localMediaDbExists? True
[18:22:36 INF] Creating the content store, localContentDbExists? True
[18:22:36 INF] Creating the media store, localMediaDbExists? True
[18:22:36 ERR] Exception (fc72de26).
Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid object name 'umbracoLock'.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
   at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)

Does anyone have any other suggestions. I’ve run up Umbraco installations on other DB’s loads of times but I can’t work out why this doesn’t just work and cause an install.

Thanks.

2

This is most likely an ownership issue with your sql user.

3

Is there a appSettings.Development.json you might have overlooked?

4

I’ve used this script to create the DB:-

USE [master];

GO

-- Create the database

CREATE DATABASE [UmbracoDb];

GO

-- Create a login (if it doesn't already exist)

-- This uses SQL authentication; change as needed for Windows auth

CREATE LOGIN [umbraco_user] WITH PASSWORD = 'StrongPassword123!';

GO

-- Create a user in the database for that login

USE [UmbracoDb];

GO

CREATE USER [umbraco_user] FOR LOGIN [umbraco_user];

GO

-- Grant required database roles and permissions for Umbraco

-- Umbraco needs db_owner to create schema, tables, stored procedures, etc.

EXEC sp_addrolemember N'db_owner', N'umbraco_user';

GO

And this connection string in appsettings.Development.json:

"umbracoDbDSN": "Server=localhost,1433;Database=UmbracoDb;User Id=umbraco_user;Password=StrongPassword123!;TrustServerCertificate=True"

I can connect to the DB via Rider’s Database tab with the same conn str so I know it’s there and working.

The umbraco_user is db_owner so it should work, but it doesn’t, dotnet run responds with :-

Building...
[14:41:29 INF] Acquiring MainDom.
[14:41:29 INF] Acquired MainDom.
[14:41:30 INF] Umbraco Runtime is not Run/Upgrade mode, so a database connection is unlikely to be available for migrations
[14:41:31 WRN] No last synced Id found, this generally means this is a new server/install. A cold boot will be triggered.
[14:41:31 INF] Registered with MainDom, localContentDbExists? True, localMediaDbExists? True
[14:41:31 INF] Creating the content store, localContentDbExists? True
[14:41:31 INF] Creating the media store, localMediaDbExists? True
[14:41:31 ERR] Exception (4fdc20ac).
Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid object name 'umbracoLock'.
5

What MainDomLock system are you using?

If it SQL based, can you try with FileSystem for development?

"Umbraco": {
  "CMS": {
    "Global": {
      "MainDomLock": "FileSystemMainDomLock"
    }
  }
}

Maybe also set the install database to true if not set already:

"Umbraco": {
  "CMS": {
    "Global": {
      "InstallMissingDatabase": true
    }
  }
}
6

Thanks, tried both of those, but no difference.

There must be a SQL script somewhere that will just set up the empty DB with appropriate login permissions. It’s hard to find out what they are.

I’m scripting it because I’m using Azure Data Studio to talk to a SQL Server 2022 in a Docker container on Linux. I’ve done this loads of times but this one isn’t working :frowning:

If I change the connection string back to it’s SQLite DB (and rename the DB to the correct name) then the site just works fine. I just want to install it on a SQL Server to run a test because the SQLite DB appears to lock up after importing 100 records. Just want to make sure it’ll be ok when it’s running on the live SQL Server.

7

Do you have the unattendedinstall to true and a valid unattended username and password? Umbraco should be perfectly able to generate the database with a valid login…

8

Turns out it won’t install to a SQLite DB either, i.e. from no DB. Two of us have tried it now. Something’s a bit weird here.

9

Can you try and post some logs for first boot on a clean DB, any type, but with the log level set to Debug or Verbose?
Maybe some migration or pre-boot action is preventing the normal Umbraco boot process.