本文是小编为大家收集整理的关于关于appsettings.json中ConnectionString的SQL别名的问题的处理/解决方法，可以参考本文帮助大家快速定位并解决问题，中文翻译不准确的可切换到English标签页查看源文。
问题描述
在我的appsettings.json中，当我使用此片段时:
"ConnectionStrings": {
"CssDatabase": "Server=BLUEJAY\\MSSQLSERVER2014;Database=CSS;Trusted_Connection=True;"
}
我可以按预期连接到DB ...没有问题.
但是，当我将其更改为使用SQL别名(CSSDB)时，像这样:
"ConnectionStrings": {
"CssDatabase": "Server=CSSDB;Database=CSS;Trusted_Connection=True;"
}
它是正确配置的，因为我可以在ssms中使用此SQL别名无问题连接到DB.
此返回:
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中，因为它不是跨平台解决方案.这里链接到讨论.
但是，有解决方法(也是从这个讨论中)，这对我来说很好，但是请记住，它仍然是Windows的解决方案:
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;
如果您不在不同的C#类中存储ConnectionsTring，则可以像我下面一样将builder.ConnectionString传递给configerservices方法中的服务:
services.AddDbContext<AppDbContext>(
opt => opt.UseSqlServer(builder.ConnectionString));
其他推荐答案
本机对别名的支持在Microsoft.data.sqlclient 5.0.0 2022年8月5日发布.
解决方法不再需要.
问题描述
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.
However, when I change that to use the SQL Alias (CSSDB), like so:
"ConnectionStrings": {
"CssDatabase": "Server=CSSDB;Database=CSS;Trusted_Connection=True;"
}
It is properly configured since I can use this SQL Alias in SSMS to connect to DB without an issue.
This returns:
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
I am using Microsoft.EntityFrameworkCore.
推荐答案
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.
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;
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));
其他推荐答案
Native support for aliases was added in Microsoft.Data.SqlClient 5.0.0 released on 5 August 2022.
The workaround should not be necessary anymore.
更多相关问答
联系站长