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.

推荐答案

由于有关Windows注册表中存储的SQL别名的信息，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_MACHINE\SOFTWARE\ Microsoft\MSSQLServer\Client\ConnectTo"
    : @"HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\MSSQLServer\Client\ConnectTo";

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屋！