Entity Framework Core connect to MSSQL database over SSH tunnel

Question

I've seen a lot of posts asking similar questions, but none of which solved the issue I have.

My setup is as follows:

Remote host with database: reachable on 127.0.0.1:1433
SSH tunneling: L5000 -> 127.0.0.1:1433

When I enter the server name 127.0.0.1,5000 in the SQL Management Studio I can connect to the database I want.

If adjust my connection string accordingly and startup my application, I get the following exception:

Type       : SqlException
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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
Source     : Core .Net SqlClient Data Provider
HResult    : -2146232060
TargetSite : .ctor
----- INNER EXCEPTION -----
Type       : Win32Exception
Message    : The system cannot find the file specified
HResult    : -2147467259

Since I can connect over the Management Studio, I assume the issue is with my connection string or something related to the Entity Framework Core. Here are some connection strings I've tested with:

Data Source=127.0.0.1,5000;Network Library=DBMSSOCN;Initial Catalog=Mux;Integrated Security=False;Persist Security Info=False;User ID=...;Password=...;MultipleActiveResultSets=True;Connect Timeout=5;Encrypt=True;TrustServerCertificate=True;Application Name=Mux;ConnectRetryCount=3;ConnectRetryInterval=5
Data Source=127.0.0.1;port=5000;Network Library=DBMSSOCN;Initial Catalog=Mux;Integrated Security=False;Persist Security Info=False;User ID=...;Password=...;MultipleActiveResultSets=True;Connect Timeout=5;Encrypt=True;TrustServerCertificate=True;Application Name=Mux;ConnectRetryCount=3;ConnectRetryInterval=5
Server=127.0.0.1;port=5000;Network Library=DBMSSOCN;Initial Catalog=Mux;Integrated Security=False;Persist Security Info=False;User ID=...;Password=...;MultipleActiveResultSets=True;Connect Timeout=5;Encrypt=True;TrustServerCertificate=True;Application Name=Mux;ConnectRetryCount=3;ConnectRetryInterval=5
Server=127.0.0.1,5000;Initial Catalog=Mux;Integrated Security=False;Persist Security Info=False;User ID=...;Password=...;MultipleActiveResultSets=True;Connect Timeout=5;Encrypt=True;TrustServerCertificate=True;Application Name=Mux;ConnectRetryCount=3;ConnectRetryInterval=5
Server=127.0.0.1;port=5000;Initial Catalog=Mux;Integrated Security=False;Persist Security Info=False;User ID=...;Password=...;MultipleActiveResultSets=True;Connect Timeout=5;Encrypt=True;TrustServerCertificate=True;Application Name=Mux;ConnectRetryCount=3;ConnectRetryInterval=5
Server=tcp,127.0.0.1,5000;Initial Catalog=Mux;Integrated Security=False;Persist Security Info=False;User ID=...;Password=...;MultipleActiveResultSets=True;Connect Timeout=5;Encrypt=True;TrustServerCertificate=True;Application Name=Mux;ConnectRetryCount=3;ConnectRetryInterval=5

Does anyone have an idea why this is?

Edit 1 I conducted some more tests; the SQL Server Profiler doesn't register any activity if I try to start my application.

Also, I tried the approach from this post .. using ... tcp: ... instead of ... tcp, ... .. this also didn't work.

I tried adding a DataSource in the Visual Studio - Test Connection: OK. I even copied the connection string displayed there, pasted it into my application, started it - didn't work. And just to have it said: yes, the application does indeed use the provided connection string.

Do Visual Studio and SQL Management Studio connect to the server in a special way that is different then by using a connection string?

Edit 2 Based on your comments I'd like to add the following information:

Project dependencies:

Microsoft.EntityFrameworkCore.SqlServer v2.0.3
Microsoft.EntityFrameworkCore.Tools v2.0.3
Microsoft.VisualStudio.Web.CodeGeneration.Design v2.0.4

The full log message:

Type       : SqlException
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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
Source     : Core .Net SqlClient Data Provider
HResult    : -2146232060
TargetSite : .ctor
Stacktrace : at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerDatabaseCreator.<>c__DisplayClass18_0.<Exists>b__0(DateTime giveUp)
   at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.<>c__DisplayClass12_0`2.<Execute>b__0(DbContext c, TState s)
   at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.Execute[TState,TResult](IExecutionStrategy strategy, Func`2 operation, Func`2 verifySucceeded, TState state)
   at Microsoft.EntityFrameworkCore.Migrations.HistoryRepository.Exists()
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at ch.wuerth.tobias.mux.Data.DataContextFactory.GetInstance()
   at ch.wuerth.tobias.mux.plugins.PluginMusicBrainz.PluginMusicBrainz.Process(String[] args) in C:\Users\Tobias\Desktop\dev\mux-cli\plugins\PluginMusicBrainz\PluginMusicBrainz.cs:line 136
   at ch.wuerth.tobias.mux.Core.plugin.PluginBase.Work(String[] args)
   at ch.wuerth.tobias.mux.App.Program..ctor(String[] args) in C:\Users\Tobias\Desktop\dev\mux-cli\App\Program.cs:line 47
Data :
 -> [HelpLink.ProdName, Microsoft SQL Server]
 -> [HelpLink.EvtSrc, MSSQLServer]
 -> [HelpLink.EvtID, 2]
 -> [HelpLink.BaseHelpUrl, http://go.microsoft.com/fwlink]
 -> [HelpLink.LinkId, 20476]
----- INNER EXCEPTION -----
Type       : Win32Exception
Message    : The system cannot find the file specified
HResult    : -2147467259

(the inner exception has no stacktrace because it must be undefined)

My DbContext initializes the connection string like this:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseSqlServer("..my connection string..");
}

Edit 3 I highly assume by now that the issue must be with the initialization of the DbContext, how else would one explain the different behaviors of the MSSQL Studio and the application.

Also for the ones seeking some context: I'm developing this application. Besides my lokal development environment I have the production server where I deploy my master branch. On the production server is also a standalone database with the production data like one would expect. The issue I have is that my CLI for data manipulation (import, processing, ..) on the production server throws an exception which is caused by the data and I cannot reproduce the error on my development environment. Instead of trying to copy the whole database (which is several gigabytes) I thought I just try to connect to the production database from within my development environment. This would allow me to debug the exception and fix the issue. Maybe some one has an alternative idea on how I could debug my application with the live database?

To clarify: you are running this solution on you local box? Not inside a VM or docker or something that would not share your localhost's routing tables? — Jeremiah Cooper, May 08 '18 at 20:51
Judging from your inner exception it looks like VS isn't able to locate your System.Data.SqlClient dll (not for certain on this as all that is posted is the inner exception message). This could be a bug related to this [github bug report](https://github.com/dotnet/corefx/issues/28139). This person was able to get it running by updating System.Data.SqlClient to 4.4.3 and I realize that I am heavily assuming you aren't on this already — bman7716, May 09 '18 at 13:59
@bman7716 thanks for your input, but I don't think this applies in my case. I've updated my answer to clarify some things — товіаѕ, May 09 '18 at 14:34
@TobiasWürth have you tested your solution against a local database (if that is possible)? Perhaps on SQL Express? — Jeremiah Cooper, May 09 '18 at 15:22
no it still runs on my local machine but instead of connecting to the local database it should connect to a remote database over a ssh tunnel — товіаѕ, May 10 '18 at 16:45
can you do a `route print` to make sure the ssh route is in place? How are you establishing the SSH tunnel? Bitvise Tunnelier or ??? — Jeremiah Cooper, May 10 '18 at 17:02
since you are able to connect using some apps but not others I wonder if it is a network security issue and the user context the app is running under. You might try creating a spike console app that just opens a connection to the remote host and runs a query. Then try running the app under different user contexts to see if it works (like under elevated privileges). — Jeremiah Cooper, May 10 '18 at 17:10
The way that SqlClient falls back from tcp to named pipes is potentially hiding the error we want to see. Can you post the error message you get when trying the "tcp:..." connection string? it will be different from the one posted above. — StrayCatDBA, May 11 '18 at 20:25
@TobiasWürth I had a similar issue on my app deployment, try re-adjusting your connection string by removing things from there. for example `Data Source=127.0.0.1,5000; Initial Catalog=Mux; User ID=...;Password=...;` etc. I am not so sure but give it a try. — Robin, May 17 '18 at 02:43
Just going to ask a stupid question here, but have you tried disabling your firewall completely, and see if that helps? because if it works without your firewall on, there is likely an issue there. — Morten Bork, May 22 '18 at 13:35
removing `Encrypt=True;TrustServerCertificate=True;` from the conncetionstring may solve the problem — Elyas Esna, May 22 '18 at 14:43

score 1 · Accepted Answer · answered May 21 '18 at 12:59

1

Your error message contains:

(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Have you opened "SQL Server Configuration Manager" and ensured that "Named Pipes" is Enabled? This setting is in 3 locations.

answered May 21 '18 at 12:59

pacreely

1,881
2
10
16

it's the little things that are hard to find. thank you very much – товіаѕ May 22 '18 at 17:34

score 0 · Answer 2 · answered May 11 '18 at 11:51

0

You can try to add this connection string

"ConnectionStrings": {
   "DbConnection": "Data Source=tcp:127.0.0.1,5000\\DBMSSOCN;Initial Catalog=mdstsbase;Integrated Security=false;User ID=user;Password=password;Application Name=application name"
}

answered May 11 '18 at 11:51

Serg csharp .Net Developer

25
4

This didn't help, same exception – товіаѕ May 13 '18 at 16:50

Entity Framework Core connect to MSSQL database over SSH tunnel

2 Answers2