SQL server alias recognized with CLR runtime but not CoreCLR

[ketrex2]

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.

[divega]

FYI @YoungGah

[joshfree]

@saurabh500 can you follow up please

[YoungGah]

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

[saurabh500]

Closing issue as aliases are not supported in CoreFX

[CumpsD]

Is there a plan/workaround to support them?

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

[robdmoore]

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.

[saurabh500]

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.

[robdmoore]

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.

[droyad]

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;

[droyad]

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

[gerardog]

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

[ststeiger]

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