How-to test TCP connectivity from a SQL Managed Instance
Published Jan 12 2022 03:49 AM 17.8K Views

This blog post is one of the 2 posts in the tiny SQL Managed Instance series on how-to determine the status of the connectivity from inside the Azure SQL Managed Instance. They will help you to determine if the SQL MI is able to reach a certain service, such as a different SQL MI or any other supported Azure service, for example.

In this post we shall focus on helping you determining the TCP connectivity from SQL Managed Instance against a given endpoint and port of your choice.


If you are interested in other posts on how-to discover different aspects of SQL MI - please visit the, which serves as a placeholder for the series.


There are scenarios where it would be nice to be able to test if a SQL Managed Instance can reach some "external" endpoints, like Azure Storage as an example.



We can use SQL Agent, available on SQL Managed Instance, to run connectivity tests from the instance itself.

The following script will create a new job on SQL Agent named TestTCPNetworkConnection and add a step to test TCP connection against an endpoint (and port) you specify.

Proper values for @endpoint and @port need to be specified.

The script will trigger the job and check status every 5 seconds until it completes.



  1. Connect to the instance.

  2. Paste the following script on a query window or use the following link to access the script - sqlmi/TestTCPNetworkConnection.sql at main · Azure/sqlmi (

    -- Parameters
    DECLARE @endpoint NVARCHAR(512) = N''
    DECLARE @port NVARCHAR(5) = N'443'
    DECLARE @jobName NVARCHAR(512) = N'TestTCPNetworkConnection', @jobId BINARY(16), @cmd NVARCHAR(MAX)
    IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = @jobName)
    EXEC msdb.dbo.sp_delete_job @job_name=@jobName, @delete_unused_schedule=1
    EXEC msdb.dbo.sp_add_job @job_name=@jobName, @enabled=1, @job_id = @jobId OUTPUT
    DECLARE @stepName NVARCHAR(512) = @endpoint + N':' + @port
    SET @cmd = (N'tnc ' + @endpoint + N' -port ' + @port +' | select ComputerName, RemoteAddress, TcpTestSucceeded | Format-List')
    EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=@stepName
    , @step_id=1, @cmdexec_success_code=0, @subsystem=N'PowerShell', @command=@cmd, @database_name=N'master'
    EXEC msdb.dbo.sp_add_jobserver @job_id = @jobId, @job_name = N'(local)'
    EXEC msdb.dbo.sp_start_job @job_name=@jobName
    --Check status every 5 seconds
    DECLARE @RunStatus INT 
    SET @RunStatus=10
    WHILE ( @RunStatus >= 4)
    SELECT distinct @RunStatus  = run_status
    FROM [msdb].[dbo].[sysjobhistory] JH JOIN [msdb].[dbo].[sysjobs] J ON JH.job_id= J.job_id 
    WHERE and step_id = 0
    WAITFOR DELAY '00:00:05'; 
    --Get logs once job completes
    SELECT [step_name] AS [Endpoint]
    ,SUBSTRING([message], CHARINDEX('TcpTestSucceeded',[message]), CHARINDEX('Process Exit',[message])-CHARINDEX('TcpTestSucceeded',[message])) as TcpTestResult
    ,SUBSTRING([message], CHARINDEX('RemoteAddress',[message]), CHARINDEX('TcpTestSucceeded',[message])-CHARINDEX('RemoteAddress',[message])) as RemoteAddressResult
    ,[run_status] ,[run_duration], [message]
    FROM [msdb].[dbo].[sysjobhistory] JH JOIN [msdb].[dbo].[sysjobs] J ON JH.job_id= J.job_id
    WHERE and step_id <> 0


  3. Set desired values for @endpoint and @port on the parameters section of the script.

  4. Run the script and check the results, you will get something like:tcp_connectivity_test_result.png


    Verify the results:

    • The outcome of each test at TcpTestSucceeded should be TcpTestSucceeded : True.
    • Check if the resolved IP Address to spot bad DNS resolution (like missing or wrong value)


Next steps

In case the test is failing (TcpTestSucceeded : False), this is usually a client-side networking issue (like DNS issue or a port being blocked) that you will need to pursue with your local network administrator.


1 Comment
Version history
Last update:
‎Sep 10 2023 04:57 AM
Updated by: