c# - .NET Core 无法连接到 SQL DB

Question

我构建了一个新的 .net Core Web API，并连接到 SQL DB。我在将 API 与数据库连接时遇到问题我尝试了本地数据库 "DefaultConnection": "Server=(localdb)\\MSSQLLocalDB; Initial Catalog=ib; Integrated Security=SSPI", 和远程数据库(请参阅 appsettings.json)。

远程:Microsoft SQL Server Express(64 位)11.0.2100.60 系统:Microsoft Windows NT 6.1 (7601)

可以使用 SQL Server Management Studio 或 Visual Studio SQL Server 对象资源管理器连接到这两个数据库。我还有一个正在运行的 ASP.NET Web 应用程序，我在其中使用相同的(远程)连接字符串。

但是使用新的 Core Web API 我无法建立连接。不使用下面的设置，也没有与 NuGet 的 Scaffold-DBContext 连接

如果您需要更多我的代码，您可以询问。非常感谢您的帮助。

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

<小时/>

核心错误(远程数据库)

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 ...

错误:26 - 定位指定的服务器/实例时出错

脚手架错误(对于本地数据库)

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)

错误:50 - LocalDB-Instanz 中的给定名称无效。:“Der angegebene Name der LocalDB-Instanz ist ungültig。”

脚手架错误(远程数据库)

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)

错误:26 - 定位指定的服务器/实例时出错

<小时/>

编辑:

可能重复来自 Unable to connect to SQL Server instance remotely ？可以连接到远程数据库，只有 Core 有问题。

Answer 1

就我而言，可以通过 ASP.NET Web API 和 Windows 窗体应用程序访问数据库。但 .Net Core API 无法正常工作。

解决该问题的方法是在连接字符串中添加端口号。

例如下面指定的连接字符串是 .NET Framework 应用程序的示例。

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

我关注了this用于设置数据库实例端口号的链接。

逗号后面的端口号是关键。

因此将连接字符串更改为如下所示:

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