Blazor数据库对比应用连接SQL Server时遇‘authentication’不支持错误

问题场景 #

开发用于对比SQL Server数据库的Blazor应用时，在appsettings.json中配置了带Active Directory Password认证的连接字符串，通过SqlConnection创建数据库连接时抛出错误：

ArgumentException: Keyword not supported: 'authentication'

相关配置与代码 #

appsettings.json 配置：

"AllowedHosts": "*",
"ConnectionStrings": {
    "SourceConnectionString": "Server=tcp:myserver.database.windows.net,1433;Initial Catalog=prod;Persist Security Info=False;User ID=admin@mycompany.com;Password=123;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication='Active Directory Password';",
    "TargetConnectionString": "Server=tcp:myserver.database.windows.net,1433;Initial Catalog=dev;Persist Security Info=False;User ID=admin@mycompany.com;Password=123;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication='Active Directory Password';"
}

SourceDbContext 代码：

namespace MsSQLDatabaseComparison.DbContexts;

public class SourceDbContext
{
    private readonly IConfiguration _configuration;
    private readonly string _connectionString;

    public SourceDbContext(IConfiguration configuration)
    {
        _configuration = configuration;
        _connectionString = _configuration.GetConnectionString("SourceConnectionString");
    }

    public IDbConnection CreateConnection() => new SqlConnection(_connectionString);
}

TargetDbContext 代码：

namespace MsSQLDatabaseComparison.DbContexts;

public class TargetDbContext
{
    private readonly IConfiguration _configuration;
    private readonly string _connectionString;

    public TargetDbContext(IConfiguration configuration)
    {
        _configuration = configuration;
        _connectionString = _configuration.GetConnectionString("TargetConnectionString");
    }

    public IDbConnection CreateConnection() => new SqlConnection(_connectionString);
}

解决方案 #

1. 更换SQL Server驱动包 #

旧版的System.Data.SqlClient不支持Authentication关键字，需使用Microsoft.Data.SqlClient包：

• 在NuGet包管理器中安装Microsoft.Data.SqlClient（推荐最新稳定版）。

2. 替换代码中的SqlConnection引用 #

将代码中的SqlConnection替换为Microsoft.Data.SqlClient.SqlConnection，修改后的SourceDbContext示例：

using Microsoft.Data.SqlClient;
using System.Data;
namespace MsSQLDatabaseComparison.DbContexts;

public class SourceDbContext
{
    private readonly IConfiguration _configuration;
    private readonly string _connectionString;

    public SourceDbContext(IConfiguration configuration)
    {
        _configuration = configuration;
        _connectionString = _configuration.GetConnectionString("SourceConnectionString");
    }

    public IDbConnection CreateConnection() => new SqlConnection(_connectionString);
}

TargetDbContext做同样修改即可。

3. 修正连接字符串格式 #

移除Authentication值的单引号，避免解析异常，修改后的连接字符串示例：

"SourceConnectionString": "Server=tcp:myserver.database.windows.net,1433;Initial Catalog=prod;Persist Security Info=False;User ID=admin@mycompany.com;Password=123;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication=Active Directory Password;"

完成以上三步后，重新运行应用即可正常使用Active Directory Password认证连接SQL Server数据库。

内容的提问来源于stack exchange，提问作者Leo