Connecting to SQL Express from WSL - SQL Network Interfaces, error: 25

Question

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:

• ensured my inbound ports are setup for windows firewall; 1433 for Sql Server and 1434 for Sql Server Browser
• ensured that sql server and sql server browser are running
• ensured that TCP/IP is Enabled in SQL Server Network Configuration > Protocols for SQLEXPRESS
• Ensured that the IP Address for wsl is Active, Enabled, and has TCP port 1433 TCP/IP properties
• Ensured that TCP Port is set to 1433 for TCP/IP properties > IPAll
• I can successfully ping the ip
• I can successfully telnet the ip
• I've checked that the instance name is SQLEXPRESS
• Ensured that my database Allows remote connections
• Ensured that the database allows mixed sql server and windows authentication
• I've tried many variations of the connection string - with and without Encrypt, TrustServerCertificate, User Id, and Password (used sa)
• I've tried several variations of the Server: IP,Port, IP,Port/InstanceName, IP/InstanceName, InstanceName, localhost/InstanceName, MachineName/InstanceName, IP/InstanceName,Port
• I've tried prefixing the above with tcp:

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?

Answer 1

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.