Cannot connect to SQL Server during install

[idseefeld]

Which Umbraco version are you using? (Please write the exact version, example: 10.1.0)

v12 rc

Bug summary

In the install step "Configure your database" I cannot connect to SQL Server 2019 database neither with integrated authentication nor database user account. I use a a named instance "SQL2019":

I have verified with SQL Server Management Studio, that connection with this settings is working and user has owner permissions.
The project works fine with SQLite.

Specifics

No response

Steps to reproduce

Create a fresh v12 Umbraco project. Run and choose SQL Server instead of SQLite database.

Expected result / actual result

No response

---

This item has been added to our backlog AB#29662

[github-actions]

Hi there @idseefeld!

Firstly, a big thank you for raising this issue. Every piece of feedback we receive helps us to make Umbraco better.

We really appreciate your patience while we wait for our team to have a look at this but we wanted to let you know that we see this and share with you the plan for what comes next.

• We'll assess whether this issue relates to something that has already been fixed in a later version of the release that it has been raised for.
• If it's a bug, is it related to a release that we are actively supporting or is it related to a release that's in the end-of-life or security-only phase?
• We'll replicate the issue to ensure that the problem is as described.
• We'll decide whether the behavior is an issue or if the behavior is intended.

We wish we could work with everyone directly and assess your issue immediately but we're in the fortunate position of having lots of contributions to work with and only a few humans who are able to do it. We are making progress though and in the meantime, we will keep you in the loop and let you know when we have any questions.

Thanks, from your friendly Umbraco GitHub bot 🤖 🙂

[idseefeld]

I tried Paul Seals "Package Script Writer" and got an error message in the terminal window:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - Die Zertifikatkette wurde von einer nicht vertrauenswürdigen Zertifizierungsstelle ausgestellt.)

So I think the issue is caused by an issue on my machine.

[abjerner]

I'm getting the same issue, so might still be worth looking into.

When googling the error, it could seem that this might be due to a version of SQL server that is no longer supported by the SQL driver that ships with Umbraco 12. In my case, I'm using SQL Express, and can't remember the last time that I updated it, so there could be some truth to that. But I haven't had the time to look into this yet.

[idseefeld]

I'm using a fairly recent SQL Server 2019 developer edition.
And I have tried the same database with an Umbraco version 10.5.1 without any issues.
So, I guess this need to be investigated.

[abjerner]

I can see I'm not 12.0.2569.0, which should be SQL Server 2014. So I'm a bit more behind 😮

The release candidate blog post doesn't seem to list any changes in supported versions of SQL Server, but I can see that the NPoco dependency was updated.

[idseefeld]

Looks like it is caused by an dependency update of Microsoft.Data.SqlClient to version 5.0.1.
I found a nice article which also gives a solution:
https://weblog.west-wind.com/posts/2021/Dec/07/Connection-Failures-with-MicrosoftDataSqlClient-4-and-later

The short clue is to add the following setting to the connection string: ;Encrypt=False

Currently you can do this only with the CLI or unattended settings. I have tried Paul Seals Package Script Writer and it worked.

[idseefeld]

@prjseal told me:
;TrustServerCertificate=true; instead of ;Encrypt=false; would be the preferred way to handle this.
I have tried this too and it works.

Btw. there is a quite long discussion about this at dotnet/SqlClient#1402.

Microsoft documentation of TrustServerCertificate can be read here: https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnectionstringbuilder.trustservercertificate?view=dotnet-plat-ext-7.0
and in more details at: https://learn.microsoft.com/en-us/sql/relational-databases/native-client/features/using-encryption-without-validation?view=sql-server-ver16.

My understanding is:
You can use either ;TrustServerCertificate=true; or ;Encrypt=false; in development environments, but in production you should configure the SQL server with a valid certificate.

For the installer UI I think there should be a checkbox for this additional parameter and documentation should mention this issue or new requirement.

[prjseal]

I’ll add this to package script writer next week so it works with SQL Server

[kjac]

Hi everyone 👋

Thank you all very much for digging into this. I do agree, we need to address this in the V12 installer somehow. I will bring it to the team Monday morning for an internal discussion.

How lucky we are to have people like you folk running the RC's through the paces! ❤️

[prjseal]

This has been added to Package Script Writer now.
Example Here

[Zeegaan]

This has been fixed in #14361, sorry for the late update 🚀