Azure API连接Azure SQL Database返回500错误，求排查建议

云原生阿岚

2026-7-17

排查连接Azure SQL Database的API端点500错误

涉及资源

Angular Web应用（本地运行及部署为Azure App Service）
部署在Azure上的.NET Web API（已接入API Management Service）
Azure SQL Database

问题描述

API中负责与Azure SQL Database交互获取数据的端点，Web应用调用时返回HTTP Error 500 Internal Server Error，其余API端点测试均正常。已在API Management Service门户为该端点启用trace for an hour，追踪结果如下：

api-inspector (5.762 ms)
{
    "request": {
        "method": "GET",
        "url": "confidential",
        "headers": [
            {
                "name": "sec-ch-ua",
                "value": "\"Not.A/Brand\";v=\"8\",\"Chromium\";v=\"114\",\"Google Chrome\";v=\"114\""
            },
            {
                "name": "sec-ch-ua-mobile",
                "value": "?0"
            },
            {
                "name": "ocp-apim-subscription-key",
                "value": "c068472fa5fc4855a214589fca573f3f"
            },
            {
                "name": "X-Forwarded-For",
                "value": "213.232.64.36"
            },
            {
                "name": "sec-ch-ua-platform",
                "value": "\"Windows\""
            },
            {
                "name": "Sec-Fetch-Site",
                "value": "cross-site"
            },
            {
                "name": "Sec-Fetch-Mode",
                "value": "cors"
            },
            {
                "name": "Sec-Fetch-Dest",
                "value": "empty"
            },
            {
                "name": "Cache-Control",
                "value": "no-cache, no-store"
            },
            {
                "name": "Accept",
                "value": "*/*"
            },
            {
                "name": "Accept-Encoding",
                "value": "gzip,deflate,br"
            },
            {
                "name": "Accept-Language",
                "value": "de-DE,de;q=0.9,en-US;q=0.8,en;q=0.7"
            },
            {
                "name": "Host",
                "value": "apim-gws-vm-hub.azure-api.net"
            },
            {
                "name": "Referer",
                "value": "https://apimanagement.hosting.portal.azure.net/"
            }
        ]
    }
}
api-inspector (0.418 ms)
{
    "configuration": {
        "api": {
            "from": "/",
            "to": {
                "scheme": "https",
                "host": "confidential",
                "port": 443,
                "path": "/",
                "queryString": "",
                "query": {},
                "isDefaultPort": true
            },
            "version": null,
            "revision": "1"
        },
        "operation": {
            "method": "GET",
            "uriTemplate": "/api/CustomerSetting"
        },
        "user": "-",
        "product": "-"
    }
}
cors (0.040 ms)
"Origin header was missing or empty and the request was classified as not cross-domain. CORS policy was not applied."
cors (0.002 ms)
"Origin header was missing or empty and the request was classified as not cross-domain. CORS policy was not applied."
Backend
(171.162 ms)↑ Back to top
forward-request (0.697 ms)
{
    "message": "Request is being forwarded to the backend service. Timeout set to 300 seconds",
    "request": {
        "method": "GET",
        "url": "confidential",
        "headers": [
            {
                "name": "Host",
                "value": "confidential"
            },
            {
                "name": "sec-ch-ua",
                "value": "\"Not.A/Brand\";v=\"8\",\"Chromium\";v=\"114\",\"Google Chrome\";v=\"114\""
            },
            {
                "name": "Request-Id",
                "value": "|84688588-445cacf71e07bd43.84688589_"
            },
            {
                "name": "sec-ch-ua-mobile",
                "value": "?0"
            },
            {
                "name": "ocp-apim-subscription-key",
                "value": "c068472fa5fc4855a214589fca573f3f"
            },
            {
                "name": "X-Forwarded-For",
                "value": "213.232.64.36,20.121.82.216"
            },
            {
                "name": "sec-ch-ua-platform",
                "value": "\"Windows\""
            },
            {
                "name": "Sec-Fetch-Site",
                "value": "cross-site"
            },
            {
                "name": "Sec-Fetch-Mode",
                "value": "cors"
            },
            {
                "name": "Sec-Fetch-Dest",
                "value": "empty"
            },
            {
                "name": "Cache-Control",
                "value": "no-cache, no-store"
            },
            {
                "name": "Accept",
                "value": "*/*"
            },
            {
                "name": "Accept-Encoding",
                "value": "gzip,deflate,br"
            },
            {
                "name": "Accept-Language",
                "value": "de-DE,de;q=0.9,en-US;q=0.8,en;q=0.7"
            },
            {
                "name": "Referer",
                "value": "https://apimanagement.hosting.portal.azure.net/"
            },
            {
                "name": "Request-Context",
                "value": "appId=cid-v1:122a11f8-2e9e-493c-9359-bc3e4fb49c3c"
            }
        ]
    }
}
forward-request (170.466 ms)
{
    "response": {
        "status": {
            "code": 500,
            "reason": "Internal Server Error"
        },
        "headers": [
            {
                "name": "Content-Length",
                "value": "0"
            },
            {
                "name": "Date",
                "value": "Tue, 04 Jul 2023 08:54:08 GMT"
            },
            {
                "name": "Set-Cookie",
                "value": "ARRAffinity=6d3572b2b5d6d73206c9c4a99a5ad142df9de7ce275826b9ad09831ef137d7de;Path=/;HttpOnly;Secure;Domain=confidential,ARRAffinitySameSite=6d3572b2b5d6d73206c9c4a99a5ad142df9de7ce275826b9ad09831ef137d7de;Path=/;HttpOnly;SameSite=None;Secure;Domain=confidential"
            },
            {
                "name": "Server",
                "value": "Microsoft-IIS/10.0"
            },
            {
                "name": "X-Powered-By",
                "value": "ASP.NET"
            }
        ]
    }
}

代码配置

appsettings.json 连接字符串

"SQLConnection": "Server=tcp:sql-myServer.database.windows.net,1433;Database=sqldb-myDB"

Program.cs 代码片段

var connectionString = MyConfig.GetValue<string>("Azure:SQLConnection");
builder.Services.AddDbContext<CustomerSettingContext>(a => a.UseSqlServer(connectionString));

已执行的排查操作

在SQL Server的IAM中添加API对应的App Service身份
在数据库安全模块中添加该App Service为数据库用户

排查思路

补全连接字符串的身份验证参数：当前连接字符串缺少托管身份认证配置，需添加;Authentication=Active Directory Managed Identity;，确保应用服务通过托管身份认证到SQL Database。
验证权限有效性：
- 确认SQL Server IAM中App Service身份的权限为SQL DB Contributor或具备数据读取权限的角色，等待权限同步完成（通常需1-5分钟）。
- 检查数据库用户对应的角色，确认拥有CustomerSetting相关表的SELECT权限。
查看API应用服务的详细日志：在Azure门户的App Service中开启日志流或查看应用日志，获取500错误的具体异常信息（如连接超时、权限不足、EF映射错误等），APIM追踪仅返回状态码，后端日志是定位问题的核心。
绕过APIM直接测试API：直接访问API的App Service端点（而非通过APIM），若同样返回500，说明问题出在API或SQL连接环节，排除APIM配置问题。
检查EF上下文映射：确认CustomerSettingContext中的实体与数据库表结构完全匹配，包括字段名、数据类型、主键定义等，避免因映射错误导致查询失败。
验证SQL防火墙规则：确保SQL Database的防火墙允许App Service的出站IP访问，或启用允许Azure服务和资源访问此服务器选项。

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

Azure API连接Azure SQL Database返回500错误，求排查建议

涉及资源 #

问题描述 #

代码配置 #

appsettings.json 连接字符串 #

Program.cs 代码片段 #

已执行的排查操作 #

排查思路 #