排除使用 Azure AD Connect 时的 SQL 连接问题Troubleshoot SQL connectivity issues with Azure AD Connect

本文说明如何排查 Azure AD Connect 与 SQL Server 之间的连接问题。This article explains how to troubleshoot connectivity issues between Azure AD Connect and SQL Server.

下面的屏幕截图显示了找不到 SQL Server 时的典型错误。The following screenshot shows a typical error, if the SQL Server cannot be found.

SQL 错误

疑难解答步骤Troubleshooting steps

打开 powershell 窗口并导入 ADSyncTools Powershell 模块Open a powershell window and Import the ADSyncTools Powershell module

Import-Module "C:\Program Files\Azure Active Directory Connect\Tools\AdSyncTools.psm1" 
  • 显示所有命令:Get-Command -Module AdSyncToolsShow all commands: Get-Command -Module AdSyncTools
  • 执行 powershell 函数:具有以下参数的 Connect-ADSyncDatabaseExecute the powershell function: Connect-ADSyncDatabase with the following parameters
    • Server。Server. SQL Server 名称。The SQL Server name.
    • Instance。Instance. (可选)SQL Server 实例名称和(可选)你要使用的端口号。(Optional) The SQL Server Instance name and optionally Port number, that you would like to use. 不要指定此参数来使用默认实例。Do not specify this parameter to use the default instance.
    • UserName。UserName. (可选)用于连接的用户帐户。(Optional) The user account to connect with. 如果留空,将使用当前登录的用户。If left blank the currently logged in user will be used. 如果要连接到远程 SQL Server,应使用你为 Azure ADConnect SQL 连接创建的自定义服务帐户。If you are connecting to a remote SQL Server this should be the custom service account you have created for Azure ADConnect SQL Connectivity. Azure AD Connect 使用 Azure AD Connect 同步服务帐户向远程 SQL Server 进行身份验证。Azure AD Connect uses the Azure AD Connect sync service account as to authenticate to a remote SQL server.
    • Password。Password. (可选)所提供的 UserName 的密码。(Optional) Password for the UserName provided.

此 powershell 函数将尝试使用传入的凭据或使用当前用户的凭据绑定到指定的 SQL Server。This powershell function will attempt to bind to the specified SQL Server and Instance using the credentials passed in OR use the credentials of the current user. 如果找不到 SQL Server,该脚本将尝试连接到 SQL Browser 服务,以确定已启用的协议和端口。If the SQL Server cannot be found the script will attempt to connect to the SQL Browser service to determine enabled protocols and ports.

仅使用服务器名称的示例:Example using just a Server name:

PS C:\Program Files\Azure Active Directory Connect\Tools> import-module .\AdSyncTools.psm1

PS C:\Program Files\Azure Active Directory Connect\Tools> Connect-AdSyncDatabase -Server SQL1
Resolving server address : SQL1
    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\;Integrated Security=True.ConnectionString
   Successfully connected.


StatisticsEnabled                : False
AccessToken                      : 
ConnectionString                 : Data Source=tcp:SQL1\;Integrated Security=True
ConnectionTimeout                : 15
Database                         : master
DataSource                       : tcp:SQL1\
PacketSize                       : 8000
ClientConnectionId               : 23e06ef2-0a38-4f5f-9291-da931de40375
ServerVersion                    : 13.00.4474
State                            : Open
WorkstationId                    : SQL1
Credential                       : 
FireInfoMessageEventOnUserErrors : False
Site                             : 
Container                        : 




PS C:\Program Files\Azure Active Directory Connect\Tools> 

使用不存在的实例和端口号的示例:Example using an Instance and Port number that don’t exist:

PS C:\Program Files\Azure Active Directory Connect\tools> Connect-AdSyncDatabase -Server SQL1 -Instance "INSTANCE1"
Resolving server address : SQL1
    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\INSTANCE1 using a TCP binding.
   Data Source=tcp:SQL1\INSTANCE1;Integrated Security=True.ConnectionString
Connect-AdSyncDatabase : 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-AdSyncDatabase -Server SQL1 -Instance "INSTANCE1"
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ConnectionError: (:) [Write-Error], WriteErrorException
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Connect-AdSyncDatabase
 

TROUBLESHOOTING: Attempting to query the SQL Server Browser service configuration on SQL1.
Get-ADSyncSQLBrowserInstances : Unable to read the SQL Server Browser configuration. An existing connection was forcibly closed by the remote host. 
Ensure port 1434 (UDP) is open on SQL1 and the SQL Server Browser service is running. 
At C:\Program Files\Azure Active Directory Connect\tools\AdSyncTools.psm1:1717 char:18
+     $instances = Get-ADSyncSQLBrowserInstances $Server
+                  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ConnectionError: (:) [Write-Error], WriteErrorException
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Get-ADSyncSQLBrowserInstances
 


WHAT TO TRY NEXT:

Each SQL instance must be bound to an explicit static TCP port and paired with an inbound firewall rule on SQL1 to allow connection. Enable the SQL Se
rver Browser service temporarily on the SQL server and run this cmdLet again to further troubleshoot the issue. Alternatively use the SQL Server Configur
ation Manager on SQL1 to verify the instance name and TCP/IP port assignment manually. 

You must specify both the instance name and the port to connect when the SQL Server Browser service is not running. An inbound firewall rule on SQL1 is required for the associated port.
    Example: 'MySQLInstance,1234' where 1234 has a matching firewall rule.

PS C:\Program Files\Azure Active Directory Connect\tools> 
PS C:\Program Files\Azure Active Directory Connect\tools> Connect-AdSyncDatabase -Server SQL1 -Instance "INSTANCE1,99"
Resolving server address : SQL1
    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\INSTANCE1,99 using a TCP binding.
   Data Source=tcp:SQL1\INSTANCE1,99;Integrated Security=True.ConnectionString
Connect-AdSyncDatabase : 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: TCP Provider, error: 0 - The remote computer refused the network connection.)
At line:1 char:1
+ Connect-AdSyncDatabase -Server SQL1 -Instance "INSTANCE1,99"
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ConnectionError: (:) [Write-Error], WriteErrorException
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Connect-AdSyncDatabase
 

TROUBLESHOOTING: Attempting to query the SQL Server Browser service configuration on SQL1.
SQL browser response contained 2 instances.
Verifying protocol bindings and port connectivity.
MSSQLSERVER     : Enabled - port 1433 is assigned and reachable through the firewall
INSTANCE1       : Blocked - the inbound firewall rule for port 58379 is missing or disabled


WHAT TO TRY NEXT:

Each SQL instance must be bound to an explicit static TCP port and paired with an
inbound firewall rule on SQL1 to allow connection. Review the TcpStatus field
for each instance and take corrective action.


InstanceName : MSSQLSERVER
tcp          : 1433
TcpStatus    : Enabled - port 1433 is assigned and reachable through the firewall

InstanceName : INSTANCE1
tcp          : 58379
TcpStatus    : Blocked - the inbound firewall rule for port 58379 is missing or disabled




PS C:\Program Files\Azure Active Directory Connect\tools>  

后续步骤Next Steps