.NET Core API连接SQL Server遇内部致命连接错误求助

问题描述 #

将.NET Core API连接到SQL Server时出现错误，测试连接Swagger返回500状态码，提示Connection failed: Internal connection fatal error，但可通过SQLCMD正常连接数据库，寻求解决方法。

相关配置与代码 #

appsettings.json #

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "DefaultConnection": "Server=Servername;Database=iRegister;User Id=Usernae;Password=Mypassword!;Pooling=False"
  }
}

Swagger测试错误信息 #

Error: response status is 500
Response body
Download

Connection failed: Internal connection fatal error.

注：可通过SQLCMD正常连接数据库

ConnectionTestController.cs代码 #

using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using System;
using System.Data.SqlClient;

namespace YourNamespace.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class ConnectionTestController : ControllerBase
    {
        private readonly IConfiguration _configuration;

        public ConnectionTestController(IConfiguration configuration)
        {
            _configuration = configuration;
        }

        [HttpGet]
        public IActionResult TestConnection()
        {
            string connectionString = _configuration.GetConnectionString("DefaultConnection");

            using (var connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    return Ok("Connection successful.");
                }
                catch (Exception ex)
                {
                    return StatusCode(500, $"Connection failed: {ex.Message}");
                }
            }
        }
    }
}

Visual Studio输出信息 #

Microsoft.Hosting.Lifetime: Information: Application started. Press Ctrl+C to shut down.
Microsoft.Hosting.Lifetime: Information: Hosting environment: Development
Microsoft.Hosting.Lifetime: Information: Content root path: D:\user\Repos\API\WebApplication1\WebApplication1
'WebApplication1.exe' (CoreCLR: clrhost): Loaded 'C:\Program Files\dotnet\shared\Microsoft.NETCore.App\8.0.1\System.Threading.Channels.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WebApplication1.exe' (CoreCLR: clrhost): Loaded 'C:\Program Files\dotnet\shared\Microsoft.NETCore.App\8.0.1\System.Net.WebSockets.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WebApplication1.exe' (CoreCLR: clrhost): Loaded 'C:\Program Files\dotnet\shared\Microsoft.NETCore.App\8.0.1\System.Threading.Tasks.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WebApplication1.exe' (CoreCLR: clrhost): Loaded 'C:\Program Files\dotnet\shared\Microsoft.NETCore.App\8.0.1\System.Net.NameResolution.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WebApplication1.exe' (CoreCLR: clrhost): Loaded 'C:\Program Files\dotnet\shared\Microsoft.NETCore.App\8.0.1\System.Text.RegularExpressions.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WebApplication1.exe' (CoreCLR: clrhost): Loaded 'D:\user\Repos\API\WebApplication1\WebApplication1\bin\Debug\net8.0\runtimes\win\lib\netcoreapp2.1\System.Data.SqlClient.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WebApplication1.exe' (CoreCLR: clrhost): Loaded 'C:\Program Files\dotnet\shared\Microsoft.NETCore.App\8.0.1\System.Data.Common.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WebApplication1.exe' (CoreCLR: clrhost): Loaded 'C:\Program Files\dotnet\shared\Microsoft.NETCore.App\8.0.1\System.Transactions.Local.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WebApplication1.exe' (CoreCLR: clrhost): Loaded 'C:\Program Files\dotnet\shared\Microsoft.NETCore.App\8.0.1\System.Threading.Timer.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WebApplication1.exe' (CoreCLR: clrhost): Loaded 'C:\Program Files\dotnet\shared\Microsoft.NETCore.App\8.0.1\System.Text.Encoding.CodePages.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WebApplication1.exe' (CoreCLR: clrhost): Loaded 'C:\Program Files\dotnet\shared\Microsoft.NETCore.App\8.0.1\System.Resources.ResourceManager.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WebApplication1.exe' (CoreCLR: clrhost): Loaded 'C:\Program Files\dotnet\shared\Microsoft.NETCore.App\8.0.1\System.Buffers.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'WebApplication1.exe' (CoreCLR: clrhost): Loaded 'C:\Program Files\dotnet\shared\Microsoft.NETCore.App\8.0.1\System.Diagnostics.Process.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Exception thrown: 'System.InvalidOperationException' in System.Data.SqlClient.dll

解决建议 #

• 修正连接字符串拼写错误：配置中User Id的值写成了Usernae，缺少一个字母'm'，这会导致身份验证失败，修正为正确的用户名。
• 替换数据库驱动：.NET Core及后续版本推荐使用Microsoft.Data.SqlClient而非旧的System.Data.SqlClient，后者在.NET 8中可能存在兼容性问题。卸载System.Data.SqlClient NuGet包，安装Microsoft.Data.SqlClient，并将代码中的using System.Data.SqlClient;替换为using Microsoft.Data.SqlClient;。
• 添加连接字符串参数：如果数据库未启用加密，可在连接字符串中添加Encrypt=False;TrustServerCertificate=True，避免SSL/TLS相关的连接错误。修改后的连接字符串示例：

  "DefaultConnection": "Server=Servername;Database=iRegister;User Id=Username;Password=Mypassword!;Pooling=False;Encrypt=False;TrustServerCertificate=True"
  

• 输出完整异常信息：修改控制器中的catch块，打印异常的InnerException，获取更详细的错误原因，帮助定位问题：

  catch (Exception ex)
  {
      var errorMsg = $"Connection failed: {ex.Message}";
      if (ex.InnerException != null)
      {
          errorMsg += $"\nInner Error: {ex.InnerException.Message}";
      }
      return StatusCode(500, errorMsg);
  }
  

• 检查驱动版本兼容性：确保安装的Microsoft.Data.SqlClient版本支持.NET 8，推荐使用最新稳定版。

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