Felsöka SQL-anslutningsproblem med Microsoft Entra Anslut
I den här artikeln beskrivs hur du felsöker anslutningsproblem mellan Microsoft Entra Anslut och SQL Server.
Följande skärmbild visar ett typiskt fel om SQL Server inte kan hittas.
Felsökningsanvisningar
Öppna ett PowerShell-fönster och importera ADSyncTools PowerShell-modulen
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force
Import-module -Name "C:\Program Files\Microsoft Azure Active Directory Connect\Tools\AdSyncTools"
Kommentar
Install-Module kräver uppdatering till PowerShell 5.0 (WMF 5.0) eller senare.
Eller installera PackageManagement PowerShell-modulförhandsgranskning – mars 2016 för PowerShell 3.0/4.0
- Visa alla kommandon:
Get-Command -Module AdSyncTools - Kör PowerShell-funktionen:
Connect-ADSyncDatabasemed följande parametrar- Server. SQL Server-namnet.
- Instans. (Valfritt) Namnet på SQL Server-instansen och valfritt portnummer som du vill använda. Ange inte den här parametern för att använda standardinstansen.
- Användarnamn. (Valfritt) Det användarkonto som ska anslutas till. Om det lämnas tomt används den inloggade användaren. Om du ansluter till en fjärransluten SQL Server bör detta vara det anpassade tjänstkontot som du har skapat för Microsoft Entra Anslut SQL-Anslut ivity. Microsoft Entra Anslut använder Microsoft Entra Anslut Sync-tjänstkontot för att autentisera till en fjärransluten SQL-server.
- Lösenord. (Valfritt) Lösenord för angivet användarnamn.
Den här PowerShell-funktionen försöker binda till den angivna SQL Server och instansen med hjälp av autentiseringsuppgifterna som skickas i ELLER använda den aktuella användarens autentiseringsuppgifter. Om DET inte går att hitta SQL Server försöker skriptet ansluta till SQL Browser-tjänsten för att fastställa aktiverade protokoll och portar.
Exempel med bara ett servernamn:
PS C:\Program Files\Microsoft Azure Active Directory Connect\Tools> import-module .\AdSyncTools.psm1
PS C:\Program Files\Microsoft 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\Microsoft Azure Active Directory Connect\Tools>
Exempel med ett instans- och portnummer som inte finns:
PS C:\Program Files\Microsoft 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\Microsoft 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\Microsoft Azure Active Directory Connect\tools>
PS C:\Program Files\Microsoft 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\Microsoft Azure Active Directory Connect\tools>