Project

Script to test Synapse / Fabric connectivity endpoints and ports needed

• Check name resolution for all possible endpoints used by Synapse
• Check if ports needed are open (1433 / 1443 / 443)
• Check Windows HOST File entries
• Check DNS configuration
• Check Internet and Self Hosted IR proxy that change name resolution from local machine to proxy
• To make this test it might open AD auth / MFA / to be able to make tests below (Connect-AzAccount -Subscription $SubscriptionID)
  • Make API test calls to apis like management.azure.com / https://WORKSPACE.dev.azuresynapse.net
  • Try to connect to SQL and SQLOndemand APIs using port 1433

Note

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE

Table of Contents

1. Requirements
2. Execution
   • Option1 - Test Synapse from Windows Running from Web
   • Option2 - Test Synapse from Windows Offline Version
   • Option3 - Test Synapse from Windows Automation script
   • Option4 - Test Synapse from MAC Offline Version
   • Option5 - Test Synapse from Linux Offline Version
   • Option6 - Test Fabric from Windows Running from Web
   • Option7 - Test Fabric from Windows Offline Version

Requirements

Requirements - Synapse Edition - Windows

• Min Powershell V5.0
• If want to run as script, might need
  • Set-ExecutionPolicy -ExecutionPolicy Unrestricted -Scope Process
• Import-Module DnsClient
• Import-Module Az.Accounts -MinimumVersion 2.2.0
  • Install-Module -Name Az -Repository PSGallery -Force
• Import-Module SQLServer
  • Install-Module -Name SqlServer -Repository PSGallery -Force

Requirements - Synapse Edition - MAC / Linux

• For MAC / Linux scripts it will use Bash script

Requirements - Fabric Edition - Windows

• Min Powershell V5.0
• If want to run as script, might need
  • Set-ExecutionPolicy -ExecutionPolicy Unrestricted -Scope Process
• Import-Module DnsClient
• SQLCMD
  • https://learn.microsoft.com/en-us/sql/tools/sqlcmd/sqlcmd-utility?view=sql-server-ver16&tabs=odbc%2Cwindows&pivots=cs1-powershell#download-and-install-sqlcmd

Data Collection

No data is sent to Microsoft by running this script

But you can send the results back to Microsoft or attach in a support case for further troubleshooting

Execution

Option 1 (Synapse-Windows-Web) - Execute last version directly from WEB (Require internet connection)

• Open Powershell ISE copy below script

ProgressPreference = "SilentlyContinue";
$parameters = @{
   WorkspaceName = 'WORKSPACENAME' # Enter your Synapse Workspace name. Not FQDN just name
   SubscriptionID = 'de41dc76-xxxx-xxxx-xxxx-xxxx'  # Subscription ID where Synapse Workspace is located
   DedicatedSQLPoolDBName = ''  
   ServerlessPoolDBName = '' 
}
$scriptUrl = 'https://raw.githubusercontent.com/microsoft/Azure-Synapse-Connectivity-Checker/main/Synapse-TestConnection.ps1'
cls
Write-Host 'Trying to download the script file from GitHub (https://github.com/microsoft/Azure-Synapse-Connectivity-Checker/), please wait...'
try {
   [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12 -bor [Net.SecurityProtocolType]::Tls11 -bor [Net.SecurityProtocolType]::Tls
   Invoke-Command -ScriptBlock ([Scriptblock]::Create((Invoke-WebRequest ($scriptUrl) -UseBasicParsing -TimeoutSec 60).Content)) -ArgumentList $parameters
}
catch {
   Write-Host 'ERROR: The script file could not be downloaded:' -ForegroundColor Red
   $_.Exception
   Write-Host 'Confirm this machine can access https://github.com/microsoft/Azure-Synapse-Connectivity-Checker/' -ForegroundColor Yellow
   Write-Host 'or use a machine with Internet access to see how to run this from machines without Internet.' -ForegroundColor Yellow
     }

• Change variables
• Execute the script

Option 2 (Synapse-Windows-Offline) - Download, change parameters and run

• Open Powershell ISE
• Copy / Past content of script (Synapse-TestConnection.ps1)
• Change variables

- $WorkspaceName = 'WORKSPACENAME' # Enter your Synapse Workspace name. Not FQDN just name
- $SubscriptionID = 'de41dc76-xxxx-xxxx-xxxx-xxxx'  # Subscription ID where Synapse Workspace is located
- $DedicatedSQLPoolDBName = ''  # Add here DB name you are testing connection. If you keep it empty it will test connectivity agains master DB
- $ServerlessPoolDBName = ''    # Add here DB name you are testing connection. If you keep it empty it will test connectivity agains master DB

• Execute the script

Option 3 (Synapse-Windows-Automation) - Run locally but with parameters (Best when need to run multiple times / automation)

• Copy (Synapse-TestConnection.ps1) script file to a folder

• On Powershell copy below script

$parameters = @{
   WorkspaceName = 'WORKSPACENAME' # Enter your Synapse Workspace name. Not FQDN just name
   SubscriptionID = 'de41dc76-xxxx-xxxx-xxxx-xxxx'  # Subscription ID where Synapse Workspace is located
   DedicatedSQLPoolDBName = ''  # Add here DB name you are testing connection. If you keep it empty it will test connectivity agains master DB
   ServerlessPoolDBName = ''  # Add here DB name you are testing connection. If you keep it empty it will test connectivity agains master DB
}

$FilePath = 'C:\TEMP\Synapse-TestConnection.ps1'
Invoke-Command -ScriptBlock ([Scriptblock]::Create((Get-Content -Path $FilePath -Raw))) -ArgumentList $parameters

• Change variables
• Execute the script

Option 4 (Synapse-MAC-Offline) - This uses Bash Script

• Copy (Synapse-TestConnection-mac.sh) script file to a folder

• Change variable

workspacename="REPLACEWORKSPACENAME"

• Grant permission and Execute the script

chmod +x Synapse-TestConnection-mac.sh

./Synapse-TestConnection-mac.sh

Note

Known issue

• ERROR: bad interpreter: /bin/bash^M: no such file or directory
• CAUSE: Windows line ending
• FIX. : Run command: sed -i -e 's/\r$//' Synapse-TestConnection-mac.sh
• REF. : https://stackoverflow.com/questions/14219092/bash-script-bin-bashm-bad-interpreter-no-such-file-or-directory

Option 5 (Synapse-Linux-Offline) - This uses Bash Script

• Copy (Synapse-TestConnection-linux.sh) script file to a folder

• Change variable

workspacename="REPLACEWORKSPACENAME"

• Grant permission and Execute the script

chmod +x Synapse-TestConnection-linux.sh

./Synapse-TestConnection-linux.sh

Option 6 (Fabric-Windows-Web) - Execute last version directly from WEB (Require internet connection)

• Open Powershell ISE copy below script

ProgressPreference = "SilentlyContinue";
$parameters = @{
   FabricEndpoint = "xxxx-xxxx.datawarehouse.pbidedicated.windows.net" # Enter your Fabric SQL Endpoint
   AADUser = "xxxx@domain.com" # AAD user that will be used to authenticate
   DatabaseName = "master" # Enter your Database Name
}
$scriptUrl = 'https://raw.githubusercontent.com/microsoft/Azure-Synapse-Connectivity-Checker/main/Fabric/Fabric-TestConnection.ps1'
cls
Write-Host 'Trying to download the script file from GitHub (https://github.com/microsoft/Azure-Synapse-Connectivity-Checker/), please wait...'
try {
   [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12 -bor [Net.SecurityProtocolType]::Tls11 -bor [Net.SecurityProtocolType]::Tls
   Invoke-Command -ScriptBlock ([Scriptblock]::Create((Invoke-WebRequest ($scriptUrl) -UseBasicParsing -TimeoutSec 60).Content)) -ArgumentList $parameters
}
catch {
   Write-Host 'ERROR: The script file could not be downloaded:' -ForegroundColor Red
   $_.Exception
   Write-Host 'Confirm this machine can access https://github.com/microsoft/Azure-Synapse-Connectivity-Checker/' -ForegroundColor Yellow
   Write-Host 'or use a machine with Internet access to see how to run this from machines without Internet.' -ForegroundColor Yellow
     }

• Change variables
• Execute the script

Option 7 (Fabric-Windows-Offline) - Download, change parameters and run

• Open Powershell ISE
• Copy / Past content of script (Synapse-TestConnection.ps1)
• Change variables

   $FabricEndpoint = "xxxx-xxxx.datawarehouse.pbidedicated.windows.net" # Enter your Fabric SQL Endpoint
   $AADUser = "xxxx@domain.com" # AAD user that will be used to authenticate
   $DatabaseName = "master" # Enter your Database Name

• Execute the script

Contributing

This project welcomes contributions and suggestions. Most contributions require you to agree to a Contributor License Agreement (CLA) declaring that you have the right to, and actually do, grant us the rights to use your contribution. For details, visit https://cla.opensource.microsoft.com.

When you submit a pull request, a CLA bot will automatically determine whether you need to provide a CLA and decorate the PR appropriately (e.g., status check, comment). Simply follow the instructions provided by the bot. You will only need to do this once across all repos using our CLA.

This project has adopted the Microsoft Open Source Code of Conduct. For more information see the Code of Conduct FAQ or contact opencode@microsoft.com with any additional questions or comments.

Trademarks

This project may contain trademarks or logos for projects, products, or services. Authorized use of Microsoft trademarks or logos is subject to and must follow Microsoft's Trademark & Brand Guidelines. Use of Microsoft trademarks or logos in modified versions of this project must not cause confusion or imply Microsoft sponsorship. Any use of third-party trademarks or logos are subject to those third-party's policies.