I've been trying to setup an SQL Server 2016 Express instance for my ASP.NET Core app. I've gotten it to a point where I can connect to the database with a dev version on my local development machine, but not with a published version on the server (that's also hosting the SQL Server Instance)
I'll call the local dev version the "dev server", and the remote published version the "real server". Both servers access the same SQL Server instance
On the dev server I connect without issues to the SQL server with the following connectionString in my appSettings.json
Data Source=10.50.70.50;Integrated Security=False;User ID=voetbal;Password=*******;Connect Timeout=15;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False
When I try to connect from the real server I get the error
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: SQL Network Interfaces, error: 52 - Unable to locate a Local Database Runtime installation. Verify that SQL Server Express is properly installed and that the Local Database Runtime feature is enabled.) On the remote machine I've tried the following connectionStrings in both appsettings.json and as an environment variable:
With IP:
Data Source=10.50.70.50;Integrated Security=False;User ID=voetbal;Password=*****;Connect Timeout=15;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False
With localhost instead of IP:
Data Source=localhost;Integrated Security=False;User ID=voetbal;Password=*****;Connect Timeout=15;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False
With Local Machine name:
Data Source=WIN-DMM50393I9A;Integrated Security=False;User ID=voetbal;Password=*****;Connect Timeout=15;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False
With Local Machine name and SQL instance name:
Data Source=WIN-DMM50393I9A\MSSQLLOCALDB;Integrated Security=False;User ID=voetbal;Password=*****;Connect Timeout=15;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False
As localdb:
Data Source=(localdb)\\MSSQLLOCALDB;Integrated Security=False;User ID=voetbal;Password=*****;Connect Timeout=15;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False
Then I thought, maybe it should be "Server" instead of "Data Source", so I tried them all again with that substitution. Still the same error.
So something tells me the connection string is not the issue here. Whenever I search the internet for the error, I only find posts about people trying to connect to an actual LocalDB instance, and not an SQL Server Express Instance. And if my app was indeed trying to connect to a LocalDB instance, then why is my dev server working with the database?
I don't know what else I can do to troubleshoot this issue. Any ideas?
