SQL server alias recognized with CLR runtime but not CoreCLR

I wrote a test console application and set up a SQL configuration alias in SQL server configuration manager. When running my application with the dnx 4.51 runtime, the database connection is established with no problem and the application functions normally

If I switch the runtime to dnx core 5.0, any attempts to connect to the database from the application fail with the SqlException 'The network path was not found.' Replacing the alias in the connection string with the actual server name resolves the issue. The connection string I'm using is included below:

"connectionString": "Server=aliasName;Database=dbName;Trusted_Connection=True;MultipleActiveResultSets=true"

Perhaps the System.Data.SqlClient coreCLR library does not support SQL aliases and this is not a bug. I just think the different behavior between runtimes is a bit odd and the error it produces is not helpful in deciphering the problem.

@YoungGah

FYI @YoungGah

@saurabh500

@saurabh500 can you follow up please

@Phasiq The support for SQL alias has been intentionally removed from CoreFX.

Closing issue as aliases are not supported in CoreFX

Is there a plan/workaround to support them?

A library somewhere, a snippet to make it work, a roadmap?

Why aren't aliases supported? I find this to be a really useful feature since often people will have (local) or .. or localhost as the server in the connection string, but I prefer to install SQL Express (quicker/easier/smaller install) and then use aliases so I can work with whatever connection string is in source control in the various projects I work on. I'm a consultant so often work in different codebases and don't always have control over the connection string used by the teams I work with.

The SQL server alias is defined on each client machine and it points to a SQL server instance. The alias information is stored in the registry on Windows. This is a OS specific dependency which can work only on Windows. As a result we decided to drop support for sql alias in core fx.

I found that behaviour surprising to be honest. The fact it would be windows only I don't think is a problem given it's transparent to the API surface area so you won't know something is missing on non-windows environments.

…

--

On 11 Mar 2017, at 12:00 am, Saurabh Singh ***@***.***> wrote: The SQL server alias is defined on each client machine and it points to a SQL server instance. The alias information is stored in the registry on Windows. This is a OS specific dependency which can work only on Windows. As a result we decided to drop support for sql alias in core fx. — You are receiving this because you commented. Reply to this email directly, view it on GitHub, or mute the thread.

This workaround does the trick (WOMM)

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;

I've pulled the above code into a lib and NuGet Package https://github.com/droyad/SqlAlias

People do use Sql Aliases. Solving this issue in corefx will kill the need to manually fix it on each .Net Core application. Examples are dbcli/mssql-cli#100 and microsoft/azuredatastudio#71

How about


            if (System.Runtime.InteropServices.RuntimeInformation.IsOSPlatform(System.Runtime.InteropServices.OSPlatform.Windows))
            {
                string key = System.Environment.GetEnvironmentVariable("PROCESSOR_ARCHITECTURE") == "x86"
                ? @"HKEY_LOCAL_MACHINE\SOFTWARE\ Microsoft\MSSQLServer\Client\ConnectTo"
                : @"HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\MSSQLServer\Client\ConnectTo";

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

Navigation Menu

Repository navigation

SQL server alias recognized with CLR runtime but not CoreCLR #14945

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

SQL server alias recognized with CLR runtime but not CoreCLR #14945

Description

Activity

divega commented on Jul 31, 2015

joshfree commented on Oct 5, 2015

YoungGah commented on Oct 5, 2015

saurabh500 commented on Oct 6, 2015

CumpsD commented on Sep 28, 2016

robdmoore commented on Mar 10, 2017

saurabh500 commented on Mar 10, 2017

robdmoore commented on Mar 10, 2017

droyad commented on May 5, 2017

droyad commented on Sep 22, 2017

gerardog commented on Dec 14, 2017

ststeiger commented on Mar 27, 2020

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions