I'm working on a university group project that was previously using MySQL but the team decided to move to MS SQL Server Express. I'm the only team member using a Mac, so I'm trying to get the project to connect to a docker container. I'm working in Visual Studio for Mac.
I've created and started an SQL Express 2017 container with this:
docker run -d --name sqlexpress -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=MSSQLContainer1' -e 'MSSQL_PID=Express' -p 1433:1433 -d mcr.microsoft.com/mssql/server:2017-latest-ubuntu
Which looks like this when running:
CONTAINER ID: e448f6e4df4
IMAGE: mcr.microsoft.com/mssql/server:2017-latest-ubuntu
COMMAND: "/opt/mssql/bin/sqls…"
CREATED: 21 hours ago
STATUS: Up 21 hours
PORTS: 0.0.0.0:1433->1433/tcp
NAMES: sqlexpress
And when I run SQL Server inside the docker and enter SELECT @@VERSION:
Microsoft SQL Server 2017 (RTM-CU16) (KB4508218) - 14.0.3223.3 (X64) Jul 12 2019 17:43:08 Copyright (C) 2017 Microsoft Corporation Express Edition (64-bit) on Linux (Ubuntu 16.04.6 LTS)
I am able to connect to the to the container from both Azure Data Studio or DBeaver without any issues and create a database called HelpdeskSystem.
But I can't for the life of me get our .NET Core app to connect to the database.
This is my current connection string; though, I've tried different variations:
"Server=localhost\\sqlexpress,1433;Database=HelpdeskSystem;User=sa;Password=MSSQLContainer1;"
Also, this is what the connection string that my teammates are using on their Windows machines looks like (and I've tried including those extra properties in my string as well):
"server=localhost\\sqlexpress;Database=HelpdeskSystem;Trusted_Connection=True;Integrated Security=True;MultipleActiveResultSets=true"
When I attempt to run a unit test that requires database connectivity, I get this exception:
InnerException: {System.Net.Sockets.SocketException (0x80004005): Undefined error: 0 at System.Data.SqlClient.SNI.SSRP.GetPortByInstanceName(String browserHostName, String instanceName) at System.Data.SqlClient.SNI.SNIProxy.CreateTcpHandle(DataSource details, Int64 timerExpire, Object callbackObject, Boolean parallel)}
Message: "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)"
I'm not sure if my string is wrong or if there's some other underlying issue preventing the web app from connecting but I'm definitely able to to connect to the container using Azure Data Studio, so I don't see why the web app wouldn't be able to.
docker run --name sqlexpressapplies thesqlexpressname to the Docker container, not to the SQL Server instance inside it. Probably your connection string should be"Server=localhost,1433;Database=HelpdeskSystem;User=sa;Password=MSSQLContainer1;"i.e.: without the instance name. – AlwaysLearning