.NET Core can´t connect to SQL DB

3

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.

Visual Studio SQL Server Object Explorer Advanced Properties

c#
asp.net-core
asked on Stack Overflow May 16, 2019 by Janneck Lange • edited May 16, 2019 by Janneck Lange

2 Answers

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;
answered on Stack Overflow Oct 17, 2019 by Kishan Vaishnav
0

If you are connecting from Windows machine A to Windows machine B (server with SQL Server installed), and are getting this error, you need to do the following:

1) Make sure your server name is correct, e.g., no typo on the name.

2) Make sure your instance name is correct and there is actually such an instance on your target machine. If you are not sure about your application, please try both ServerInstance and Server\Instance in your connection string

3) Make sure the server machine is reachable, e.g, DNS can be resolved correctly, you are able to ping the server (not always true).

4) Make sure SQL Browser service is running on the server.

5) If the firewall is enabled on the server, you need to put sqlbrowser.exe and/or UDP port 1434 into exception.

answered on Stack Overflow May 16, 2019 by Sunil Dhappadhule

User contributions licensed under CC BY-SA 3.0