.NET Core can´t connect to SQL DB

Question

I build a new .net Core Web API with connection to SQL DB. I have problems to connect the API with my Database I tried a local DB "DefaultConnection": "Server=(localdb)\\MSSQLLocalDB; Initial Catalog=ib; Integrated Security=SSPI", and a remote (see appsettings.json).

Remote: Microsoft SQL Server Express (64-bit) 11.0.2100.60 System: Microsoft Windows NT 6.1 (7601)

It is possible to connect to both DB´s with SQL Server Management Studio or Visual Studio SQL Server Object Explorer. I also have a working asp.net Web Application where I use the same (remote) connection string.

But with the new Core Web API I cant get a connection. Not with the settings below and also no connection wirh the Scaffold-DBContext from NuGet

If you need more of my Code you can ask. Thank you very much for your help.

 Scaffold-DbContext "Connection String" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

Startup.cs

 services.AddDbContext<ibContext>(options =>
            options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

appsettings.json

"ConnectionStrings": {
    "DefaultConnection": "Server=192.168.1.XXX\\SQL2012; Initial Catalog=IBCOREDB_XXX; User ID=XXXX;Password=XXXXX;",
  },

---

Core Error (remote DB)

Exception thrown: 'System.Data.SqlClient.SqlException' in System.Data.SqlClient.dll
'iisexpress.exe' (CoreCLR: clrhost): Loaded 'C:\Program Files\dotnet\shared\Microsoft.NETCore.App\2.2.4\System.Diagnostics.StackTrace.dll'. Cannot find or open the PDB file.
'iisexpress.exe' (CoreCLR: clrhost): Loaded 'C:\Program Files\dotnet\shared\Microsoft.NETCore.App\2.2.4\System.Reflection.Metadata.dll'. Cannot find or open the PDB file.
Microsoft.EntityFrameworkCore.Database.Connection:Error: An error occurred using the connection to database 'IBCOREDB_XXX' on server '192.168.1.XXX\SQL2012'.

System.Data.SqlClient.SqlException (0x80131904): 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: 26 - Error Locating Server/Instance Specified)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at ...

Error: 26 - Error Locating Server/Instance Specified

Scaffold Error (for local DB)

System.Data.SqlClient.SqlException (0x80131904): 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: 50 - Local Database Runtime error occurred. Der angegebene Name der LocalDB-Instanz ist ungültig.
) ---> System.ComponentModel.Win32Exception (0x89C5011B): Unknown error (0x89c5011b)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken)

Error: 50 - The given name from the LocalDB-Instanz is not valid.:"Der angegebene Name der LocalDB-Instanz ist ungültig."

Scaffold Error (Remote DB)

System.Data.SqlClient.SqlException (0x80131904): 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: 26 - Error Locating Server/Instance Specified)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken)

Error: 26 - Error Locating Server/Instance Specified

---

Edit:

Possible dublicate from Unable to connect to SQL Server instance remotely? It is possible to get a connection to the remote Database, only Core has a problem with it.

Answer 1

In my case the database was accessible via ASP.NET Web API and Windows Forms app. But the .Net Core API wasn't working.

The solution to the problem is to add port number in the connection string.

For e.g. The connection string specified below is example of the .NET Framework app.

data source=SERVER\SQLSERVER2017;initial catalog=DBNAME;persist security info=True;user id=ADMIN;password=ADMIN123;MultipleActiveResultSets=True;App=EntityFramework

I followed this link to set port number to the DB instance.

The port number after comma is the key.

So change connection string to something like this:

Server=SERVER\\SQLSERVER2017,1433;Database=DBNAME;User Id=ADMIN; Password=ADMIN123;MultipleActiveResultSets=True;

Answer 2

We hit a similar (or identical?) issue. .NET Core didn't work with a connection string but .NET Framework worked just fine with that same string. It turned out to be because our connection string was using an SQL Alias rather than the IP hostname of the database server, and .NET Core dropped SQL Alias support because it was too Windowsy/registry-y.

Changing the connection string to use the IP hostname or number resolved the issue.

• .NET team discussion on issue: https://github.com/dotnet/corefx/issues/2575
• Related SO issue: Issues with SQL Alias in ConnectionString in appsettings.json for instance.