Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Issues with SQL Alias in ConnectionString in appsettings.json

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.

like image 623
Eric Avatar asked Jul 26 '17 12:07

Eric


People also ask

How alias works in SQL Server?

SQL aliases are used to give a table, or a column in a table, a temporary name. Aliases are often used to make column names more readable. An alias only exists for the duration of that query. An alias is created with the AS keyword.

How do I call a connection string from AppSettings json?

Reading Connection String from AppSettings. json file using IConfiguration interface. In the below example, the IConfiguration is injected in the Controller and assigned to the private property Configuration. Then inside the Controller, the Connection String is read from the AppSettings.

Which is the ConnectionString property?

The value of the ConnectionString property is a connection string that includes the source database name and the parameters you need to establish the connection. The default value of the ConnectionString property is an empty string. The Server attribute is mandatory in all situations.

How do you give a connection string in AppSettings json for Windows authentication?

Adding the AppSettings.json file Then click Add, then New Item and then choose App Settings File option (shown below) and click Add button. Once the File is created, it will have a DefaultConnection, below that a new Connection String with Windows Authentication is added.


1 Answers

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));
like image 183
user2771704 Avatar answered Oct 13 '22 04:10

user2771704