asp.net core - Issues with SQL Alias in ConnectionString in appsettings.json

Question

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.

Answer 1

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_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;

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));