connectivity-troubleshoot
Compare original and translation side by side
🇺🇸
Original
English🇨🇳
Translation
ChineseConnectivity
连接性
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 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 . Retry transient service/resume failures with bounds, not permanent credential failures. Do not open a firewall or broaden permissions as an automatic diagnostic step.
sqlcmdauthentication-diagnostics结合错误号、严重级别和驱动程序输出一起阅读。严重级别为 10 的信息性消息可能伴随进程成功退出,因此仅凭 退出状态无法确定成功。检查实际的客户端版本和行为。将 DNS/TCP/TLS 故障与身份验证或数据库访问故障分开;将登录、令牌、主体或授权诊断移交给 。重试瞬时服务或恢复故障时应设置界限,而不是重试永久性凭据故障。不要将打开防火墙或扩大权限作为自动诊断步骤。
sqlcmdauthentication-diagnosticsRequired 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 plugin (
microsoft-sql-admin,mssql_list_database_connections, thenmssql_connect_database); any other SQL tool the agent host provides for running T-SQL and reading result sets;mssql_execute_queryfrom a terminal. If the SQL Tools MCP server is not connected or lists no connections, follow thesqlcmdskill, 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.sql-tools-mcp-setup - Least privilege: connect that tool with a diagnostic login that cannot write, for example one that
holds only (
VIEW SERVER PERFORMANCE STATEbefore SQL Server 2022) or, on Azure SQL Database,VIEW SERVER STATEorVIEW DATABASE PERFORMANCE STATE, plus any other read permission these queries need. A tool is only as read-only as the login it connects with.##MS_ServerStateReader##
- 一个连接到目标服务器和数据库的只读 SQL 查询工具。按以下顺序优先选择:随 插件提供的 SQL Tools MCP server(
microsoft-sql-admin、mssql_list_database_connections,然后是mssql_connect_database);Agent 宿主机提供的任何其他用于运行 T-SQL 和读取结果集的 SQL 工具;从终端使用mssql_execute_query。如果 SQL Tools MCP server 未连接或未列出任何连接,请遵循sqlcmdskill,该 skill 会在安装或配置任何内容之前询问。如果无法提供任何 SQL 工具,请向用户提供要运行的 T-SQL,并要求其返回结果。sql-tools-mcp-setup - 最小权限:使用无法执行写入操作的诊断登录连接该工具,例如仅拥有 (SQL Server 2022 之前为
VIEW SERVER PERFORMANCE STATE)权限,或在 Azure SQL Database 上拥有VIEW SERVER STATE或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, and granting server access) change security principals. They are recommendations only.ALTER LOGIN ... ENABLE - 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 value returned by the query (or the current system date) for the report header date.
QueryExecutedAtUTCConnectivityChecker根据用户症状按顺序执行以下步骤。对于报告标题日期,使用 查询返回的 值(或当前系统日期)。
ConnectivityCheckerQueryExecutedAtUTCPre-Connection Troubleshooting
连接前故障排查
If connection cannot be established at all, guide the user through client-side diagnostics first:
- DNS Resolution:
nslookup <server>.database.windows.net - Port Reachability:
Test-NetConnection -ComputerName <server> -Port 1433 - Azure SQL Connectivity Checker: https://github.com/Azure/SQL-Connectivity-Checker
如果完全无法建立连接,请先指导用户进行客户端诊断:
- DNS 解析:
nslookup <server>.database.windows.net - 端口可达性:
Test-NetConnection -ComputerName <server> -Port 1433 - 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 (for example
plus the appropriate authentication flags, such as for Windows authentication or
/ for Microsoft Entra).
Do not put passwords or other credentials in commands or examples; let the tool or the user supply them.
sqlcmdsqlcmd -S <server> -d <database> -N-E--authentication-method ActiveDirectoryDefaultActiveDirectoryInteractiveThen 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 工具进行连接,或使用 (例如 ,并加上适当的身份验证标志,如用于 Windows authentication 的 ,或用于 Microsoft Entra 的 / )。
不要在命令或示例中放入密码或其他凭据;让工具或用户提供凭据。
sqlcmdsqlcmd -S <server> -d <database> -N-E--authentication-method ActiveDirectoryDefaultActiveDirectoryInteractive然后确认连接以及当前连接的服务器和数据库:
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 query. This returns:
ConnectivityChecker- 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):
- Run query to get targeted resolution guidance
CommonConnectivityErrors - For login errors (18456): Run to check login status, lock state, and permissions
LoginDiagnostics - For firewall errors (40532): Run (Azure SQL DB only, requires
ListFirewallRulesdatabase)master
如果用户提供错误代码(例如 18456、4060、40613):
- 运行 查询,以获取针对性的解决建议
CommonConnectivityErrors - 对于登录错误(18456):运行 ,检查登录状态、锁定状态和权限
LoginDiagnostics - 对于防火墙错误(40532):运行 (仅限 Azure SQL DB,且需要
ListFirewallRules数据库)master
Step 4: Platform-Specific Checks
步骤 4:平台专项检查
Detect platform via Engine Edition, then run appropriate checks:
| Platform | Engine Edition | Additional Checks |
|---|---|---|
| Azure SQL Database | 5 | |
| Azure SQL Managed Instance | 8 | |
| Fabric SQL Database | 11 | |
| SQL Server (VM/on-prem) | 1-4 | |
通过引擎版本检测平台,然后运行适当的检查:
| 平台 | 引擎版本 | 附加检查 |
|---|---|---|
| Azure SQL Database | 5 | |
| Azure SQL Managed Instance | 8 | |
| Fabric SQL Database | 11 | |
| SQL Server(VM/本地部署) | 1-4 | |
Step 5: Generate Report
步骤 5:生成报告
Present findings using the Report Template below.
使用下方的报告模板呈现结果。
Parameters
参数
| Parameter | Default | Description |
|---|---|---|
| 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: |
| DiagnosticLevel | | Level of diagnostics: |
| LoginName | (current) | Login name to diagnose (defaults to current login). Use to check another user's status |
| ShowAllLogins | | 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 | | Whether to run firewall rule analysis (Azure SQL Database only) |
| IncludeLoginDiagnostics | | Whether to run login status and permission checks |
| IncludeEncryptionCheck | | Whether to verify TLS/encryption status |
| IncludeVNetRules | | Whether to query Virtual Network service endpoint rules |
| Port | | Target TCP port for connectivity validation and recommendations |
| MaxResults | | Maximum rows to return per diagnostic section |
| TopRecommendations | | Maximum number of platform-specific recommendations to return |
| 参数 | 默认值 | 说明 |
|---|---|---|
| ErrorCode | (无) | 要过滤诊断并显示针对性解决建议的特定 SQL 错误码(例如 18456、4060、40613) |
| ErrorState | (无) | 对于错误 18456,要获取针对性解决的特定状态号(5、7、8、11、18、38、40) |
| Category | (无) | 按类别筛选错误: |
| DiagnosticLevel | | 诊断级别: |
| LoginName | (当前) | 要诊断的登录名称(默认为当前登录)。用于检查其他用户的状态 |
| ShowAllLogins | | 显示所有登录和用户,而不只是当前登录。适用于安全审计 |
| IPAddress | (自动检测) | 要根据防火墙规则验证的客户端 IP(默认为当前连接 IP) |
| IncludeFirewallCheck | | 是否运行防火墙规则分析(仅限 Azure SQL Database) |
| IncludeLoginDiagnostics | | 是否运行登录状态和权限检查 |
| IncludeEncryptionCheck | | 是否验证 TLS/加密状态 |
| IncludeVNetRules | | 是否查询 Virtual Network 服务终结点规则 |
| Port | | 用于连接验证和建议的目标 TCP 端口 |
| MaxResults | | 每个诊断部分最多返回的行数 |
| TopRecommendations | | 最多返回的平台专项建议数量 |
Report Template
报告模板
Present results in this exact format:
undefined使用以下确切格式呈现结果:
undefinedConnectivity 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}}
| Property | Value |
|---|---|
| Auth Method | {{AuthScheme}} |
| Encryption | {{EncryptionEnabled}} |
| TDS Protocol Version | {{TDSProtocolVersion}} |
| Client IP | {{ClientIP}} |
| Server Port | {{ServerPort}} |
| Property | Value |
|---|---|
| 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}}
| Severity | Issue | Details |
|---|---|---|
| {{one row per issue detected}} |
{{List issues with severity: Critical/Warning/Info}}
| Severity | Issue | Details |
|---|---|---|
| {{one row per issue detected}} |
Recommended Actions
Recommended Actions
{{Numbered list of platform-specific recommendations, ordered by priority}}
- {{Most critical action}}
- {{Next action}} ...
{{Numbered list of platform-specific recommendations, ordered by priority}}
- {{Most critical action}}
- {{Next action}} ...
Additional Resources
Additional Resources
- Azure SQL Connectivity Checker: https://github.com/Azure/SQL-Connectivity-Checker
- Connectivity Settings: https://learn.microsoft.com/en-us/azure/azure-sql/database/connectivity-settings
- Troubleshooting Checklist: https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/connect/resolve-connectivity-errors-checklist
undefined- Azure SQL Connectivity Checker: https://github.com/Azure/SQL-Connectivity-Checker
- Connectivity Settings: https://learn.microsoft.com/en-us/azure/azure-sql/database/connectivity-settings
- Troubleshooting Checklist: https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/connect/resolve-connectivity-errors-checklist
undefinedSQL 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;
ENDsql
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;
ENDTestConnectivity - 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);
ENDsql
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);
ENDListFirewallRules - 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;
ENDsql
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;
ENDCommonConnectivityErrors - 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);