connectivity-troubleshoot

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Connectivity

连接性

Use this skill when the user asks to test connectivity, verify a connection, check if they can connect, or validate database access.
当用户要求测试连接、验证连接、检查是否能够连接或验证数据库访问权限时,使用此 skill。

Scope

范围

  • Covers DNS, TCP and timeout failures (53, 10060, 10061, SQLSTATE 08001), TLS and pre-login handshakes, Azure firewall rejections (40615, client IP address not allowed), login and database-open errors at connect time (18456, 4060), and transient service errors.
  • Separate transport from identity and database access, and do not assume a generic login failure means Microsoft Entra authentication. For deeper login, token, principal or permission diagnosis, use
    authentication-diagnostics
    .
  • 涵盖 DNS、TCP 和超时故障(53、10060、10061、SQLSTATE 08001)、TLS 和预登录握手、Azure 防火墙拒绝(40615,客户端 IP 地址不允许)、连接时发生的登录和数据库打开错误(18456、4060),以及瞬时服务错误。
  • 将传输问题与身份和数据库访问权限分开处理,不要将泛化的登录失败视为 Microsoft Entra authentication。对于更深层次的登录、令牌、主体或权限诊断,请使用
    authentication-diagnostics
    。

Azure SQL Database specialization

Azure SQL Database 专项说明

For Azure SQL Database (EngineEdition 5), read Azure-specific guidance before choosing diagnostic queries. It preserves the service-specific permissions, capture limitations and error interpretation. Keep this skill's scope and authorization rules; the reference is not a second workflow to run unconditionally.
对于 Azure SQL Database(EngineEdition 5),在选择诊断查询之前,请阅读 Azure 专项指南。该指南保留了特定于服务的权限、捕获限制和错误解释规则。保持此 skill 的范围和授权规则;该参考文档不是第二个需要无条件执行的工作流。

Azure errors and authentication handoff

Azure 错误与身份验证移交

Read error number, severity and driver output together. An informational severity-10 message can accompany a successful process exit, so
sqlcmd
exit status alone does not establish success. Check the actual client version and behavior. Separate DNS/TCP/TLS failures from authentication or database-access failures; hand login/token/principal/grant diagnosis to
authentication-diagnostics
. Retry transient service/resume failures with bounds, not permanent credential failures. Do not open a firewall or broaden permissions as an automatic diagnostic step.
结合错误号、严重级别和驱动程序输出一起阅读。严重级别为 10 的信息性消息可能伴随进程成功退出,因此仅凭
sqlcmd
退出状态无法确定成功。检查实际的客户端版本和行为。将 DNS/TCP/TLS 故障与身份验证或数据库访问故障分开;将登录、令牌、主体或授权诊断移交给
authentication-diagnostics
。重试瞬时服务或恢复故障时应设置界限,而不是重试永久性凭据故障。不要将打开防火墙或扩大权限作为自动诊断步骤。

Required tools

必需工具

  • A read-only SQL query tool connected to the target server and database. Prefer, in order: the SQL Tools MCP server that ships with the
    microsoft-sql-admin
    plugin (
    mssql_list_database_connections
    ,
    mssql_connect_database
    , then
    mssql_execute_query
    ); any other SQL tool the agent host provides for running T-SQL and reading result sets;
    sqlcmd
    from a terminal. If the SQL Tools MCP server is not connected or lists no connections, follow the
    sql-tools-mcp-setup
    skill, which asks before installing or configuring anything. If no SQL tool can be made available, give the user the T-SQL to run and ask for the results.
  • Least privilege: connect that tool with a diagnostic login that cannot write, for example one that holds only
    VIEW SERVER PERFORMANCE STATE
    (
    VIEW SERVER STATE
    before SQL Server 2022) or, on Azure SQL Database,
    VIEW DATABASE PERFORMANCE STATE
    or
    ##MS_ServerStateReader##
    , plus any other read permission these queries need. A tool is only as read-only as the login it connects with.
  • 一个连接到目标服务器和数据库的只读 SQL 查询工具。按以下顺序优先选择:随
    microsoft-sql-admin
    插件提供的 SQL Tools MCP server(
    mssql_list_database_connections
    、
    mssql_connect_database
    ,然后是
    mssql_execute_query
    );Agent 宿主机提供的任何其他用于运行 T-SQL 和读取结果集的 SQL 工具;从终端使用
    sqlcmd
    。如果 SQL Tools MCP server 未连接或未列出任何连接,请遵循
    sql-tools-mcp-setup
    skill,该 skill 会在安装或配置任何内容之前询问。如果无法提供任何 SQL 工具,请向用户提供要运行的 T-SQL,并要求其返回结果。
  • 最小权限:使用无法执行写入操作的诊断登录连接该工具,例如仅拥有
    VIEW SERVER PERFORMANCE STATE
    (SQL Server 2022 之前为
    VIEW SERVER STATE
    )权限,或在 Azure SQL Database 上拥有
    VIEW DATABASE PERFORMANCE STATE
    或
    ##MS_ServerStateReader##
    ,以及这些查询需要的任何其他读取权限。工具的只读程度取决于其所连接的登录权限。

Remediation Safety

修复安全性

  • All diagnostic queries in this skill are read-only.
  • The resolutions it reports (
    ALTER USER ... WITH LOGIN
    ,
    DROP USER
    ,
    ALTER LOGIN ... ENABLE
    , and granting server access) change security principals. They are recommendations only.
  • Never run any of them without the user's explicit confirmation for that specific command. A request to investigate, diagnose, or "fix" the problem is not that confirmation.
  • Before asking, state the principal, the change, and how to undo it.
  • 此 skill 中的所有诊断查询均为只读。
  • 其报告的解决方案(
    ALTER USER ... WITH LOGIN
    、
    DROP USER
    、
    ALTER LOGIN ... ENABLE
    以及授予服务器访问权限)会更改安全主体。这些解决方案仅供参考。
  • 未经用户针对该特定命令的明确确认,绝不执行其中任何一项。请求调查、诊断或“修复”问题并不构成该确认。
  • 询问之前,请说明主体、将进行的更改以及如何撤销。

Investigation Workflow

调查工作流

Execute these steps in order based on user symptoms. Use the
QueryExecutedAtUTC
value returned by the
ConnectivityChecker
query (or the current system date) for the report header date.
根据用户症状按顺序执行以下步骤。对于报告标题日期,使用
ConnectivityChecker
查询返回的
QueryExecutedAtUTC
值(或当前系统日期)。

Pre-Connection Troubleshooting

连接前故障排查

If connection cannot be established at all, guide the user through client-side diagnostics first:
  1. DNS Resolution:
    nslookup <server>.database.windows.net
  2. Port Reachability:
    Test-NetConnection -ComputerName <server> -Port 1433
  3. Azure SQL Connectivity Checker: https://github.com/Azure/SQL-Connectivity-Checker
如果完全无法建立连接,请先指导用户进行客户端诊断:
  1. DNS 解析:
    nslookup <server>.database.windows.net
  2. 端口可达性:
    Test-NetConnection -ComputerName <server> -Port 1433
  3. Azure SQL Connectivity Checker:https://github.com/Azure/SQL-Connectivity-Checker

Step 1: Verify Connection Status

步骤 1:验证连接状态

Connect using the host's SQL tool, or with
sqlcmd
(for example
sqlcmd -S <server> -d <database> -N
plus the appropriate authentication flags, such as
-E
for Windows authentication or
--authentication-method ActiveDirectoryDefault
/
ActiveDirectoryInteractive
for Microsoft Entra). Do not put passwords or other credentials in commands or examples; let the tool or the user supply them.
Then confirm the connection and which server and database you are connected to:
sql
SELECT @@SERVERNAME AS ServerName, DB_NAME() AS DatabaseName, SUSER_SNAME() AS LoginName,
       SERVERPROPERTY('EngineEdition') AS EngineEdition;
If this returns a row, the connection is established; proceed to Step 2. If the connection attempt fails, capture the exact error number and message and go to Step 3 (and the Pre-Connection Troubleshooting steps above).
使用宿主的 SQL 工具进行连接,或使用
sqlcmd
(例如
sqlcmd -S <server> -d <database> -N
,并加上适当的身份验证标志,如用于 Windows authentication 的
-E
,或用于 Microsoft Entra 的
--authentication-method ActiveDirectoryDefault
/
ActiveDirectoryInteractive
)。 不要在命令或示例中放入密码或其他凭据;让工具或用户提供凭据。
然后确认连接以及当前连接的服务器和数据库:
sql
SELECT @@SERVERNAME AS ServerName, DB_NAME() AS DatabaseName, SUSER_SNAME() AS LoginName,
       SERVERPROPERTY('EngineEdition') AS EngineEdition;
如果查询返回一行,则连接已建立;继续执行步骤 2。如果连接尝试失败,请捕获确切的错误号和错误消息,并转到步骤 3(以及上述连接前故障排查步骤)。

Step 2: Run ConnectivityChecker

步骤 2:运行 ConnectivityChecker

Execute the
ConnectivityChecker
query. This returns:
  • Current connection details (server, database, login, auth method)
  • Encryption status and TLS version
  • TCP listener info (SQL Server only)
  • Private/public endpoint detection (Azure SQL only)
执行
ConnectivityChecker
查询。它将返回:
  • 当前连接详情(服务器、数据库、登录、身份验证方法)
  • 加密状态和 TLS 版本
  • TCP 侦听器信息(仅限 SQL Server)
  • 专用/公共终结点检测(仅限 Azure SQL)

Step 3: Error-Specific Diagnosis

步骤 3:错误专项诊断

If the user provides an error code (e.g., 18456, 4060, 40613):
  1. Run
    CommonConnectivityErrors
    query to get targeted resolution guidance
  2. For login errors (18456): Run
    LoginDiagnostics
    to check login status, lock state, and permissions
  3. For firewall errors (40532): Run
    ListFirewallRules
    (Azure SQL DB only, requires
    master
    database)
如果用户提供错误代码(例如 18456、4060、40613):
  1. 运行
    CommonConnectivityErrors
    查询,以获取针对性的解决建议
  2. 对于登录错误(18456):运行
    LoginDiagnostics
    ,检查登录状态、锁定状态和权限
  3. 对于防火墙错误(40532):运行
    ListFirewallRules
    (仅限 Azure SQL DB,且需要
    master
    数据库)

Step 4: Platform-Specific Checks

步骤 4:平台专项检查

Detect platform via Engine Edition, then run appropriate checks:
PlatformEngine EditionAdditional Checks
Azure SQL Database5
ListFirewallRules
,
TestConnectivity
Azure SQL Managed Instance8
TestConnectivity
(VNET/NSG/DNS guidance)
Fabric SQL Database11
TestConnectivity
SQL Server (VM/on-prem)1-4
LoginDiagnostics
,
TestConnectivity
通过引擎版本检测平台,然后运行适当的检查:
平台引擎版本附加检查
Azure SQL Database5
ListFirewallRules
、
TestConnectivity
Azure SQL Managed Instance8
TestConnectivity
(VNET/NSG/DNS 指南)
Fabric SQL Database11
TestConnectivity
SQL Server(VM/本地部署)1-4
LoginDiagnostics
、
TestConnectivity

Step 5: Generate Report

步骤 5:生成报告

Present findings using the Report Template below.
使用下方的报告模板呈现结果。

Parameters

参数

ParameterDefaultDescription
ErrorCode(none)Specific SQL error code (e.g., 18456, 4060, 40613) to filter diagnostics and show targeted resolution guidance
ErrorState(none)For error 18456, the specific state number (5, 7, 8, 11, 18, 38, 40) to get targeted resolution
Category(none)Filter errors by category:
Network
,
TLS/Certificate
,
Authentication
,
Azure Transient
,
Resource
DiagnosticLevel
full
Level of diagnostics:
quick
(basic connectivity check only),
full
(all sections), or
custom
(user selects which checks to run)
LoginName(current)Login name to diagnose (defaults to current login). Use to check another user's status
ShowAllLogins
false
Show all logins/users instead of just current login. Useful for security audits
IPAddress(auto-detected)Client IP to validate against firewall rules (defaults to current connection IP)
IncludeFirewallCheck
true
Whether to run firewall rule analysis (Azure SQL Database only)
IncludeLoginDiagnostics
true
Whether to run login status and permission checks
IncludeEncryptionCheck
true
Whether to verify TLS/encryption status
IncludeVNetRules
true
Whether to query Virtual Network service endpoint rules
Port
1433
Target TCP port for connectivity validation and recommendations
MaxResults
20
Maximum rows to return per diagnostic section
TopRecommendations
10
Maximum number of platform-specific recommendations to return
参数默认值说明
ErrorCode(无)要过滤诊断并显示针对性解决建议的特定 SQL 错误码(例如 18456、4060、40613)
ErrorState(无)对于错误 18456,要获取针对性解决的特定状态号(5、7、8、11、18、38、40)
Category(无)按类别筛选错误:
Network
、
TLS/Certificate
、
Authentication
、
Azure Transient
、
Resource
DiagnosticLevel
full
诊断级别:
quick
(仅执行基本连接检查)、
full
(执行所有部分)或
custom
(由用户选择要运行的检查)
LoginName(当前)要诊断的登录名称(默认为当前登录)。用于检查其他用户的状态
ShowAllLogins
false
显示所有登录和用户,而不只是当前登录。适用于安全审计
IPAddress(自动检测)要根据防火墙规则验证的客户端 IP(默认为当前连接 IP)
IncludeFirewallCheck
true
是否运行防火墙规则分析(仅限 Azure SQL Database)
IncludeLoginDiagnostics
true
是否运行登录状态和权限检查
IncludeEncryptionCheck
true
是否验证 TLS/加密状态
IncludeVNetRules
true
是否查询 Virtual Network 服务终结点规则
Port
1433
用于连接验证和建议的目标 TCP 端口
MaxResults
20
每个诊断部分最多返回的行数
TopRecommendations
10
最多返回的平台专项建议数量

Report Template

报告模板

Present results in this exact format:
undefined
使用以下确切格式呈现结果:
undefined

Connectivity Diagnostics Report

Connectivity Diagnostics Report

Server: {{ServerName}} Database: {{DatabaseName}} Platform: {{Platform}} Date: {{current date}}

Server: {{ServerName}} Database: {{DatabaseName}} Platform: {{Platform}} Date: {{current date}}

Connection Status: {{Connected/Failed}}

Connection Status: {{Connected/Failed}}

PropertyValue
Auth Method{{AuthScheme}}
Encryption{{EncryptionEnabled}}
TDS Protocol Version{{TDSProtocolVersion}}
Client IP{{ClientIP}}
Server Port{{ServerPort}}

PropertyValue
Auth Method{{AuthScheme}}
Encryption{{EncryptionEnabled}}
TDS Protocol Version{{TDSProtocolVersion}}
Client IP{{ClientIP}}
Server Port{{ServerPort}}

Issues Detected

Issues Detected

{{List issues with severity: Critical/Warning/Info}}
SeverityIssueDetails
{{one row per issue detected}}

{{List issues with severity: Critical/Warning/Info}}
SeverityIssueDetails
{{one row per issue detected}}

Recommended Actions

Recommended Actions

{{Numbered list of platform-specific recommendations, ordered by priority}}
  1. {{Most critical action}}
  2. {{Next action}} ...

{{Numbered list of platform-specific recommendations, ordered by priority}}
  1. {{Most critical action}}
  2. {{Next action}} ...

Additional Resources

Additional Resources

SQL Queries

SQL 查询

ConnectivityChecker - Comprehensive connection diagnostics

ConnectivityChecker - 综合连接诊断

sql
SET NOCOUNT ON;

DECLARE @EngineEdition int = CONVERT(int, SERVERPROPERTY('EngineEdition'));
DECLARE @Platform nvarchar(60) =
    CASE @EngineEdition
        WHEN 5 THEN N'Azure SQL Database'
        WHEN 8 THEN N'Azure SQL Managed Instance'
        WHEN 11 THEN N'Fabric SQL Database'
        ELSE        N'SQL Server (VM / on-prem)'
    END;

-- Section 1: Current Connection Details
SELECT 
    N'1. Connection Details' AS Section,
    SYSUTCDATETIME() AS QueryExecutedAtUTC,
    @@SERVERNAME AS ServerName,
    DB_NAME() AS DatabaseName,
    @Platform AS Platform,
    SUSER_SNAME() AS LoginName,
    USER_NAME() AS DatabaseUser,
    ORIGINAL_LOGIN() AS OriginalLogin,
    CONNECTIONPROPERTY('net_transport') AS NetTransport,
    CONNECTIONPROPERTY('auth_scheme') AS AuthScheme,
    CONNECTIONPROPERTY('client_net_address') AS ClientIP,
    CONNECTIONPROPERTY('local_net_address') AS ServerIP,
    CONNECTIONPROPERTY('local_tcp_port') AS ServerPort;

-- Section 2: Encryption & TLS
SELECT 
    N'2. Encryption Status' AS Section,
    c.encrypt_option AS EncryptionEnabled,
    c.protocol_type,
    c.protocol_version AS TDSProtocolVersion,
    s.host_name AS ClientHostName,
    s.program_name AS ClientApplication,
    s.client_interface_name AS ClientDriver,
    s.client_version AS ClientProtocolVersion,
    c.num_reads AS PacketsReceived,
    c.num_writes AS PacketsSent,
    DATEDIFF(SECOND, s.login_time, GETDATE()) AS ConnectionDurationSeconds
FROM sys.dm_exec_connections c
JOIN sys.dm_exec_sessions s ON c.session_id = s.session_id
WHERE c.session_id = @@SPID
OPTION (MAXDOP 1);

-- Section 3: TCP Listeners (SQL Server only - not available on Azure SQL DB, MI, or Fabric)
IF @EngineEdition NOT IN (5, 8, 11)
BEGIN
    SELECT 
        N'3. TCP Listeners' AS Section,
        ip_address,
        port,
        state_desc,
        start_time
    FROM sys.dm_tcp_listener_states
    WHERE type_desc = N'TSQL'
      AND state_desc = N'ONLINE'
      OPTION (MAXDOP 1);
END

-- Section 4: Azure Connection Policy & Private Endpoint (Azure SQL only)
IF @EngineEdition = 5
BEGIN
    -- Check if connected via private endpoint (client IP in private range)
    DECLARE @ClientIPCheck varchar(45) = CONVERT(varchar(45), CONNECTIONPROPERTY('client_net_address'));
    DECLARE @IsPrivateEndpoint bit = 
        CASE 
            WHEN @ClientIPCheck LIKE '10.%' THEN 1
            WHEN @ClientIPCheck LIKE '172.1[6-9].%' OR @ClientIPCheck LIKE '172.2[0-9].%' OR @ClientIPCheck LIKE '172.3[0-1].%' THEN 1
            WHEN @ClientIPCheck LIKE '192.168.%' THEN 1
            ELSE 0
        END;
    
    SELECT 
        N'4. Azure Connection Info' AS Section,
        @ClientIPCheck AS ClientIP,
        CASE @IsPrivateEndpoint 
            WHEN 1 THEN 'Private Endpoint (Private IP Range)' 
            ELSE 'Public Endpoint' 
        END AS EndpointType,
        N'Note: Connection policy (Redirect vs Proxy) affects latency. Redirect is faster but requires ports 11000-11999. Check Azure Portal > SQL Server > Networking > Connection policy.' AS PolicyNote;
END
sql
SET NOCOUNT ON;

DECLARE @EngineEdition int = CONVERT(int, SERVERPROPERTY('EngineEdition'));
DECLARE @Platform nvarchar(60) =
    CASE @EngineEdition
        WHEN 5 THEN N'Azure SQL Database'
        WHEN 8 THEN N'Azure SQL Managed Instance'
        WHEN 11 THEN N'Fabric SQL Database'
        ELSE        N'SQL Server (VM / on-prem)'
    END;

-- Section 1: Current Connection Details
SELECT 
    N'1. Connection Details' AS Section,
    SYSUTCDATETIME() AS QueryExecutedAtUTC,
    @@SERVERNAME AS ServerName,
    DB_NAME() AS DatabaseName,
    @Platform AS Platform,
    SUSER_SNAME() AS LoginName,
    USER_NAME() AS DatabaseUser,
    ORIGINAL_LOGIN() AS OriginalLogin,
    CONNECTIONPROPERTY('net_transport') AS NetTransport,
    CONNECTIONPROPERTY('auth_scheme') AS AuthScheme,
    CONNECTIONPROPERTY('client_net_address') AS ClientIP,
    CONNECTIONPROPERTY('local_net_address') AS ServerIP,
    CONNECTIONPROPERTY('local_tcp_port') AS ServerPort;

-- Section 2: Encryption & TLS
SELECT 
    N'2. Encryption Status' AS Section,
    c.encrypt_option AS EncryptionEnabled,
    c.protocol_type,
    c.protocol_version AS TDSProtocolVersion,
    s.host_name AS ClientHostName,
    s.program_name AS ClientApplication,
    s.client_interface_name AS ClientDriver,
    s.client_version AS ClientProtocolVersion,
    c.num_reads AS PacketsReceived,
    c.num_writes AS PacketsSent,
    DATEDIFF(SECOND, s.login_time, GETDATE()) AS ConnectionDurationSeconds
FROM sys.dm_exec_connections c
JOIN sys.dm_exec_sessions s ON c.session_id = s.session_id
WHERE c.session_id = @@SPID
OPTION (MAXDOP 1);

-- Section 3: TCP Listeners (SQL Server only - not available on Azure SQL DB, MI, or Fabric)
IF @EngineEdition NOT IN (5, 8, 11)
BEGIN
    SELECT 
        N'3. TCP Listeners' AS Section,
        ip_address,
        port,
        state_desc,
        start_time
    FROM sys.dm_tcp_listener_states
    WHERE type_desc = N'TSQL'
      AND state_desc = N'ONLINE'
      OPTION (MAXDOP 1);
END

-- Section 4: Azure Connection Policy & Private Endpoint (Azure SQL only)
IF @EngineEdition = 5
BEGIN
    -- Check if connected via private endpoint (client IP in private range)
    DECLARE @ClientIPCheck varchar(45) = CONVERT(varchar(45), CONNECTIONPROPERTY('client_net_address'));
    DECLARE @IsPrivateEndpoint bit = 
        CASE 
            WHEN @ClientIPCheck LIKE '10.%' THEN 1
            WHEN @ClientIPCheck LIKE '172.1[6-9].%' OR @ClientIPCheck LIKE '172.2[0-9].%' OR @ClientIPCheck LIKE '172.3[0-1].%' THEN 1
            WHEN @ClientIPCheck LIKE '192.168.%' THEN 1
            ELSE 0
        END;
    
    SELECT 
        N'4. Azure Connection Info' AS Section,
        @ClientIPCheck AS ClientIP,
        CASE @IsPrivateEndpoint 
            WHEN 1 THEN 'Private Endpoint (Private IP Range)' 
            ELSE 'Public Endpoint' 
        END AS EndpointType,
        N'Note: Connection policy (Redirect vs Proxy) affects latency. Redirect is faster but requires ports 11000-11999. Check Azure Portal > SQL Server > Networking > Connection policy.' AS PolicyNote;
END

TestConnectivity - platform-aware connectivity troubleshooting recommendations

TestConnectivity - 平台感知的连接故障排查建议

sql
SET NOCOUNT ON;
DECLARE @EngineEdition int = CONVERT(int, SERVERPROPERTY('EngineEdition'));
DECLARE @Platform nvarchar(60) =
    CASE @EngineEdition
        WHEN 5 THEN N'Azure SQL Database'
        WHEN 8 THEN N'Azure SQL Managed Instance'
        WHEN 11 THEN N'Fabric SQL Database'
        ELSE        N'SQL Server (VM / on-prem)'
    END;
DECLARE @rec TABLE
(
    SortOrder       int IDENTITY(1,1),
    Platform        nvarchar(60),
    Severity        nvarchar(20),  -- 'Critical', 'Warning', 'Info'
    Recommendation  nvarchar(max)
);
-- Common baseline (applies to all flavors)
INSERT INTO @rec(Platform, Severity, Recommendation)
VALUES
(@Platform, N'Critical', N'Validate server/FQDN/connection string and confirm the SQL port.'),
(@Platform, N'Critical', N'From the client/network host, test TCP port reachability: PowerShell "Test-NetConnection -ComputerName <server> -Port 1433" (or custom port), and/or "sqlcmd" to validate end-to-end SQL connectivity.');
-- Azure SQL Database recommendations
IF @EngineEdition = 5
BEGIN
    INSERT INTO @rec(Platform, Severity, Recommendation)
    VALUES
    (@Platform, N'Warning', N'Connect to [master] and run ListFirewallRules to check if client IP is allowed. Use sp_set_firewall_rule to add rules.'),
    (@Platform, N'Warning', N'Review "Allow Azure services" setting in Azure Portal > Networking (avoid enabling broadly in production).'),
    (@Platform, N'Info', N'Verify DNS resolves to intended endpoint (private vs public). Run Azure SQL Connectivity Checker: https://github.com/Azure/SQL-Connectivity-Checker');
END
-- Azure SQL Managed Instance recommendations
IF @EngineEdition = 8
BEGIN
    INSERT INTO @rec(Platform, Severity, Recommendation)
    VALUES
    (@Platform, N'Critical', N'Run Azure SQL Connectivity Checker: https://github.com/Azure/SQL-Connectivity-Checker'),
    (@Platform, N'Critical', N'Test-NetConnection <server>.database.windows.net -Port <TCPPort> — confirm TcpTestSucceeded=True.'),
    (@Platform, N'Critical', N'Validate VNET path, NSG rules on MI subnet (inbound 1433), and DNS resolution (custom DNS/private DNS zone misconfig is common).'),
    (@Platform, N'Warning', N'Public vs private endpoint? Public: https://learn.microsoft.com/azure/azure-sql/managed-instance/public-endpoint-configure'),
    (@Platform, N'Warning', N'Validate route tables (UDR/forced tunneling/NVA paths can break connectivity).');
END
-- Fabric SQL Database recommendations
IF @EngineEdition = 11
BEGIN
    INSERT INTO @rec(Platform, Severity, Recommendation)
    VALUES
    (@Platform, N'Critical', N'Fabric SQL Database: Verify workspace access and permissions in Microsoft Fabric portal.'),
    (@Platform, N'Warning', N'Fabric SQL Database: Check if the SQL endpoint is enabled for the lakehouse/warehouse.'),
    (@Platform, N'Info', N'Fabric SQL Database: Ensure you are using Microsoft Entra authentication (SQL auth not supported).'),
    (@Platform, N'Warning', N'Fabric SQL Database: Validate network path if using private endpoints or ExpressRoute.');
END
-- SQL Server on Azure VM/on-prem recommendations (excludes Fabric)
IF @EngineEdition NOT IN (5, 8, 11)
BEGIN
    INSERT INTO @rec(Platform, Severity, Recommendation)
    VALUES
    (@Platform, N'Critical', N'Verify SQL Server is started (error log: "SQL Server is now ready for client connections").'),
    (@Platform, N'Critical', N'Validate NSG rules (NIC/subnet) allow inbound TCP to SQL port. Validate Windows Firewall (and UDP 1434 for SQL Browser/named instances).'),
    (@Platform, N'Critical', N'Checklist: https://learn.microsoft.com/troubleshoot/sql/database-engine/connect/resolve-connectivity-errors-checklist'),
    (@Platform, N'Warning', N'If using Public IP/LB, validate inbound NAT/LB rules map to correct backend port and AG listener config.');
END
-- Emit recommendations as a result set (ordered by severity priority)
SELECT
    Platform,
    Severity,
    Recommendation
FROM @rec
ORDER BY 
    CASE Severity 
        WHEN N'Critical' THEN 1 
        WHEN N'Warning' THEN 2 
        WHEN N'Info' THEN 3 
        ELSE 4 
    END,
    SortOrder
    OPTION (MAXDOP 1);
sql
SET NOCOUNT ON;
DECLARE @EngineEdition int = CONVERT(int, SERVERPROPERTY('EngineEdition'));
DECLARE @Platform nvarchar(60) =
    CASE @EngineEdition
        WHEN 5 THEN N'Azure SQL Database'
        WHEN 8 THEN N'Azure SQL Managed Instance'
        WHEN 11 THEN N'Fabric SQL Database'
        ELSE        N'SQL Server (VM / on-prem)'
    END;
DECLARE @rec TABLE
(
    SortOrder       int IDENTITY(1,1),
    Platform        nvarchar(60),
    Severity        nvarchar(20),  -- 'Critical', 'Warning', 'Info'
    Recommendation  nvarchar(max)
);
-- Common baseline (applies to all flavors)
INSERT INTO @rec(Platform, Severity, Recommendation)
VALUES
(@Platform, N'Critical', N'Validate server/FQDN/connection string and confirm the SQL port.'),
(@Platform, N'Critical', N'From the client/network host, test TCP port reachability: PowerShell "Test-NetConnection -ComputerName <server> -Port 1433" (or custom port), and/or "sqlcmd" to validate end-to-end SQL connectivity.');
-- Azure SQL Database recommendations
IF @EngineEdition = 5
BEGIN
    INSERT INTO @rec(Platform, Severity, Recommendation)
    VALUES
    (@Platform, N'Warning', N'Connect to [master] and run ListFirewallRules to check if client IP is allowed. Use sp_set_firewall_rule to add rules.'),
    (@Platform, N'Warning', N'Review "Allow Azure services" setting in Azure Portal > Networking (avoid enabling broadly in production).'),
    (@Platform, N'Info', N'Verify DNS resolves to intended endpoint (private vs public). Run Azure SQL Connectivity Checker: https://github.com/Azure/SQL-Connectivity-Checker');
END
-- Azure SQL Managed Instance recommendations
IF @EngineEdition = 8
BEGIN
    INSERT INTO @rec(Platform, Severity, Recommendation)
    VALUES
    (@Platform, N'Critical', N'Run Azure SQL Connectivity Checker: https://github.com/Azure/SQL-Connectivity-Checker'),
    (@Platform, N'Critical', N'Test-NetConnection <server>.database.windows.net -Port <TCPPort> — confirm TcpTestSucceeded=True.'),
    (@Platform, N'Critical', N'Validate VNET path, NSG rules on MI subnet (inbound 1433), and DNS resolution (custom DNS/private DNS zone misconfig is common).'),
    (@Platform, N'Warning', N'Public vs private endpoint? Public: https://learn.microsoft.com/azure/azure-sql/managed-instance/public-endpoint-configure'),
    (@Platform, N'Warning', N'Validate route tables (UDR/forced tunneling/NVA paths can break connectivity).');
END
-- Fabric SQL Database recommendations
IF @EngineEdition = 11
BEGIN
    INSERT INTO @rec(Platform, Severity, Recommendation)
    VALUES
    (@Platform, N'Critical', N'Fabric SQL Database: Verify workspace access and permissions in Microsoft Fabric portal.'),
    (@Platform, N'Warning', N'Fabric SQL Database: Check if the SQL endpoint is enabled for the lakehouse/warehouse.'),
    (@Platform, N'Info', N'Fabric SQL Database: Ensure you are using Microsoft Entra authentication (SQL auth not supported).'),
    (@Platform, N'Warning', N'Fabric SQL Database: Validate network path if using private endpoints or ExpressRoute.');
END
-- SQL Server on Azure VM/on-prem recommendations (excludes Fabric)
IF @EngineEdition NOT IN (5, 8, 11)
BEGIN
    INSERT INTO @rec(Platform, Severity, Recommendation)
    VALUES
    (@Platform, N'Critical', N'Verify SQL Server is started (error log: "SQL Server is now ready for client connections").'),
    (@Platform, N'Critical', N'Validate NSG rules (NIC/subnet) allow inbound TCP to SQL port. Validate Windows Firewall (and UDP 1434 for SQL Browser/named instances).'),
    (@Platform, N'Critical', N'Checklist: https://learn.microsoft.com/troubleshoot/sql/database-engine/connect/resolve-connectivity-errors-checklist'),
    (@Platform, N'Warning', N'If using Public IP/LB, validate inbound NAT/LB rules map to correct backend port and AG listener config.');
END
-- Emit recommendations as a result set (ordered by severity priority)
SELECT
    Platform,
    Severity,
    Recommendation
FROM @rec
ORDER BY 
    CASE Severity 
        WHEN N'Critical' THEN 1 
        WHEN N'Warning' THEN 2 
        WHEN N'Info' THEN 3 
        ELSE 4 
    END,
    SortOrder
    OPTION (MAXDOP 1);

LoginDiagnostics - Check login status and permissions

LoginDiagnostics - 检查登录状态和权限

sql
SET NOCOUNT ON;

-- Parameters (set these before running)
DECLARE @LoginName nvarchar(256) = NULL;       -- NULL = current login, or specify login to check
DECLARE @ShowAllLogins bit = 0;                 -- 0 = specified login only, 1 = all logins
DECLARE @MaxResults int = 20;                   -- Max rows to return per section

DECLARE @EngineEdition int = CONVERT(int, SERVERPROPERTY('EngineEdition'));
DECLARE @TargetLogin nvarchar(256) = ISNULL(@LoginName, SUSER_SNAME());
DECLARE @CurrentUser nvarchar(256) = USER_NAME();

-- Section 1: Current Identity
SELECT 
    N'1. Current Identity' AS Section,
    SUSER_SNAME() AS CurrentServerLogin,
    @CurrentUser AS CurrentDatabaseUser,
    ORIGINAL_LOGIN() AS OriginalLogin,
    CONNECTIONPROPERTY('auth_scheme') AS AuthMethod,
    IS_SRVROLEMEMBER('sysadmin') AS IsSysAdmin,
    IS_MEMBER('db_owner') AS IsDbOwner,
    @TargetLogin AS TargetLoginBeingDiagnosed;

-- Section 2: Login Status (SQL Server / MI only - not available in Azure SQL DB)
IF @EngineEdition NOT IN (5)
BEGIN
    -- Check if is_locked column exists (not available on all versions)
    IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('sys.sql_logins') AND name = 'is_locked')
    BEGIN
        EXEC sp_executesql N'
        SELECT TOP (@MaxRows)
            N''2. Login Status'' AS Section,
            sp.name AS LoginName,
            sp.type_desc AS LoginType,
            CASE WHEN sl.is_disabled = 1 THEN ''DISABLED'' ELSE ''Enabled'' END AS LoginStatus,
            CASE WHEN sl.is_locked = 1 THEN ''LOCKED'' ELSE ''Not Locked'' END AS LockStatus,
            CASE WHEN sl.is_expiration_checked = 1 THEN ''Yes'' ELSE ''No'' END AS PasswordExpirationChecked,
            sl.default_database_name AS DefaultDatabase,
            sp.create_date AS CreatedDate,
            sp.modify_date AS ModifiedDate
        FROM sys.server_principals sp
        LEFT JOIN sys.sql_logins sl ON sp.principal_id = sl.principal_id
        WHERE @ShowAll = 1 OR sp.name = @Login
        ORDER BY 
            CASE WHEN sl.is_disabled = 1 THEN 0 ELSE 1 END,
            CASE WHEN sl.is_locked = 1 THEN 0 ELSE 1 END,
            sp.name
        OPTION (MAXDOP 1);',
        N'@Login nvarchar(256), @ShowAll bit, @MaxRows int', @TargetLogin, @ShowAllLogins, @MaxResults;
    END
    ELSE
    BEGIN
        -- Fallback without is_locked for older versions
        SELECT TOP (@MaxResults)
            N'2. Login Status' AS Section,
            sp.name AS LoginName,
            sp.type_desc AS LoginType,
            CASE WHEN sl.is_disabled = 1 THEN 'DISABLED' ELSE 'Enabled' END AS LoginStatus,
            N'Unknown (column not available)' AS LockStatus,
            CASE WHEN sl.is_expiration_checked = 1 THEN 'Yes' ELSE 'No' END AS PasswordExpirationChecked,
            sl.default_database_name AS DefaultDatabase,
            sp.create_date AS CreatedDate,
            sp.modify_date AS ModifiedDate
        FROM sys.server_principals sp
        LEFT JOIN sys.sql_logins sl ON sp.principal_id = sl.principal_id
        WHERE @ShowAllLogins = 1 OR sp.name = @TargetLogin
        ORDER BY 
            CASE WHEN sl.is_disabled = 1 THEN 0 ELSE 1 END,
            sp.name
            OPTION (MAXDOP 1);
    END
END

-- Section 2b: Azure SQL DB contained users (when on Azure SQL DB)
IF @EngineEdition = 5
BEGIN
    SELECT TOP (@MaxResults)
        N'2. Database Users (Azure SQL DB)' AS Section,
        dp.name AS UserName,
        dp.type_desc AS UserType,
        dp.authentication_type_desc AS AuthType,
        dp.create_date AS CreatedDate,
        dp.modify_date AS ModifiedDate
    FROM sys.database_principals dp
    WHERE dp.type IN ('S', 'E', 'X')  -- SQL user, External user, External group
      AND (@ShowAllLogins = 1 OR dp.name = @CurrentUser)
    ORDER BY dp.name
    OPTION (MAXDOP 1);
END

-- Section 3: Database User Permissions (with SQL Server 2016 compatibility fallback)
DECLARE @SqlVersion int = CONVERT(int, SERVERPROPERTY('ProductMajorVersion'));

IF @SqlVersion >= 14 -- SQL Server 2017+ has STRING_AGG
BEGIN
    EXEC sp_executesql N'
    SELECT 
        N''3. Database Roles'' AS Section,
        dp.name AS UserName,
        dp.type_desc AS UserType,
        STRING_AGG(r.name, '', '') AS DatabaseRoles
    FROM sys.database_principals dp
    LEFT JOIN sys.database_role_members drm ON dp.principal_id = drm.member_principal_id
    LEFT JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id
    WHERE dp.name = @User
    GROUP BY dp.name, dp.type_desc
    OPTION (MAXDOP 1);',
    N'@User nvarchar(256)', @CurrentUser;
END
ELSE
BEGIN
    -- SQL Server 2016 compatible version using FOR XML PATH
    SELECT 
        N'3. Database Roles' AS Section,
        dp.name AS UserName,
        dp.type_desc AS UserType,
        STUFF((
            SELECT ', ' + r.name
            FROM sys.database_role_members drm
            JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id
            WHERE drm.member_principal_id = dp.principal_id
            FOR XML PATH(''), TYPE
        ).value('.', 'nvarchar(max)'), 1, 2, '') AS DatabaseRoles
    FROM sys.database_principals dp
    WHERE dp.name = @CurrentUser
    OPTION (MAXDOP 1);
END

-- Section 4: Effective Permissions on Current Database
-- fn_my_permissions returns: entity_name, subentity_name, permission_name
SELECT TOP (@MaxResults)
    N'4. Database Permissions' AS Section,
    entity_name AS EntityName,
    subentity_name AS SubEntityName,
    permission_name AS PermissionName
FROM fn_my_permissions(NULL, 'DATABASE')
ORDER BY permission_name
OPTION (MAXDOP 1);

-- Section 5: Orphaned Users Detection (users without matching server login)
IF @EngineEdition NOT IN (5, 11)  -- Not applicable for Azure SQL DB or Fabric
BEGIN
    SELECT TOP (@MaxResults)
        N'5. Orphaned Users' AS Section,
        dp.name AS UserName,
        dp.type_desc AS UserType,
        dp.sid AS UserSID,
        N'User has no matching server login. Fix with ALTER USER ... WITH LOGIN or DROP USER.' AS Resolution
    FROM sys.database_principals dp
    WHERE dp.type IN ('S', 'U')  -- SQL user or Windows user
      AND dp.sid IS NOT NULL
      AND dp.sid <> 0x00
      AND dp.name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys')
      AND NOT EXISTS (
          SELECT 1 FROM sys.server_principals sp WHERE sp.sid = dp.sid
      )
      OPTION (MAXDOP 1);
END
sql
SET NOCOUNT ON;

-- Parameters (set these before running)
DECLARE @LoginName nvarchar(256) = NULL;       -- NULL = current login, or specify login to check
DECLARE @ShowAllLogins bit = 0;                 -- 0 = specified login only, 1 = all logins
DECLARE @MaxResults int = 20;                   -- Max rows to return per section

DECLARE @EngineEdition int = CONVERT(int, SERVERPROPERTY('EngineEdition'));
DECLARE @TargetLogin nvarchar(256) = ISNULL(@LoginName, SUSER_SNAME());
DECLARE @CurrentUser nvarchar(256) = USER_NAME();

-- Section 1: Current Identity
SELECT 
    N'1. Current Identity' AS Section,
    SUSER_SNAME() AS CurrentServerLogin,
    @CurrentUser AS CurrentDatabaseUser,
    ORIGINAL_LOGIN() AS OriginalLogin,
    CONNECTIONPROPERTY('auth_scheme') AS AuthMethod,
    IS_SRVROLEMEMBER('sysadmin') AS IsSysAdmin,
    IS_MEMBER('db_owner') AS IsDbOwner,
    @TargetLogin AS TargetLoginBeingDiagnosed;

-- Section 2: Login Status (SQL Server / MI only - not available in Azure SQL DB)
IF @EngineEdition NOT IN (5)
BEGIN
    -- Check if is_locked column exists (not available on all versions)
    IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('sys.sql_logins') AND name = 'is_locked')
    BEGIN
        EXEC sp_executesql N'
        SELECT TOP (@MaxRows)
            N''2. Login Status'' AS Section,
            sp.name AS LoginName,
            sp.type_desc AS LoginType,
            CASE WHEN sl.is_disabled = 1 THEN ''DISABLED'' ELSE ''Enabled'' END AS LoginStatus,
            CASE WHEN sl.is_locked = 1 THEN ''LOCKED'' ELSE ''Not Locked'' END AS LockStatus,
            CASE WHEN sl.is_expiration_checked = 1 THEN ''Yes'' ELSE ''No'' END AS PasswordExpirationChecked,
            sl.default_database_name AS DefaultDatabase,
            sp.create_date AS CreatedDate,
            sp.modify_date AS ModifiedDate
        FROM sys.server_principals sp
        LEFT JOIN sys.sql_logins sl ON sp.principal_id = sl.principal_id
        WHERE @ShowAll = 1 OR sp.name = @Login
        ORDER BY 
            CASE WHEN sl.is_disabled = 1 THEN 0 ELSE 1 END,
            CASE WHEN sl.is_locked = 1 THEN 0 ELSE 1 END,
            sp.name
        OPTION (MAXDOP 1);',
        N'@Login nvarchar(256), @ShowAll bit, @MaxRows int', @TargetLogin, @ShowAllLogins, @MaxResults;
    END
    ELSE
    BEGIN
        -- Fallback without is_locked for older versions
        SELECT TOP (@MaxResults)
            N'2. Login Status' AS Section,
            sp.name AS LoginName,
            sp.type_desc AS LoginType,
            CASE WHEN sl.is_disabled = 1 THEN 'DISABLED' ELSE 'Enabled' END AS LoginStatus,
            N'Unknown (column not available)' AS LockStatus,
            CASE WHEN sl.is_expiration_checked = 1 THEN 'Yes' ELSE 'No' END AS PasswordExpirationChecked,
            sl.default_database_name AS DefaultDatabase,
            sp.create_date AS CreatedDate,
            sp.modify_date AS ModifiedDate
        FROM sys.server_principals sp
        LEFT JOIN sys.sql_logins sl ON sp.principal_id = sl.principal_id
        WHERE @ShowAllLogins = 1 OR sp.name = @TargetLogin
        ORDER BY 
            CASE WHEN sl.is_disabled = 1 THEN 0 ELSE 1 END,
            CASE WHEN sl.is_locked = 1 THEN 0 ELSE 1 END,
            sp.name
            OPTION (MAXDOP 1);
    END
END

-- Section 2b: Azure SQL DB contained users (when on Azure SQL DB)
IF @EngineEdition = 5
BEGIN
    SELECT TOP (@MaxResults)
        N'2. Database Users (Azure SQL DB)' AS Section,
        dp.name AS UserName,
        dp.type_desc AS UserType,
        dp.authentication_type_desc AS AuthType,
        dp.create_date AS CreatedDate,
        dp.modify_date AS ModifiedDate
    FROM sys.database_principals dp
    WHERE dp.type IN ('S', 'E', 'X')  -- SQL user, External user, External group
      AND (@ShowAllLogins = 1 OR dp.name = @CurrentUser)
    ORDER BY dp.name
    OPTION (MAXDOP 1);
END

-- Section 3: Database User Permissions (with SQL Server 2016 compatibility fallback)
DECLARE @SqlVersion int = CONVERT(int, SERVERPROPERTY('ProductMajorVersion'));

IF @SqlVersion >= 14 -- SQL Server 2017+ has STRING_AGG
BEGIN
    EXEC sp_executesql N'
    SELECT 
        N''3. Database Roles'' AS Section,
        dp.name AS UserName,
        dp.type_desc AS UserType,
        STRING_AGG(r.name, '', '') AS DatabaseRoles
    FROM sys.database_principals dp
    LEFT JOIN sys.database_role_members drm ON dp.principal_id = drm.member_principal_id
    LEFT JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id
    WHERE dp.name = @User
    GROUP BY dp.name, dp.type_desc
    OPTION (MAXDOP 1);',
    N'@User nvarchar(256)', @CurrentUser;
END
ELSE
BEGIN
    -- SQL Server 2016 compatible version using FOR XML PATH
    SELECT 
        N'3. Database Roles' AS Section,
        dp.name AS UserName,
        dp.type_desc AS UserType,
        STUFF((
            SELECT ', ' + r.name
            FROM sys.database_role_members drm
            JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id
            WHERE drm.member_principal_id = dp.principal_id
            FOR XML PATH(''), TYPE
        ).value('.', 'nvarchar(max)'), 1, 2, '') AS DatabaseRoles
    FROM sys.database_principals dp
    WHERE dp.name = @CurrentUser
    OPTION (MAXDOP 1);
END

-- Section 4: Effective Permissions on Current Database
-- fn_my_permissions returns: entity_name, subentity_name, permission_name
SELECT TOP (@MaxResults)
    N'4. Database Permissions' AS Section,
    entity_name AS EntityName,
    subentity_name AS SubEntityName,
    permission_name AS PermissionName
FROM fn_my_permissions(NULL, 'DATABASE')
ORDER BY permission_name
OPTION (MAXDOP 1);

-- Section 5: Orphaned Users Detection (users without matching server login)
IF @EngineEdition NOT IN (5, 11)  -- Not applicable for Azure SQL DB or Fabric
BEGIN
    SELECT TOP (@MaxResults)
        N'5. Orphaned Users' AS Section,
        dp.name AS UserName,
        dp.type_desc AS UserType,
        dp.sid AS UserSID,
        N'User has no matching server login. Fix with ALTER USER ... WITH LOGIN or DROP USER.' AS Resolution
    FROM sys.database_principals dp
    WHERE dp.type IN ('S', 'U')  -- SQL user or Windows user
      AND dp.sid IS NOT NULL
      AND dp.sid <> 0x00
      AND dp.name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys')
      AND NOT EXISTS (
          SELECT 1 FROM sys.server_principals sp WHERE sp.sid = dp.sid
      )
    OPTION (MAXDOP 1);
END

ListFirewallRules - Azure SQL Database server-level firewall rules

ListFirewallRules - Azure SQL Database 服务器级防火墙规则

sql
SET NOCOUNT ON;

-- Parameters (set these before running)
DECLARE @IPAddress varchar(45) = NULL;           -- NULL = auto-detect current client IP
DECLARE @IncludeVNetRules bit = 1;               -- 1 = include VNet service endpoint rules
DECLARE @MaxResults int = 50;                    -- Max firewall rules to return

DECLARE @EngineEdition int = CONVERT(int, SERVERPROPERTY('EngineEdition'));

-- Guard clause: sys.firewall_rules is only available on Azure SQL Database
IF @EngineEdition <> 5
BEGIN
    SELECT 
        N'Server Firewall Rules' AS Section,
        N'OUT OF SCOPE: This query only applies to Azure SQL Database (Engine Edition 5).' AS Message,
        CASE @EngineEdition
            WHEN 8 THEN N'Azure SQL Managed Instance uses NSG rules and VNET configuration instead of firewall rules.'
            WHEN 11 THEN N'Fabric SQL Database uses workspace permissions and network settings in Microsoft Fabric portal.'
            ELSE N'SQL Server on-premises/VM uses Windows Firewall and NSG rules instead.'
        END AS Guidance;
    RETURN;
END

-- Guard clause: sys.firewall_rules is only available in master database
IF DB_NAME() <> N'master'
BEGIN
    SELECT 
        N'Server Firewall Rules' AS Section,
        N'ERROR: Must connect to [master] database to query sys.firewall_rules.' AS RuleName,
        N'Run: USE master; or reconnect to master database.' AS Resolution;
    RETURN;
END

-- Use provided IP or auto-detect from current connection
DECLARE @ClientIP varchar(45) = ISNULL(@IPAddress, CONVERT(varchar(45), CONNECTIONPROPERTY('client_net_address')));
DECLARE @ClientIPInt bigint = NULL;

-- Convert client IP to bigint for range comparison (IPv4 only)
IF @ClientIP IS NOT NULL
   AND @ClientIP NOT LIKE '<%'
   AND CHARINDEX(':', @ClientIP) = 0
BEGIN
    SET @ClientIPInt =
        (TRY_CONVERT(bigint, PARSENAME(@ClientIP, 4)) * 256 * 256 * 256) +
        (TRY_CONVERT(bigint, PARSENAME(@ClientIP, 3)) * 256 * 256) +
        (TRY_CONVERT(bigint, PARSENAME(@ClientIP, 2)) * 256) +
        (TRY_CONVERT(bigint, PARSENAME(@ClientIP, 1)));
END

-- Show current client IP first
SELECT 
    N'Current Client IP' AS Section,
    @ClientIP AS ClientIP,
    CASE WHEN @IPAddress IS NULL THEN N'(auto-detected)' ELSE N'(user specified)' END AS Source;

-- List all firewall rules with client IP match indicator
SELECT TOP (@MaxResults)
    N'Server Firewall Rules' AS Section,
    name AS RuleName,
    start_ip_address AS StartIP,
    end_ip_address AS EndIP,
    CASE 
        WHEN start_ip_address = '0.0.0.0' AND end_ip_address = '0.0.0.0' 
        THEN 'Allow Azure Services (Security Risk!)'
        WHEN start_ip_address = end_ip_address 
        THEN 'Single IP'
        ELSE 'IP Range'
    END AS RuleType,
    CASE 
        WHEN @ClientIPInt IS NULL THEN 'Unable to check'
        WHEN @ClientIPInt BETWEEN 
            (TRY_CONVERT(bigint, PARSENAME(start_ip_address, 4)) * 256 * 256 * 256) +
            (TRY_CONVERT(bigint, PARSENAME(start_ip_address, 3)) * 256 * 256) +
            (TRY_CONVERT(bigint, PARSENAME(start_ip_address, 2)) * 256) +
            (TRY_CONVERT(bigint, PARSENAME(start_ip_address, 1)))
        AND 
            (TRY_CONVERT(bigint, PARSENAME(end_ip_address, 4)) * 256 * 256 * 256) +
            (TRY_CONVERT(bigint, PARSENAME(end_ip_address, 3)) * 256 * 256) +
            (TRY_CONVERT(bigint, PARSENAME(end_ip_address, 2)) * 256) +
            (TRY_CONVERT(bigint, PARSENAME(end_ip_address, 1)))
        THEN 'YES - Covers Current Client'
        ELSE 'No'
    END AS CoversCurrentClient
FROM sys.firewall_rules
ORDER BY 
    CASE WHEN start_ip_address = '0.0.0.0' AND end_ip_address = '0.0.0.0' THEN 0 ELSE 1 END,
    name
    OPTION (MAXDOP 1);

-- List Virtual Network Service Endpoint rules (if enabled and available)
IF @IncludeVNetRules = 1 AND OBJECT_ID('sys.virtual_network_rules', 'V') IS NOT NULL
BEGIN
    EXEC sp_executesql N'
    SELECT
        N''Virtual Network Rules'' AS Section,
        name AS RuleName,
        virtual_network_subnet_id AS SubnetResourceId,
        CASE 
            WHEN virtual_network_subnet_id LIKE ''%/subnets/%'' 
            THEN REVERSE(LEFT(REVERSE(virtual_network_subnet_id), CHARINDEX(''/'', REVERSE(virtual_network_subnet_id)) - 1))
            ELSE ''Unknown''
        END AS SubnetName,
        CASE state
            WHEN 0 THEN ''Initializing''
            WHEN 1 THEN ''InProgress''
            WHEN 2 THEN ''Ready''
            WHEN 3 THEN ''Deleting''
            WHEN 4 THEN ''Unknown''
            ELSE ''State: '' + CAST(state AS varchar(10))
        END AS RuleState,
        state_desc AS StateDescription
    FROM sys.virtual_network_rules
    ORDER BY name
    OPTION (MAXDOP 1);';
END
ELSE
BEGIN
    SELECT 
        N'Virtual Network Rules' AS Section,
        N'No VNet service endpoint rules configured (sys.virtual_network_rules not available or empty).' AS Message;
END
sql
SET NOCOUNT ON;

-- Parameters (set these before running)
DECLARE @IPAddress varchar(45) = NULL;           -- NULL = auto-detect current client IP
DECLARE @IncludeVNetRules bit = 1;               -- 1 = include VNet service endpoint rules
DECLARE @MaxResults int = 50;                    -- Max firewall rules to return

DECLARE @EngineEdition int = CONVERT(int, SERVERPROPERTY('EngineEdition'));

-- Guard clause: sys.firewall_rules is only available on Azure SQL Database
IF @EngineEdition <> 5
BEGIN
    SELECT 
        N'Server Firewall Rules' AS Section,
        N'OUT OF SCOPE: This query only applies to Azure SQL Database (Engine Edition 5).' AS Message,
        CASE @EngineEdition
            WHEN 8 THEN N'Azure SQL Managed Instance uses NSG rules and VNET configuration instead of firewall rules.'
            WHEN 11 THEN N'Fabric SQL Database uses workspace permissions and network settings in Microsoft Fabric portal.'
            ELSE N'SQL Server on-premises/VM uses Windows Firewall and NSG rules instead.'
        END AS Guidance;
    RETURN;
END

-- Guard clause: sys.firewall_rules is only available in master database
IF DB_NAME() <> N'master'
BEGIN
    SELECT 
        N'Server Firewall Rules' AS Section,
        N'ERROR: Must connect to [master] database to query sys.firewall_rules.' AS RuleName,
        N'Run: USE master; or reconnect to master database.' AS Resolution;
    RETURN;
END

-- Use provided IP or auto-detect from current connection
DECLARE @ClientIP varchar(45) = ISNULL(@IPAddress, CONVERT(varchar(45), CONNECTIONPROPERTY('client_net_address')));
DECLARE @ClientIPInt bigint = NULL;

-- Convert client IP to bigint for range comparison (IPv4 only)
IF @ClientIP IS NOT NULL
   AND @ClientIP NOT LIKE '<%'
   AND CHARINDEX(':', @ClientIP) = 0
BEGIN
    SET @ClientIPInt =
        (TRY_CONVERT(bigint, PARSENAME(@ClientIP, 4)) * 256 * 256 * 256) +
        (TRY_CONVERT(bigint, PARSENAME(@ClientIP, 3)) * 256 * 256) +
        (TRY_CONVERT(bigint, PARSENAME(@ClientIP, 2)) * 256) +
        (TRY_CONVERT(bigint, PARSENAME(@ClientIP, 1)));
END

-- Show current client IP first
SELECT 
    N'Current Client IP' AS Section,
    @ClientIP AS ClientIP,
    CASE WHEN @IPAddress IS NULL THEN N'(auto-detected)' ELSE N'(user specified)' END AS Source;

-- List all firewall rules with client IP match indicator
SELECT TOP (@MaxResults)
    N'Server Firewall Rules' AS Section,
    name AS RuleName,
    start_ip_address AS StartIP,
    end_ip_address AS EndIP,
    CASE 
        WHEN start_ip_address = '0.0.0.0' AND end_ip_address = '0.0.0.0' 
        THEN 'Allow Azure Services (Security Risk!)'
        WHEN start_ip_address = end_ip_address 
        THEN 'Single IP'
        ELSE 'IP Range'
    END AS RuleType,
    CASE 
        WHEN @ClientIPInt IS NULL THEN 'Unable to check'
        WHEN @ClientIPInt BETWEEN 
            (TRY_CONVERT(bigint, PARSENAME(start_ip_address, 4)) * 256 * 256 * 256) +
            (TRY_CONVERT(bigint, PARSENAME(start_ip_address, 3)) * 256 * 256) +
            (TRY_CONVERT(bigint, PARSENAME(start_ip_address, 2)) * 256) +
            (TRY_CONVERT(bigint, PARSENAME(start_ip_address, 1)))
        AND 
            (TRY_CONVERT(bigint, PARSENAME(end_ip_address, 4)) * 256 * 256 * 256) +
            (TRY_CONVERT(bigint, PARSENAME(end_ip_address, 3)) * 256 * 256) +
            (TRY_CONVERT(bigint, PARSENAME(end_ip_address, 2)) * 256) +
            (TRY_CONVERT(bigint, PARSENAME(end_ip_address, 1)))
        THEN 'YES - Covers Current Client'
        ELSE 'No'
    END AS CoversCurrentClient
FROM sys.firewall_rules
ORDER BY 
    CASE WHEN start_ip_address = '0.0.0.0' AND end_ip_address = '0.0.0.0' THEN 0 ELSE 1 END,
    name
    OPTION (MAXDOP 1);

-- List Virtual Network Service Endpoint rules (if enabled and available)
IF @IncludeVNetRules = 1 AND OBJECT_ID('sys.virtual_network_rules', 'V') IS NOT NULL
BEGIN
    EXEC sp_executesql N'
    SELECT
        N''Virtual Network Rules'' AS Section,
        name AS RuleName,
        virtual_network_subnet_id AS SubnetResourceId,
        CASE 
            WHEN virtual_network_subnet_id LIKE ''%/subnets/%'' 
            THEN REVERSE(LEFT(REVERSE(virtual_network_subnet_id), CHARINDEX(''/'', REVERSE(virtual_network_subnet_id)) - 1))
            ELSE ''Unknown''
        END AS SubnetName,
        CASE state
            WHEN 0 THEN ''Initializing''
            WHEN 1 THEN ''InProgress''
            WHEN 2 THEN ''Ready''
            WHEN 3 THEN ''Deleting''
            WHEN 4 THEN ''Unknown''
            ELSE ''State: '' + CAST(state AS varchar(10))
        END AS RuleState,
        state_desc AS StateDescription
    FROM sys.virtual_network_rules
    ORDER BY name
    OPTION (MAXDOP 1);';
END
ELSE
BEGIN
    SELECT 
        N'Virtual Network Rules' AS Section,
        N'No VNet service endpoint rules configured (sys.virtual_network_rules not available or empty).' AS Message;
END

CommonConnectivityErrors - Error code reference with state details

CommonConnectivityErrors - 包含状态详细信息的错误代码参考

sql
SET NOCOUNT ON;

-- Parameters (set these before running)
DECLARE @ErrorCode int = NULL;                   -- Specific error code to filter (NULL = all)
DECLARE @ErrorState int = NULL;                  -- For 18456 errors, specific state to highlight
DECLARE @Category nvarchar(50) = NULL;           -- Filter by category: 'Network', 'TLS/Certificate', 'Authentication', 'Azure Transient', 'Resource'
DECLARE @MaxResults int = 50;                    -- Max results to return

-- Error 18456 State Reference (separate section when @ErrorState is specified)
IF @ErrorState IS NOT NULL
BEGIN
    SELECT
        N'Error 18456 State Reference' AS Section,
        v.State,
        v.Meaning,
        v.Resolution
    FROM (VALUES
        (5,  N'Invalid user ID', N'Login does not exist. Verify login name spelling and case sensitivity.'),
        (7,  N'Login disabled', N'Login exists but is disabled. Run: ALTER LOGIN [name] ENABLE;'),
        (8,  N'Incorrect password', N'Password is wrong. Reset password or verify credentials.'),
        (9,  N'Password is not valid', N'The supplied password is invalid. Reset password or verify credentials.'),
        (11, N'Login valid but no server access', N'Login exists but lacks CONNECT SQL permission. Grant server access.'),
        (12, N'Login valid but no server access (trigger)', N'Same as 11, caused by login trigger.'),
        (18, N'Password must be changed', N'Password must be changed before login. User must change via SSMS or reset.'),
        (27, N'Initial database not found', N'Default database does not exist or is offline.'),
        (38, N'Requested database not found', N'Could not find the database requested by the user. Verify database name, existence, and that it is online.'),
        (46, N'Requested database not found', N'Could not find the database requested by the user (same as state 38). Verify database name, existence, and that it is online.'),
        (102, N'AAD authentication failed', N'Azure Active Directory authentication error (states 102-111). Check AAD configuration and user sync.'),
        (132, N'AAD authentication failed', N'Azure Active Directory authentication error (states 132-133). Check AAD configuration and user sync.')
    ) AS v(State, Meaning, Resolution)
    WHERE v.State = @ErrorState OR @ErrorState IS NULL
    OPTION (MAXDOP 1);
END

-- Common connectivity error codes with category for filtering
SELECT TOP (@MaxResults)
    v.ErrorCode,
    v.Category,
    v.[Description],
    v.[Resolution]
FROM (VALUES
    -- Authentication Errors
    (18456,  N'Authentication', N'Login failed',
             N'Check error STATE in SQL error log: State 5=Invalid user, State 7=Login disabled, State 8=Wrong password, State 9=Password not valid, State 11/12=Login valid but no server access, State 18=Password must be changed, State 38/46=Requested database not found, States 102-111/132-133=AAD auth failed. Run LoginDiagnostics query to check login status.'),
    (4060,   N'Authentication', N'Cannot open database requested by the login',
             N'Database may not exist, user lacks access, default database is invalid, or database is offline/restoring. Verify DB name, permissions, and DB state.'),
    
    -- Network Errors
    (53,     N'Network', N'Network path not found',
             N'DNS/name resolution or blocked path. Validate server FQDN and network reachability.'),
    (233,    N'Network', N'Connection broken',
             N'Forcibly closed. Check network stability, idle timeout, connection pooling.'),
    (10054,  N'Network', N'Connection reset by peer',
             N'Firewall timeout, proxy termination, or server-side kill. Check keep-alive settings.'),
    (10060,  N'Network', N'Connection timeout',
             N'Validate firewall/NSG/proxy/VPN path and DNS resolution.'),
    (10061,  N'Network', N'Connection refused',
             N'Service not started or endpoint unreachable. Verify target availability.'),
    
    -- Azure Transient Errors
    (40197,  N'Azure Transient', N'Service error processing request',
             N'Transient failover/scaling. Retry with backoff.'),
    (40532,  N'Azure Transient', N'Cannot open server',
             N'Firewall block. Run ListFirewallRules. For private endpoint, verify DNS.'),
    (40613,  N'Azure Transient', N'Database unavailable',
             N'Resuming/scaling/failing over. Retry with exponential backoff (max 5 retries, 5-30s).'),
    (40615,  N'Azure Transient', N'Cannot connect in region',
             N'Verify correct server FQDN for target region.'),
    (40143,  N'Azure Transient', N'Service tier not supported',
             N'Check DTU vs vCore requirements.'),
    
    -- Resource Limit Errors
    (10928,  N'Resource', N'Worker limit exceeded',
             N'Reduce concurrent connections or scale up.'),
    (10929,  N'Resource', N'Session limit reached',
             N'Close idle connections or scale up.'),
    (40544,  N'Resource', N'Database quota exceeded',
             N'Delete data, archive, or scale up storage.'),
    (40549,  N'Resource', N'Long-running transaction killed',
             N'Optimize query or break into smaller batches.'),
    (49918,  N'Resource', N'Insufficient resources',
             N'Scale up DTU/vCores. Check sys.dm_db_resource_stats.'),
    
    -- TLS/Certificate Errors
    (-2146893019, N'TLS/Certificate', N'SSL certificate validation failed',
             N'Check: cert valid/not expired, chain complete, client trusts root CA, name matches CN/SAN. Ensure TLS 1.2+ on client.'),
    (-2146893007, N'TLS/Certificate', N'Untrusted certificate authority',
             N'Install root CA in client trusted store, or TrustServerCertificate=True (not for production).'),
    (-2146893022, N'TLS/Certificate', N'Target principal name incorrect',
             N'FQDN vs certificate CN/SAN mismatch. Common with private endpoints + custom DNS.'),
    (772,    N'TLS/Certificate', N'Certificate chain untrusted',
             N'Check Windows certificate store for missing intermediate CAs.'),
    (64,     N'TLS/Certificate', N'TLS handshake terminated',
             N'TLS version/cipher mismatch. Ensure TLS 1.2 support. Update client drivers.'),
    (20,     N'TLS/Certificate', N'Certificate not validated',
             N'Check TLS settings, cert validity, and network proxies intercepting TLS.')
) AS v(ErrorCode, Category, [Description], [Resolution])
WHERE (@ErrorCode IS NULL OR v.ErrorCode = @ErrorCode)
  AND (@Category IS NULL OR v.Category = @Category)
ORDER BY v.Category, v.ErrorCode
OPTION (MAXDOP 1);
sql
SET NOCOUNT ON;

-- Parameters (set these before running)
DECLARE @ErrorCode int = NULL;                   -- Specific error code to filter (NULL = all)
DECLARE @ErrorState int = NULL;                  -- For 18456 errors, specific state to highlight
DECLARE @Category nvarchar(50) = NULL;           -- Filter by category: 'Network', 'TLS/Certificate', 'Authentication', 'Azure Transient', 'Resource'
DECLARE @MaxResults int = 50;                    -- Max results to return

-- Error 18456 State Reference (separate section when @ErrorState is specified)
IF @ErrorState IS NOT NULL
BEGIN
    SELECT
        N'Error 18456 State Reference' AS Section,
        v.State,
        v.Meaning,
        v.Resolution
    FROM (VALUES
        (5,  N'Invalid user ID', N'Login does not exist. Verify login name spelling and case sensitivity.'),
        (7,  N'Login disabled', N'Login exists but is disabled. Run: ALTER LOGIN [name] ENABLE;'),
        (8,  N'Incorrect password', N'Password is wrong. Reset password or verify credentials.'),
        (9,  N'Password is not valid', N'The supplied password is invalid. Reset password or verify credentials.'),
        (11, N'Login valid but no server access', N'Login exists but lacks CONNECT SQL permission. Grant server access.'),
        (12, N'Login valid but no server access (trigger)', N'Same as 11, caused by login trigger.'),
        (18, N'Password must be changed', N'Password must be changed before login. User must change via SSMS or reset.'),
        (27, N'Initial database not found', N'Default database does not exist or is offline.'),
        (38, N'Requested database not found', N'Could not find the database requested by the user. Verify database name, existence, and that it is online.'),
        (46, N'Requested database not found', N'Could not find the database requested by the user (same as state 38). Verify database name, existence, and that it is online.'),
        (102, N'AAD authentication failed', N'Azure Active Directory authentication error (states 102-111). Check AAD configuration and user sync.'),
        (132, N'AAD authentication failed', N'Azure Active Directory authentication error (states 132-133). Check AAD configuration and user sync.')
    ) AS v(State, Meaning, Resolution)
    WHERE v.State = @ErrorState OR @ErrorState IS NULL
    OPTION (MAXDOP 1);
END

-- Common connectivity error codes with category for filtering
SELECT TOP (@MaxResults)
    v.ErrorCode,
    v.Category,
    v.[Description],
    v.[Resolution]
FROM (VALUES
    -- Authentication Errors
    (18456,  N'Authentication', N'Login failed',
             N'Check error STATE in SQL error log: State 5=Invalid user, State 7=Login disabled, State 8=Wrong password, State 9=Password not valid, State 11/12=Login valid but no server access, State 18=Password must be changed, State 38/46=Requested database not found, States 102-111/132-133=AAD auth failed. Run LoginDiagnostics query to check login status.'),
    (4060,   N'Authentication', N'Cannot open database requested by the login',
             N'Database may not exist, user lacks access, default database is invalid, or database is offline/restoring. Verify DB name, permissions, and DB state.'),
    
    -- Network Errors
    (53,     N'Network', N'Network path not found',
             N'DNS/name resolution or blocked path. Validate server FQDN and network reachability.'),
    (233,    N'Network', N'Connection broken',
             N'Forcibly closed. Check network stability, idle timeout, connection pooling.'),
    (10054,  N'Network', N'Connection reset by peer',
             N'Firewall timeout, proxy termination, or server-side kill. Check keep-alive settings.'),
    (10060,  N'Network', N'Connection timeout',
             N'Validate firewall/NSG/proxy/VPN path and DNS resolution.'),
    (10061,  N'Network', N'Connection refused',
             N'Service not started or endpoint unreachable. Verify target availability.'),
    
    -- Azure Transient Errors
    (40197,  N'Azure Transient', N'Service error processing request',
             N'Transient failover/scaling. Retry with backoff.'),
    (40532,  N'Azure Transient', N'Cannot open server',
             N'Firewall block. Run ListFirewallRules. For private endpoint, verify DNS.'),
    (40613,  N'Azure Transient', N'Database unavailable',
             N'Resuming/scaling/failing over. Retry with exponential backoff (max 5 retries, 5-30s).'),
    (40615,  N'Azure Transient', N'Cannot connect in region',
             N'Verify correct server FQDN for target region.'),
    (40143,  N'Azure Transient', N'Service tier not supported',
             N'Check DTU vs vCore requirements.'),
    
    -- Resource Limit Errors
    (10928,  N'Resource', N'Worker limit exceeded',
             N'Reduce concurrent connections or scale up.'),
    (10929,  N'Resource', N'Session limit reached',
             N'Close idle connections or scale up.'),
    (40544,  N'Resource', N'Database quota exceeded',
             N'Delete data, archive, or scale up storage.'),
    (40549,  N'Resource', N'Long-running transaction killed',
             N'Optimize query or break into smaller batches.'),
    (49918,  N'Resource', N'Insufficient resources',
             N'Scale up DTU/vCores. Check sys.dm_db_resource_stats.'),
    
    -- TLS/Certificate Errors
    (-2146893019, N'TLS/Certificate', N'SSL certificate validation failed',
             N'Check: cert valid/not expired, chain complete, client trusts root CA, name matches CN/SAN. Ensure TLS 1.2+ on client.'),
    (-2146893007, N'TLS/Certificate', N'Untrusted certificate authority',
             N'Install root CA in client trusted store, or TrustServerCertificate=True (not for production).'),
    (-2146893022, N'TLS/Certificate', N'Target principal name incorrect',
             N'FQDN vs certificate CN/SAN mismatch. Common with private endpoints + custom DNS.'),
    (772,    N'TLS/Certificate', N'Certificate chain untrusted',
             N'Check Windows certificate store for missing intermediate CAs.'),
    (64,     N'TLS/Certificate', N'TLS handshake terminated',
             N'TLS version/cipher mismatch. Ensure TLS 1.2 support. Update client drivers.'),
    (20,     N'TLS/Certificate', N'Certificate not validated',
             N'Check TLS settings, cert validity, and network proxies intercepting TLS.')
) AS v(ErrorCode, Category, [Description], [Resolution])
WHERE (@ErrorCode IS NULL OR v.ErrorCode = @ErrorCode)
  AND (@Category IS NULL OR v.Category = @Category)
ORDER BY v.Category, v.ErrorCode
OPTION (MAXDOP 1);