排查使用 Microsoft Entra Connect 时出现的 SQL 连接性问题
本文说明如何排查 Microsoft Entra Connect 与 SQL Server 之间的连接性问题。
下面的屏幕截图显示了找不到 SQL Server 时的典型错误。
疑难解答步骤
使用“以管理员身份运行”打开 PowerShell 窗口并安装/导入 ADSyncTools PowerShell 模块。
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force
Install-Module ADSyncTools
Import-Module ADSyncTools
注意
Install-Module 需要更新到 PowerShell 5.0 (WMF 5.0) 或更高版本；
或者安装 PackageManagement PowerShell 模块预览 - 2016 年 3 月，适用于 PowerShell 3.0/4.0
- 显示所有命令：
Get-Command *Sql* -Module ADSyncTools - 执行 PowerShell 函数：具有以下参数的
Connect-ADSyncToolsSqlDatabase：- 服务器：SQL Server 名称。
- 实例（可选）：SQL Server 实例名称和（可选）你要使用的端口号。 不要指定此参数来使用默认实例。
- 端口（可选）：SQL Server 端口
- 用户名（可选）：用于连接的用户帐户，在留空的情况下使用当前登录帐户。 如果要连接到远程 SQL Server，则此用户名应该是为 Microsoft Entra Connect SQL 连接创建的自定义服务帐户。 Microsoft Entra Connect 使用 Microsoft Entra Connect 同步服务帐户向远程 SQL Server 进行身份验证。
- 密码（可选）：所提供的 UserName 的密码。
此 PowerShell 函数会尝试使用传入的凭据或使用当前用户的凭据绑定到指定的 SQL Server 和实例。 如果找不到 SQL Server，该脚本会尝试连接到 SQL Browser 服务，以确定已启用的协议和端口。
仅使用服务器名称的示例：
PS C:\> Connect-ADSyncToolsSqlDatabase -Server SQL1.contoso.com
Resolving server address : SQL1.contoso.com
InterNetworkV6 : fe80::6c90:a995:3e70:ef74%17
InterNetworkV6 : 2001:4898:e0:66:6c90:a995:3e70:ef74
InterNetwork : 10.91.26.143
Attempting to connect to SQL1 using a TCP binding for the default instance.
Data Source=tcp:SQL1.contoso.com\;Integrated Security=True.ConnectionString
Successfully connected.
StatisticsEnabled : False
AccessToken :
ConnectionString : Data Source=tcp:SQL1\;Integrated Security=True
ConnectionTimeout : 15
Database : master
DataSource : tcp:SQL1.contoso.com\
PacketSize : 8000
ClientConnectionId : 23e06ef2-0a38-4f5f-9291-da931de40375
ServerVersion : 13.00.4474
State : Open
WorkstationId : SQL1
Credential :
FireInfoMessageEventOnUserErrors : False
Site :
Container :
使用服务器名称和 SQL 命名实例的示例：
PS C:\> Connect-ADSyncToolsSqlDatabase -Server SQL1.contoso.com -Instance SQLINSTANCE1
Resolving server address : SQL1.contoso.com
InterNetwork: 10.0.100.24
Attempting to connect to SQL1.contoso.com\SQLINSTANCE1 using a TCP binding.
Data Source=tcp:SQL1.contoso.com\SQLINSTANCE1;Integrated Security=True
Successfully connected.
StatisticsEnabled : False
AccessToken :
ConnectionString : Data Source=tcp:SQL1.contoso.com\SQLINSTANCE1;Integrated Security=True
ConnectionTimeout : 15
Database : master
DataSource : tcp:SQL1.contoso.com\SQLINSTANCE1
PacketSize : 8000
ClientConnectionId : 2b365b7a-4348-45f6-9314-d6b56db36dbd
ServerVersion : 13.00.4259
State : Open
WorkstationId : SQL1
Credential :
FireInfoMessageEventOnUserErrors : False
Site :
Container :
使用无法访问的 SQL 实例的示例。 它会尝试查询 SQL Server Browser 服务并显示可用的 SQL 实例和相应的端口。
PS C:\> Connect-ADSyncToolsSqlDatabase -Server SQL01.Contoso.com -Instance DEFAULT
Resolving server address : SQL01.Contoso.com
InterNetwork: 10.0.100.24
Attempting to connect to SQL01.Contoso.com\SQL using a TCP binding.
Data Source=tcp:SQL01.Contoso.com\SQL;Integrated Security=True
Connect-ADSyncToolsSqlDatabase : Unable to connect using a TCP binding. A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was
not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance
Specified)
At line:1 char:1
+ Connect-ADSyncToolsSqlDatabase -Server SQL01.Contoso.com -Insta ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : ConnectionError: (:) [Write-Error], WriteErrorException
+ FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Connect-ADSyncToolsSqlDatabase
TROUBLESHOOTING: Attempting to query the SQL Server Browser service configuration on SQL01.Contoso.com.
SQL browser response contained 2 instances.
Verifying protocol bindings and port connectivity...
SQLINSTANCE1 : Enabled - port 49823 is assigned and reachable through the firewall
SQL2019 : Enabled - port 50631 is assigned and reachable through the firewall
WHAT TO TRY NEXT:
Each SQL instance must be bound to an explicit static TCP port and paired with an inbound firewall rule on SQL01.Contoso.com to allow connection. Review the TcpStatus field for each instance and take cor
rective action.
InstanceName : SQLINSTANCE1
tcp : 49823
TcpStatus : Enabled - port 49823 is assigned and reachable through the firewall
InstanceName : SQL2019
tcp : 50631
TcpStatus : Enabled - port 50631 is assigned and reachable through the firewall