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.

score 43 · Accepted Answer · answered Jul 26 '17 at 15:12

43

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

answered Jul 26 '17 at 15:12

user2771704

5,994
6
37
38

1

Worked nicely. And +1 for `services.AddDbContext(opt => opt.UseSqlServer(builder.ConnectionString));` – Eric Jul 26 '17 at 17:21
5

I've wrapped up this code in a library/NuGet package: https://github.com/droyad/SqlAlias – Robert Wagner Sep 22 '17 at 04:41
2

Just wasted the whole afternoon trying to figure out why the hell my connection string was all of a sudden not working... – niaher Nov 22 '17 at 14:35
1

Is there any way to use this (or something similar) when working with EF Core code-first migrations? At the moment I have this working fine for using my application, but I have to remove the alias from the connection string if I want to add/run a migration, so I lose a lot of the benefit. – Tim Apr 30 '20 at 11:39
This code is working fine for me If I connect so MicroSoft SQL Server, did anyone to connect to OracleDataSource, would the above code work when connecting to OracleDataSource – user804401 Oct 12 '21 at 10:45
I am trying to run this in OpenShift and receiving the error " ORA-12545: Network Transport: Unable to resolve connect hostname" – user804401 Oct 12 '21 at 10:54
The `PROCESSOR_ARCHITECTURE` check is unnecessary, and changes the behaviour of the connection string. Aliases can be defined separately for 32-bit and 64-bit applications. A .NET Framewwork app will use the alias from the list that matches the architecture it was built for. This code will always use the 32-bit version. – Richard Deeming Jun 27 '22 at 07:08

score 4 · Answer 2 · answered Sep 07 '22 at 09:56

4

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.

answered Sep 07 '22 at 09:56

Grzegorz Smulko

2,525
1
29
42

In my case using the SqlClient v5.0.1 causes exception: ```A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) ---> System.ComponentModel.Win32Exception (0x80090325): The certificate chain was issued by an authority that is not trusted.``` I can add a `"TrustServerCertificate=True;"` to connection string to resolve it, but it not so convenient in comparison to the workaround code in other answer. – Roganik Nov 15 '22 at 22:57
That was a helpful comment @Roganik but also a separate topic, you will not be able to use the accepted answer forever and most people running .NET core on servers will at some point need to run on linux as well. This answer is currently much more useful to people using .NET at work. – VoronoiPotato Dec 13 '22 at 23:31

Issues with SQL Alias in ConnectionString in appsettings.json

2 Answers2

Linked