dotnet-episerver create-cms-database not working?

Nick Hamlin

Vote:

I feel embarrassed posting this because I'm sure it's a stupid oversight on my part, however, I'm at an impasse.

I have a CMS 12 solution that works just fine. I copied the full solution to a new directory and I have gone in and changed all namespaces and filenames to a new project name. I've updated the appSettings.json (and relevant environment versions) as well as the launchSettings.json. I have previous CMS 12 versions running so I've already loaded the templates and the cli, but I went ahead and did that too.

When I run the 'dotnet-episerver create-cms-database' with the proper parameters it gives three lines as an output:


[01:32:40 INF] Running create-cms-database command.
[01:32:40 WRN] The username episerver already exists on the server, no need to create.
[01:32:40 INF] create-cms-database has completed.

I publish the site and I get:

Microsoft.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

In SSMS, I can log into the server with the connection string credentials and then see the new database created with that command, but there are 0 tables under it. I was advised publishing and running the site will build out the EF tables on initialization but it doesn't. It's driving me mad because it's (what I believe to be) a straight copy of another solution that works just fine, just with new namespaces and filenames. My belief is it's in that command that is not building out the databases correctly. Permissions and SQL table access and everything else has been confirmed.

I'm sure it's something small I'm overlooking. I've checked out multiple blogs and forum posts but nothing that addresses taking another CMS 12 solution and converting it (which would be crucial for any company with a startup project).

I hope it's something I'm just not seeing. I'm hoping someone here has a hint. I'm not against going down a(nother) rabbit hole!

#295432

Jan 27, 2023 7:48

Minesh Shah (Netcel)

Vote:

in appsettings.Development.json have you updated your database location ?

  "ConnectionStrings": {
    "EPiServerDB": "Data Source=(LocalDb)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\MultisitePOC.mdf;Initial Catalog=MultisitePOC;Integrated Security=True;Connect Timeout=30"
  },

Most likly not the issue as cant connect to the database But make sure Model Sync has not been explicaitally disabled

CreateDatabaseSchema should be set to true like below

{
          "EpiServer": {
            "Cms": {
                "DataAccess": {
                    "CreateDatabaseSchema": "true"
                }
            }
    }

Docs

https://docs.developers.optimizely.com/content-cloud/v12.0.0-content-cloud/docs/installing-database-schema

https://docs.developers.optimizely.com/content-cloud/v12.0.0-content-cloud/docs/automatic-schema-updates

#295433

Edited, Jan 27, 2023 8:34

Eric Herlitz

Vote:

Check my guide in this video on how to setup the SQL Database using the CLI

https://www.youtube.com/watch?v=U0zWMnfW-Hk&

Check the topics on

Setting up a database using the CLI: (18:56)
Setting up an admin user using the CLI: (22:44)

Most likely something did go wrong when creating the connectionstring, any typical development environment with a local sqlserver would look like this

  "ConnectionStrings": {
    "EPiServerDB": "Server=localhost;Database=DBNAME;User Id=USERNAME;Password=PASSWORD;TrustServerCertificate=True;"
  },

#295443

Jan 27, 2023 12:13

Nick Hamlin - Jan 28, 2023 19:31

Thank you for this. I watched your video and saw that the create-cms-database created an empty database for you, too. That, and your comment about the connection string, helped me narrow down why the initialization wasn't working.

Nick Hamlin

Vote:

I was able to figure out what was wrong, and as expected 'git blame' was not kind to me. In short: we started using SQL Aliases recently and that broke the new site/connection.

A couple of months ago, someone else on the project introduced SQL Server Aliases to the project and upgraded Microsoft.Data.SqlClient to v5.0.0 (which provides native support to SQL aliases per https://stackoverflow.com/a/73633473). However, I noticed that Opti was throwing a warning that:

EPiServer.Framework 12.9.3 requires Microsoft.Data.SqlClient (>=4.0.0 && < 5.0.0) but version Microsoft.Data.SqlClient 5.0.0 was resolved.

About a month ago, I went on a refactoring run to get rid of warnings and errors and rolled back that NuGet package to 4.1.1. Everything was good because there weren't warnings when compiling or in the pipeline, however, I didn't realize the .dll for v5.0.0 wasn't overwritten when published so the site ran without complaining too.

Jump forward to this week, I copied the solution to a new namespace and restored NuGet packages. That restore installed Microsoft.Data.SqlClient v4.1.1 which doesn't have native support for aliases. That's why it couldn't figure out how to connect.

Note: Apparently, the above-referenced SO entry says Microsoft stores aliases in the registry and dropped support in .Net Core, so this might not even be an issue with CMS 11.

---

Quick edit: I just noticed EPiServer.Framework 12.11.0 does support Microsoft.Data.SqlClient 5.0+ so I'm going to make the suggestion to upgrade from 12.9.3

#295497

Edited, Jan 28, 2023 19:28