How-to test TCP connectivity from a SQL Managed Instance

Published Jan 12 2022 03:49 AM 2,249 Views
Microsoft

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  http://aka.ms/sqlmi-howto, 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.

 

Strategy

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.

 

Steps

  1. Connect to the instance.

  2. Paste the following script on a query window:

    --START
    -- Parameters
    DECLARE @endpoint NVARCHAR(512) = N'myteststorageforsqlmi.blob.core.windows.net'
    DECLARE @port NVARCHAR(5) = N'443'
    
    --Script
    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, @server_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)
    BEGIN
    SELECT distinct @RunStatus  = run_status
    FROM [msdb].[dbo].[sysjobhistory] JH JOIN [msdb].[dbo].[sysjobs] J ON JH.job_id= J.job_id 
    WHERE J.name=@jobName and step_id = 0
    WAITFOR DELAY '00:00:05'; 
    END
    
    --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 J.name=@jobName and step_id <> 0
    --END

     

  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
%3CLINGO-SUB%20id%3D%22lingo-sub-3058458%22%20slang%3D%22en-US%22%3EHow-to%20test%20TCP%20connectivity%20from%20a%20SQL%20Managed%20Instance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3058458%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EThis%20blog%20post%20is%20one%20of%20the%202%20posts%20in%20the%20tiny%20SQL%20Managed%20Instance%20series%20on%20how-to%20determine%20the%20status%20of%20the%20connectivity%26nbsp%3B%3CU%3Efrom%20inside%3C%2FU%3E%26nbsp%3Bthe%20Azure%20SQL%20Managed%20Instance.%20They%20will%20help%20you%20to%20determine%20if%20the%20SQL%20MI%20is%20able%20to%20reach%20a%20certain%20service%2C%20such%20as%20a%20different%20SQL%20MI%20or%20any%20other%20supported%20Azure%20service%2C%20for%20example.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%3CBR%20%2F%3EIn%20this%20post%20we%20shall%20focus%20on%20helping%20you%20determining%20the%20TCP%20connectivity%20from%20SQL%20Managed%20Instance%20against%20a%20given%20endpoint%20and%20port%20of%20your%20choice.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EIf%20you%20are%20interested%20in%20other%20posts%20on%20how-to%20discover%20different%20aspects%20of%20SQL%20MI%20-%20please%20visit%20the%26nbsp%3B%26nbsp%3B%3C%2FSPAN%3E%3CA%20href%3D%22http%3A%2F%2Faka.ms%2Fsqlmi-howto%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttp%3A%2F%2Faka.ms%2Fsqlmi-howto%3C%2FA%3E%3CSPAN%3E%2C%20which%20serves%20as%20a%20placeholder%20for%20the%20series.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThere%20are%20scenarios%20where%20it%20would%20be%20nice%20to%20be%20able%20to%20test%20if%20a%20SQL%20Managed%20Instance%20can%20reach%20some%20%22external%22%20endpoints%2C%20like%20Azure%20Storage%20as%20an%20example.%3C%2FP%3E%0A%3CH2%20dir%3D%22auto%22%20id%3D%22toc-hId-334771767%22%20id%3D%22toc-hId-336616946%22%20id%3D%22toc-hId-336616946%22%20id%3D%22toc-hId-336616946%22%3E%26nbsp%3B%3C%2FH2%3E%0A%3CH2%20dir%3D%22auto%22%20id%3D%22toc-hId--1472682696%22%20id%3D%22toc-hId--1470837517%22%20id%3D%22toc-hId--1470837517%22%20id%3D%22toc-hId--1470837517%22%3E%3CA%20id%3D%22user-content-strategy%22%20class%3D%22anchor%22%20href%3D%22https%3A%2F%2Fgithub.com%2FAzure%2Fsqlmi%2Fblob%2F4e911f777f47b4fca2ca860fafa201289fe29ce0%2Fhow-to%2Fhow-to-test-tcp-connection-from-mi%2FREADME.md%23strategy%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%20aria-hidden%3D%22true%22%3E%3C%2FA%3EStrategy%3C%2FH2%3E%0A%3CP%3EWe%20can%20use%20SQL%20Agent%2C%20available%20on%20SQL%20Managed%20Instance%2C%20to%20run%20connectivity%20tests%20from%20the%20instance%20itself.%3C%2FP%3E%0A%3CP%3EThe%20following%20script%20will%20create%20a%20new%20job%20on%20SQL%20Agent%20named%20TestTCPNetworkConnection%20and%20add%20a%20step%20to%20test%20TCP%20connection%20against%20an%20endpoint%20(and%20port)%20you%20specify.%3C%2FP%3E%0A%3CP%3EProper%20values%20for%20%40endpoint%20and%20%40port%20need%20to%20be%20specified.%3C%2FP%3E%0A%3CP%3EThe%20script%20will%20trigger%20the%20job%20and%20check%20status%20every%205%20seconds%20until%20it%20completes.%3C%2FP%3E%0A%3CH2%20dir%3D%22auto%22%20id%3D%22toc-hId-1014830137%22%20id%3D%22toc-hId-1016675316%22%20id%3D%22toc-hId-1016675316%22%20id%3D%22toc-hId-1016675316%22%3E%26nbsp%3B%3C%2FH2%3E%0A%3CH2%20dir%3D%22auto%22%20id%3D%22toc-hId--792624326%22%20id%3D%22toc-hId--790779147%22%20id%3D%22toc-hId--790779147%22%20id%3D%22toc-hId--790779147%22%3E%3CA%20id%3D%22user-content-steps%22%20class%3D%22anchor%22%20href%3D%22https%3A%2F%2Fgithub.com%2FAzure%2Fsqlmi%2Fblob%2F4e911f777f47b4fca2ca860fafa201289fe29ce0%2Fhow-to%2Fhow-to-test-tcp-connection-from-mi%2FREADME.md%23steps%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%20aria-hidden%3D%22true%22%3E%3C%2FA%3ESteps%3C%2FH2%3E%0A%3COL%20dir%3D%22auto%22%3E%0A%3CLI%3E%3CP%3EConnect%20to%20the%20instance.%3C%2FP%3E%0A%3C%2FLI%3E%0A%3CLI%3E%3CP%3EPaste%20the%20following%20script%20on%20a%20query%20window%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3E--START%0A--%20Parameters%0ADECLARE%20%40endpoint%20NVARCHAR(512)%20%3D%20N'myteststorageforsqlmi.blob.core.windows.net'%0ADECLARE%20%40port%20NVARCHAR(5)%20%3D%20N'443'%0A%0A--Script%0ADECLARE%20%40jobName%20NVARCHAR(512)%20%3D%20N'TestTCPNetworkConnection'%2C%20%40jobId%20BINARY(16)%2C%20%40cmd%20NVARCHAR(MAX)%0AIF%20EXISTS%20(SELECT%20job_id%20FROM%20msdb.dbo.sysjobs_view%20WHERE%20name%20%3D%20%40jobName)%0AEXEC%20msdb.dbo.sp_delete_job%20%40job_name%3D%40jobName%2C%20%40delete_unused_schedule%3D1%0AEXEC%20msdb.dbo.sp_add_job%20%40job_name%3D%40jobName%2C%20%40enabled%3D1%2C%20%40job_id%20%3D%20%40jobId%20OUTPUT%0ADECLARE%20%40stepName%20NVARCHAR(512)%20%3D%20%40endpoint%20%2B%20N'%3A'%20%2B%20%40port%0ASET%20%40cmd%20%3D%20(N'tnc%20'%20%2B%20%40endpoint%20%2B%20N'%20-port%20'%20%2B%20%40port%20%2B'%20%7C%20select%20ComputerName%2C%20RemoteAddress%2C%20TcpTestSucceeded%20%7C%20Format-List')%0AEXEC%20msdb.dbo.sp_add_jobstep%20%40job_id%3D%40jobId%2C%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425384%22%20target%3D%22_blank%22%3E%40step%3C%2FA%3E_name%3D%40stepName%0A%2C%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425384%22%20target%3D%22_blank%22%3E%40step%3C%2FA%3E_id%3D1%2C%20%40cmdexec_success_code%3D0%2C%20%40subsystem%3DN'PowerShell'%2C%20%40command%3D%40cmd%2C%20%40database_name%3DN'master'%0A%0AEXEC%20msdb.dbo.sp_add_jobserver%20%40job_id%20%3D%20%40jobId%2C%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F924444%22%20target%3D%22_blank%22%3E%40server%3C%2FA%3E_name%20%3D%20N'(local)'%0AEXEC%20msdb.dbo.sp_start_job%20%40job_name%3D%40jobName%0A%0A--Check%20status%20every%205%20seconds%0ADECLARE%20%40RunStatus%20INT%20%0ASET%20%40RunStatus%3D10%0AWHILE%20(%20%40RunStatus%20%26gt%3B%3D%204)%0ABEGIN%0ASELECT%20distinct%20%40RunStatus%20%20%3D%20run_status%0AFROM%20%5Bmsdb%5D.%5Bdbo%5D.%5Bsysjobhistory%5D%20JH%20JOIN%20%5Bmsdb%5D.%5Bdbo%5D.%5Bsysjobs%5D%20J%20ON%20JH.job_id%3D%20J.job_id%20%0AWHERE%20J.name%3D%40jobName%20and%20step_id%20%3D%200%0AWAITFOR%20DELAY%20'00%3A00%3A05'%3B%20%0AEND%0A%0A--Get%20logs%20once%20job%20completes%0ASELECT%20%5Bstep_name%5D%20AS%20%5BEndpoint%5D%0A%2CSUBSTRING(%5Bmessage%5D%2C%20CHARINDEX('TcpTestSucceeded'%2C%5Bmessage%5D)%2C%20CHARINDEX('Process%20Exit'%2C%5Bmessage%5D)-CHARINDEX('TcpTestSucceeded'%2C%5Bmessage%5D))%20as%20TcpTestResult%0A%2CSUBSTRING(%5Bmessage%5D%2C%20CHARINDEX('RemoteAddress'%2C%5Bmessage%5D)%2C%20CHARINDEX('TcpTestSucceeded'%2C%5Bmessage%5D)-CHARINDEX('RemoteAddress'%2C%5Bmessage%5D))%20as%20RemoteAddressResult%0A%2C%5Brun_status%5D%20%2C%5Brun_duration%5D%2C%20%5Bmessage%5D%0AFROM%20%5Bmsdb%5D.%5Bdbo%5D.%5Bsysjobhistory%5D%20JH%20JOIN%20%5Bmsdb%5D.%5Bdbo%5D.%5Bsysjobs%5D%20J%20ON%20JH.job_id%3D%20J.job_id%0AWHERE%20J.name%3D%40jobName%20and%20step_id%20%26lt%3B%26gt%3B%200%0A--END%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3C%2FLI%3E%0A%3CLI%3E%3CP%3ESet%20desired%20values%20for%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CEM%3E%40endpoint%3C%2FEM%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eand%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CEM%3E%40port%3C%2FEM%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eon%20the%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CEM%3Eparameters%3C%2FEM%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Esection%20of%20the%20script.%3C%2FP%3E%0A%3C%2FLI%3E%0A%3CLI%3E%3CP%3ERun%20the%20script%20and%20check%20the%20results%2C%20you%20will%20get%20something%20like%3A%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20image-alt%3D%22tcp_connectivity_test_result.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F339073iF635367CDF4ACF5F%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22tcp_connectivity_test_result.png%22%20alt%3D%22tcp_connectivity_test_result.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EVerify%20the%20results%3A%3C%2FP%3E%0A%3CUL%20dir%3D%22auto%22%3E%0A%3CLI%3EThe%20outcome%20of%20each%20test%20at%20TcpTestSucceeded%20should%20be%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CEM%3ETcpTestSucceeded%20%3A%20True%3C%2FEM%3E.%3C%2FLI%3E%0A%3CLI%3ECheck%20if%20the%20resolved%20IP%20Address%20to%20spot%20bad%20DNS%20resolution%20(like%20missing%20or%20wrong%20value)%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CH2%20dir%3D%22auto%22%20id%3D%22toc-hId-1694888507%22%20id%3D%22toc-hId-1696733686%22%20id%3D%22toc-hId-1696733686%22%20id%3D%22toc-hId-1696733686%22%3E%26nbsp%3B%3C%2FH2%3E%0A%3CH2%20dir%3D%22auto%22%20id%3D%22toc-hId--112565956%22%20id%3D%22toc-hId--110720777%22%20id%3D%22toc-hId--110720777%22%20id%3D%22toc-hId--110720777%22%3E%3CA%20id%3D%22user-content-next-steps%22%20class%3D%22anchor%22%20href%3D%22https%3A%2F%2Fgithub.com%2FAzure%2Fsqlmi%2Fblob%2F4e911f777f47b4fca2ca860fafa201289fe29ce0%2Fhow-to%2Fhow-to-test-tcp-connection-from-mi%2FREADME.md%23next-steps%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%20aria-hidden%3D%22true%22%3E%3C%2FA%3ENext%20steps%3C%2FH2%3E%0A%3CP%3EIn%20case%20the%20test%20is%20failing%20(%3CEM%3ETcpTestSucceeded%20%3A%20False%3C%2FEM%3E)%2C%20this%20is%20usually%20a%20client-side%20networking%20issue%20(like%20DNS%20issue%20or%20a%20port%20being%20blocked)%20that%20you%20will%20need%20to%20pursue%20with%20your%20local%20network%20administrator.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-3058458%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20image-alt%3D%22tcp_connectivity_test_teaser.png%22%20style%3D%22width%3A%20506px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F339045iE58B2F0656E22A83%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22tcp_connectivity_test_teaser.png%22%20alt%3D%22tcp_connectivity_test_teaser.png%22%20%2F%3E%3C%2FSPAN%3EIn%20this%20post%2C%20with%20the%20help%20of%20T-SQL%2C%20you%20can%20discover%20how-to%20determine%20the%20TCP%20connectivity%20from%20SQL%20Managed%20Instance%20against%20a%20given%20endpoint%20and%20port%20of%20your%20choice.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3370677%22%20slang%3D%22en-US%22%3ERe%3A%20How-to%20test%20TCP%20connectivity%20from%20a%20SQL%20Managed%20Instance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3370677%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Niko!%3CBR%20%2F%3E%3CBR%20%2F%3EI%20had%20to%20use%20this%20script%20today%2C%20and%20I%20noticed%20a%20small%20issue%3A%20the%20code%20as-is%20fails%20when%20running%20the%20job%20because%20no%20owner%20is%20defined%20and%20the%20instance%20complains%20about%20not%20being%20able%20to%20determine%20if%20the%20owner%20of%20the%20job%20has%20server%26nbsp%3B%20access%3C%2FP%3E%3CP%3EBy%20adding%20%22%2C%40owner_login_name%20%3D%20'sa'%20%22%20it%20runs%20fine%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E
Version history
Last update:
‎Jan 28 2022 07:04 AM
Updated by: