12

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

one noa
  • 345
  • 1
  • 3
  • 10
Janneck Lange
  • 882
  • 2
  • 11
  • 34
  • wait - so you're trying to simultaneously connect to 2 separate databases? – jazb May 16 '19 at 07:16
  • 1
    no i only want to connect the remote DB. But I tried everything :/ – Janneck Lange May 16 '19 at 07:17
  • looks similar to this question: https://stackoverflow.com/questions/6987709/unable-to-connect-to-sql-server-instance-remotely – jazb May 16 '19 at 07:20
  • For a remote server with User and password you could try using `Data Source=` instead of `Server=` – DTul May 16 '19 at 07:20
  • Possible duplicate of [Unable to connect to SQL Server instance remotely](https://stackoverflow.com/questions/6987709/unable-to-connect-to-sql-server-instance-remotely) – jazb May 16 '19 at 07:20
  • @DTul did not change anything - same error – Janneck Lange May 16 '19 at 07:27
  • @JohnB there is no solution for me – Janneck Lange May 16 '19 at 07:37
  • it could be a firewall problem or any of a dozen other traps. you have to systematically eliminate possibilities by trial an error i'm afraid. – jazb May 16 '19 at 07:40
  • have you tried using visual studio to connect? ie using the connect to server in the tools? – jazb May 16 '19 at 07:41
  • @JohnB i tried with SQL Server Management Studio and Visual Studio SQL Server Object Explorer – Janneck Lange May 16 '19 at 07:42
  • try sql server config manager on the server itself and see what protocols are enabled etc – jazb May 16 '19 at 07:45
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/193444/discussion-between-janneck-lange-and-johnb). – Janneck Lange May 16 '19 at 07:51
  • @JanneckLange the error is clear - you try to connect to a non-existent server. Either the IP is wrong or the instance name. `192.168.1.XXX\\SQL2012` is *definitely* wrong, as SQL Server Express always runs as named instance called `SQLEXPRESS`. You should have used `server\\SQLEXPRESS` – Panagiotis Kanavos May 16 '19 at 08:06
  • @Panagiotis Kanavos take a look at the image - "connection succeeded" – Janneck Lange May 16 '19 at 08:09
  • @JanneckLange and no, .NET Core has no problems connecting to SQL Server. Thousands of developers would have noticed if it did. I use .NET Core to import data into SQL Server. You've posted a lot of contradictory connection strings and IPs in this question though. LocalDB is an *embedded* database, installed as part of SQL Server Express. So the connection string in your config file and all other attempts will fail. A LocalDB connection string can't connect to a database server on the other hand. What *is* the relevant information after all? Where are you trying to connect? – Panagiotis Kanavos May 16 '19 at 08:10
  • @JanneckLange you've made a lot of contradictory claims and one 100% wrong - that .NET Core cant' connect to SQL Server. The image *doesn't* show a SQL Server Express connection string and definitely not a LocalDB. – Panagiotis Kanavos May 16 '19 at 08:12
  • Let us continue here https://chat.stackoverflow.com/rooms/193444/discussion-between-janneck-lange-and-johnb – Janneck Lange May 16 '19 at 08:15
  • The Problem is not the name of the Database or the Server or Core itself. I have a problem to get the connection to the Server with Core. On ther ways i get a connection – Janneck Lange May 16 '19 at 08:25
  • any solution to this @JanneckLange ? i am having same issue... – Burk Mar 09 '20 at 10:34
  • @Burk not really, look below – Janneck Lange Mar 13 '20 at 09:28
  • these two problems look similar and here is what was my problem. https://stackoverflow.com/a/60703731/3411340 – Burk May 24 '20 at 20:24
  • My SQL server is hosted in the local network and I was using the fixed IP address of that machine in the connection string, one morning my application couldn't reach the database even though the fixed IP address hadn't changed. I configured it to connect basis the host name and the connection is working again. I am too tired to understand why this is happening, but for others out there: try switching between IP address and host name if you have this issue... :( – Superman.Lopez Dec 09 '20 at 02:20

3 Answers3

13

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;
Kishan Vaishnav
  • 2,273
  • 1
  • 17
  • 45
  • Same thing happened to me, I was using `localhost:1433`, but it should be `localhost,1433` for some reason – Sudara Apr 22 '20 at 23:33
  • I've torn my hair out for the past day trying to get this to work. Thank you so much! The port reference was the key to resolve my issue! – LostLogic May 31 '23 at 07:08
3

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.

Scott Stafford
  • 43,764
  • 28
  • 129
  • 177
1

The Problem in this case was the instance Server=192.168.1.XXX\\SQL2012;. I don't know why but the system can't handle it. I use another Server instead (without instance).

Real solutions are welcome.

Janneck Lange
  • 882
  • 2
  • 11
  • 34