如何在环境变量中正确存储连接字符串以供生产 ASP.Net Core MVC 应用程序检索 [英] How to correctly store connection strings in environment variables for retrieval by production ASP.Net Core MVC applications

问题描述

我正在开发一个 ASP.NET Core MVC 应用程序，但我的连接字符串有问题.

I am working on an ASP.NET Core MVC application and I am having an issue with my connection strings.

我的生产服务器上有一个 ASPNETCORE_ENVIRONMENT 变量设置为 Production，而我的生产服务器是运行 IIS 的 Windows Server 2012R2.我还在生产服务器上安装了 DotNetCore.1.0.4_1.1.1-WindowsHosting.exe.

I have an ASPNETCORE_ENVIRONMENT variable set to Production on my production server and my production server is a Windows Server 2012R2 running IIS. I also have the DotNetCore.1.0.4_1.1.1-WindowsHosting.exe installed on the production server.

在开发过程中，我使用 UserSecrets 来保存我的连接字符串.这工作正常.

During development, I am using UserSecrets to hold my connection string. This is working properly.

对于生产，我希望我的连接字符串在生产服务器上的环境变量中，这就是我遇到问题的地方.我怀疑这可能是我构建环境变量的方式.

For production, I want my connection strings in environment variables on my production server and this is where I am having an issue. I suspect that it may be in the way I am structuring the environment variable.

当我尝试访问生产中的数据库时，我收到一个错误，表明它无法解析连接字符串.

When I try to access the database in production I get an error indicating basically that it can't cannot parse the connection string.

An exception occurred in the database while iterating the results of a query.

System.ArgumentException: Keyword not supported: '"server'.
at System.Data.Common.DbConnectionOptions.ParseInternal(Dictionary`2 
parsetable, String connectionString, Boolean buildChain, Dictionary`2 synonyms)
at System.Data.Common.DbConnectionOptions..ctor(String connectionString, Dictionary`2 synonyms)
at System.Data.SqlClient.SqlConnectionString..ctor(String connectionString)

如果我将连接字符串放在 appSettings.json 中，生产服务器就可以正常工作.

If I put the connection string in appSettings.json, the production server works just fine.

所以，这是我的 appSettings.json 文件的示例，它显示了在生产中工作的连接字符串；

So, here is an example of my appSettings.json file showing the connection string that works in production;

{
  "ConnectionStrings": {
     "TestDb": "Server=TestServer;Database=TestDb;Persist Security Info=True;User ID=TestUser;Password=testpassword;MultipleActiveResultSets=true"
  },

    ...
    ...
    ...
  }
}

如果我将此 appSettings.json 文件部署到生产环境，它可以正常工作.

If I deploy this appSettings.json file to production, it works OK.

在我的 ASP.Net Core 应用程序的 Startup.cs 文件中，我有以下内容；

In my ASP.Net Core application, in the Startup.cs file, I have the following;

public Startup(IHostingEnvironment env)
{
    var builder = new ConfigurationBuilder()
        .SetBasePath(env.ContentRootPath)
        .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)
        .AddJsonFile($"appsettings.{env.EnvironmentName}.json", optional: true);

    if (env.IsDevelopment())
    {
        // For more details on using the user secret store see https://go.microsoft.com/fwlink/?LinkID=532709
        builder.AddUserSecrets<Startup>();
    }

    builder.AddEnvironmentVariables();
    Configuration = builder.Build();

}

我的理解是最后列出的 builder.add... 具有优先级，所以在我的情况下，如果环境中存在连接字符串，它应该优先于 appsettings 中的任何内容.

My understanding is that the last builder.add... listed has priority, so in my case, if a connection string exists in the environment, it should take priority over anything in appsettings.

所以在生产中，如果我使用下面的 appSettings.config 文件；

So in production, if I use the following appSettings.config file;

{
  "ConnectionStrings": {
     "TestDb": "Placeholder for connection string. Overridden by User Secrets in Development and Environment Variables in Production. "
  },

    ...
    ...
    ...
  }
}

如果我有连接字符串的环境变量，那么我在该 appsettings.json 文件中的 ConnectionStrings:TestDb 值应该是什么.

It should not matter what I have as a value for ConnectionStrings:TestDb in that appsettings.json file if I have an environment variable for the connection string.

下面列出的是我正在使用的环境变量；

Listed below is the environment variable I am using;

Variable                    Value
ConnectionStrings:TestDb    "Server=TestServer;Database=TestDb;Persist Security Info=True;User ID=TestUser;Password=testpassword;MultipleActiveResultSets=true"

但是，当我使用此设置时，我在尝试访问数据库时收到错误消息，指出它无法解析连接字符串.

However, when I use this setup, I get an error when I try to access the database indicating that it can't parse the connection string.

我不得不假设问题是我在环境变量中指定连接字符串的方式，但是在网上搜索了一段时间后，我无法找到环境变量值应该是什么样子的示例喜欢.例如，我是否需要在整个字符串前后加上单引号?连接字符串的各个部分需要单引号还是双引号?

I have to assume that the problem is the way I have the connection string specified in the environment variable, but after quite a while searching online, I have not been able to find an example of exactly what the environment variable value should look like. For example, do I need to put leading and trailing single quotes around the entire string? Does individual sections of the connection string need single or double quotes?

任何帮助，例如在环境变量中定义的正确连接字符串的示例，将不胜感激.

Any help, such as an example of a proper connection string defined in an environment variable, would be greatly appreciated.

推荐答案

在您的连接变量中设置了拼写错误/错误的值.

There is a typo/wrong value set in your connection variables.

在您粘贴的这个输出中可以看到:

Which can be seen in this output you pasted:

Variable                    Value
ConnectionStrings:TestDb    "Server=TestServer;Database=TestDb;Persist Security Info=True;User ID=TestUser;Password=testpassword;MultipleActiveResultSets=true"

这可能发生在通过设置变量时

This likely happend while setting the variable via

$env:ConnectionStrings:MyDb = """Server=..."""

正确的方法是设置不带引号.

the correct way is to set it without the quotation marks.

$env:ConnectionStrings:MyDb = "Server=..."

旧答案(对于可能搜索类似问题的其他用户)

连接字符串的约定是 Azure Web 使用的 SQLCONNSTR_、MYSQLCONNSTR_、SQLAZURECONNSTR_ 和 CUSTOMCONNSTR_应用程序，但也应适用于自托管、VM 或任何其他云提供商.

Old answer (for other users who may search for similar issues)

The convention for connection strings is SQLCONNSTR_, MYSQLCONNSTR_, SQLAZURECONNSTR_ and CUSTOMCONNSTR_ which are used by Azure Web Apps, but should also work for self-hosting, VMs or any other cloud provider.

因此，如果您有一个名为 CUSTOMCONNSTR_TestDb 的环境变量，它将与在

So if you have an environment variable called CUSTOMCONNSTR_TestDb it will be the same as defining it in appsettings.json in

{
    "connectionStrings": {
        "TestDb": "..."
    }
}

如果在 .UseJsonFile(...) 之后调用 AddEnvironmentVariables()，它也会覆盖其中的值.最后注册获胜.

It will also override the value inside it, if AddEnvironmentVariables() is called after .UseJsonFile(...). Last registration wins.

var builder = new ConfigurationBuilder()
    .SetBasePath(env.ContentRootPath)
    .AddJsonFile("appsettings.json", optional: true, reloadOnChange: true)
    .AddJsonFile($"appsettings.{env.EnvironmentName}.json", optional: true)
    // This one needs to be last
    .AddEnvironmentVariables();

您还可以使用其他变量来覆盖配置值.iirc.ASPNETCORE_ 是默认前缀(但您可以在 AddEnvironmentVariables("MY_") 中更改它).

You can also use other variables to override configuration values. iirc. ASPNETCORE_ is the default prefix (but you can change it in the AddEnvironmentVariables("MY_")).

所以 ASPNETCORE_MySettings 会覆盖 Configuration["MySettings"](或 Configuration.Get("MySettings"))和 ASPNETCORE_My__Settings (在 Linux 上使用 双下划线 表示级别层次结构，阅读其中 : 用于获取配置 - Linux 不允许在变量名中使用冒号)覆盖 Configuration["My:Settings"] 和

So a ASPNETCORE_MySettings overrides Configuration["MySettings"] (or Configuration.Get("MySettings")) and ASPNETCORE_My__Settings (use double underscore for level hierarchy on Linux, read where : is used to obtain the config - Linux disallows colon in variable names) overrides Configuration["My:Settings"] so same as

{
    "my": {
        "settings": "..."
    }
}

除非他们最近改变了这一点.

Unless they changed that recently.

FWIW:据我记忆，环境变量/配置键名不区分大小写.

FWIW: Environment variables/configuration key names are case-insensitive as far as I remember.

这篇关于如何在环境变量中正确存储连接字符串以供生产 ASP.Net Core MVC 应用程序检索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！