appsettings.json 中 ConnectionString 中 SQL 别名的问题 [英] Issues with SQL Alias in ConnectionString in appsettings.json

问题描述

在我的 appsettings.json 中，当我使用此代码段时:

In my appsettings.json, when I use this snippet:

"ConnectionStrings": {
    "CssDatabase": "Server=BLUEJAY\MSSQLSERVER2014;Database=CSS;Trusted_Connection=True;" 
}

我可以按预期连接到数据库...没问题.

I can connect to the db as expected... no issues.

但是，当我将其更改为使用 SQL 别名 (CSSDB) 时，如下所示:

However, when I change that to use the SQL Alias (CSSDB), like so:

"ConnectionStrings": {
    "CssDatabase": "Server=CSSDB;Database=CSS;Trusted_Connection=True;" 
}

它已正确配置，因为我可以在 SSMS 中使用此 SQL 别名来毫无问题地连接到数据库.

It is properly configured since I can use this SQL Alias in SSMS to connect to DB without an issue.

返回:

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) --->
System.ComponentModel.Win32Exception: The network path was not found

我正在使用 Microsoft.EntityFrameworkCore.

推荐答案

由于有关 SQL 别名的信息存储在 Windows 注册表中，Microsoft 团队决定放弃对 .NET Core 的支持，因为它不是跨平台解决方案.这是讨论链接.

Since information about SQL Aliases stored in Windows registry the Microsoft team decided to drop its support in .NET Core, because it is not cross-platform solution. Here the link to discussion about it.

但是有解决方法(也来自此讨论)，对我来说效果很好，但请记住，它仍然是仅适用于 Windows 的解决方案:

However there is workaround(also from this discussion), which worked fine for me, but bear in mind it is still Windows only solution:

var builder = new SqlConnectionStringBuilder(config.ConnectionString);

var key = Environment.GetEnvironmentVariable("PROCESSOR_ARCHITECTURE") == "x86"
    ? @"HKEY_LOCAL_MACHINESOFTWARE MicrosoftMSSQLServerClientConnectTo"
    : @"HKEY_LOCAL_MACHINESOFTWAREWOW6432NodeMicrosoftMSSQLServerClientConnectTo";

var newSource = (string)Microsoft.Win32.Registry.GetValue(key, builder.DataSource, null);
if (newSource != null)
    builder.DataSource = newSource.Substring(newSource.IndexOf(',') + 1);

config.ConnectionString = builder.ConnectionString;

如果您没有将 ConnectionString 存储在不同的 C# 类中，您可以像下面那样将 builder.ConnectionString 传递给 ConfigureServices 方法中的服务:

If you not storing ConnectionString in the distinct C# class you can just pass the builder.ConnectionString to services in ConfigureServices method like I did below:

services.AddDbContext<AppDbContext>(
                opt => opt.UseSqlServer(builder.ConnectionString));

这篇关于appsettings.json 中 ConnectionString 中 SQL 别名的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！