I tried every single variation that I could find on the internet. Everything. And still every time I get the very same exception:
System.Data.SqlClient.SqlException: '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: TCP Provider, error: 25 - Connection string is not valid)'
With this weird inner exception:
SocketException: The operation completed successfully
When I specify the port directly, then I end up with this wonderful exception:
An attempt was made to access a socket in a way forbidden by its access permissions
No matter what, it doesn't work. Moreover I'd like to have MS SQL Management Studio open while testing (that was the only process which could block the port).
So why does it not work? Management Studio can connect with IP\InstanceName without a problem. Why is the System.Data.Sql.SqlConnection class incapable of doing so?
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = "tcp:***.***.***.***\\SQLEXPRESS";
builder.InitialCatalog = "MyDB";
builder.Password = "MyPass";
builder.UserID = "ID";
builder.IntegratedSecurity = false;
_connection = new SqlConnection(builder.ToString());
FYI: I am using the ConnectionStringBuilder, so there shouldn't be any issue. Server name, instance name, SQL user and password are valid and I did explicitly set Integrated Security = False.
The problem was (which was not really clear) that UWP requires a capabilitiy in order to run that code. I read somewhere that you would need Enterprise Authentication which was not the one. Since my program and server are in a closed, private network I enabled Private Networks (Client & Server).
The one you would need is Internet (Client & Server). Sadly the exceptions don't hint to that and the fact that SqlClient on UWP requires this to work is nearly nowhere mentioned or not stressed enough so you wouldn't overlook it.
Solution found in this .Net Standard issue.