实体框架核心通过SSH隧道连接到MSSQL数据库

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
• 带有数据库的远程主机:127.0.1:14 33可达
• SSH tunneling: L5000 -> 127.0.0.1:1433
• SSH隧道: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.

当我在SQL Management Studio中输入服务器名127.0.0.1,5000时，我就可以连接到我想要的数据库。

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:

因为我可以通过Management Studio进行连接，所以我假设问题是与我的连接字符串或与实体框架核心相关的东西有关。以下是我测试过的一些连接字符串:

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.

编辑1我进行了更多的测试;如果我尝试启动应用程序，SQL Server分析器不会注册任何活动。

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

同时，我也尝试了这篇文章中的方法。使用……tcp:…而不是……tcp、…这也不起作用。

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.

我尝试在Visual Studio - Test Connection中添加一个数据源:OK。我甚至复制了显示在那里的连接字符串，将它粘贴到我的应用程序中，启动了它——没有工作。我想说的是:是的，应用程序确实使用了提供的连接字符串。

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

Visual Studio和SQL Management Studio以一种特殊的方式连接到服务器，然后通过使用连接字符串来实现不同的连接吗?

---

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

根据您的评论，我想添加以下信息:

Project dependencies:

项目依赖项:

• Microsoft.EntityFrameworkCore.SqlServer v2.0.3
• Microsoft.EntityFrameworkCore。v2.0.3状态"置疑"
• Microsoft.EntityFrameworkCore.Tools v2.0.3
• Microsoft.EntityFrameworkCore。工具v2.0.3
• Microsoft.VisualStudio.Web.CodeGeneration.Design v2.0.4
• Microsoft.VisualStudio.Web.CodeGeneration。设计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)

(内部异常没有stacktrace，因为它必须是未定义的)

My DbContext initializes the connection string like this:

我的DbContext初始化连接字符串如下所示:

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.

到目前为止，我高度假设问题必须是DbContext的初始化，否则如何解释MSSQL Studio和应用程序的不同行为呢?

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?

对于那些寻找上下文的人:我正在开发这个应用程序。除了我的lokal开发环境之外，我还有一个生产服务器，在那里我部署了我的主分支。在生产服务器上也是一个独立的数据库，包含生产数据。我遇到的问题是，我在生产服务器上的CLI对数据操作(导入、处理、.. .)抛出一个由数据引起的异常，我无法在开发环境中重现错误。我没有尝试复制整个数据库(几个gb)，而是尝试从开发环境中连接到生产数据库。这将允许我调试异常并修复问题。也许有人对如何使用live数据库调试应用程序有不同的想法?

2 个解决方案

#1

Your error message contains:

你的错误信息包含:

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

(提供程序:命名管道提供程序，错误:40 -无法打开到SQL Server的连接)

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

您是否打开了“SQL Server Configuration Manager”并确保启用了“命名管道”?此设置在3个位置。

#2

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"
}