I've been working through this for nearly two days and I'm running out of options. Maybe you guys can help.
Background: I migrated a .netcore api from Windows to WSL2 (Ubuntu 20.04). My Database is running on Windows. When attempting to run dotnet ef database update
it gives me the error:
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: SQL Network Interfaces, error: 25 - Connection string is not valid: Connection string is not valid)
My Connection string (ip is placeholder):
"Server=192.168.0.1,1433/SQLEXPRESS,1433;Database=MyDatabase;Integrated Security=True;MultipleActiveResultSets=True;Encrypt=no"
I've googled that error and tried (or have already done) many of the suggested solutions, including:
On the windows side using powershell I ran the following:
$sqlConn = New-Object System.Data.SqlClient.SqlConnection
$sqlConn.ConnectionString = "Server=localhost,1433;Database=MyDatabase;Encrypt=no;TrustServerCertificate=True;User ID=sa;Password='';Integrated Security=True;MultipleActiveResultSets=True"
$sqlConn
It prints out all of the correct information. Omitting the Encrypt=no
will give me an error 40 which is related to a self-signed ssl cert. Omitting the User Id and Password still successfully connect.
What am I missing?
You need to remove the instance name if you are using a fixed port. You certainly shouldn't be specifying the port after the instance name.
And SQL Authentication uses Integrated Security=False
. If it's true
then it uses Windows Authentication and it makes no sense to pass a username and password. You can't use Windows Authentication from WSL.
Server=192.168.0.1,1433;Database=MyDatabase;MultipleActiveResultSets=True;User ID=sa;Password=''
Add TrustServerCertificate=True
if really necessary, be aware that it's a security risk.