MSSQL Connection String Fails on Orchard Core Setup - "The provided connection string is invalid or server is unreachable."

[GeospatialAustralia]

I am doing the basic setup for orchard core and am getting "The provided connection string is invalid or server is unreachable."
This occurs when I use a remote sql connection or localhost.
My database is created in sql already. I am using the sa login

Connection Strings I have tried include (I use a custom port for security and 1433 is not active);

1. Server=sql.gsa.land,2990;Database=GSA;User Id=sa;Password=mypwd
2. Server=localhost;Database=GSA;Trusted_Connection=True;MultipleActiveResultSets=true

The same result occurs if I run the VS 2022 project on my machine or on the web server as in the 2. connection string example.
The database is right there and very reachable.
My "Recipe" is "Agency" as it really is just a test website.

I have setup the website in IIS and assigned a folder. The established app pool is core compliant using 'No Managed Code'
I am already running .net core sites so all hosting components are fine.
I am using a specific user; Administrator in the App Pool

Does anyone have any ideas on how to resolve this?. I did not think I would be stuck at the setup page.....
Thankyou for your time!

[hishamco]

Try to add Encrypt=false in your connection string

[GeospatialAustralia]

Legend Hishamco that worked perfectly...the cs now looks like this and works fine
Server=myserverurl,myport;Database=GSA2;User Id=sa;Password=mypwd;Encrypt=false
Thankyou

[ThiemeNL]

Hi, I have exactly the same issue. My connection string is working in C# with and without Encrypt=false.

However the orchard setup always gives me "The provided connection string is invalid or server is unreachable."

using var cnn = new SqlConnection(@"Server=localhost;Database=dev-orchard;User Id=dev;Password=mypass;Encrypt=false");
cnn.Open();

Console.WriteLine("Connection Open!");

I'm not sure which port the sql server is running. But I'm sure the connection string is valid, since it is working in a C# console application. Do you have any advice?

[ThiemeNL]

No worries, I found an empty connection string in the default appsettings.json. While the setup was requesting a connection string I didn't except that the default app settings had some influence on the setup.

[GeospatialAustralia]

Hello ThiemeNL

It is good to hear you found a suitable CS. I was a bit lost when I kept entering what seemed to be a normally valid CS. I even went the extreme option of SQlLite and ran the setup on the web server testing 'localhost' which are two things I would never normally do. In the same way ran normal sql and the setup as localhost on the web server which breaks all the rules of remote deployment systems also. My endgame will see this running totally in azure assuming I have something worth running.

As for the port unless you have customised your 'SQL Configuration' ports on the SQL Server it should be the default 1433. We just migrated from default ports long ago to try and reduce hack simplicity and likelihood. There is even a trick to getting that to work in the TCP/IP configuration which is made more frustrating by the fact there is no warning or direction when trying to connect with SSMS. It simply just doesn't work until it is done right. That is another issue entirely.

As for this one you seem to have found the 'appsettings.json' that gets updated by a successful setup process and is written to "OrchardCore.Cms.Web\App_Data\Sites\Default\appsettings.json".

For a default sql port you should only ever need to use the following so it seems.
"Server=yourserver;Database=dbname;User Id=sqlusername;Password=sqluserpwd;Encrypt=false"

I am road testing Orchard Core at this point in time to assess its suitability as a fast, adaptable and secure cms to run some high traffic and image dependent websites on premise or in azure. I still have a lot to learn about it yet, but it could be what I am after. It is a large departure from ASPX for me and I feel like I understand about 0.1% of it so far but it has potential and most importantly good community support.

Good Luck

[ThiemeNL]

Thanks for your reply. I also went for the SQLlite option after a while trying without any luck. But then I notice that also the SQLlite option was giving me the same error. Then I found out the empty CS in the root of my project: OrchardCore.Cms.Web\appsettings.json. It would be nice if Orchard tells which CS was invalid. In my case the default CS was invalid, but the tenant CS was valid. I'm using it like you said (without specifying a port) "Server=yourserver;Database=dbname;User Id=sqlusername;Password=sqluserpwd;Encrypt=false"

Btw, we started using Orchard a couple of months ago and I'm quit happy with the solution. Specially because we love .net