%3CLINGO-SUB%20id%3D%22lingo-sub-1158677%22%20slang%3D%22en-US%22%3EAzure%20SQL%20DB%20Connectivity%20Troubleshooting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1158677%22%20slang%3D%22en-US%22%3E%3CP%3EWorking%20in%20support%20we%20deal%20daily%20with%20connectivity%20issues%20to%20Azure%20SQL%20DB%2C%20find%20below%20some%20information%20to%20help%20you%20troubleshoot%20for%20connectivity%20issues.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFirst%20you%20need%20to%20understand%20the%20%3CSTRONG%3EAzure%20SQL%20DB%20connectivity%20architecture%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-connectivity-architecture%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-connectivity-architecture%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhen%20connecting%20to%20Azure%20SQL%20DB%20depending%20on%20where%20is%20the%20client%20(Azure%20%2F%20On-premises)%20and%20also%20depending%20on%20Connection%20policy%20you%20set%20you%20may%20need%20extra%20ports%20than%20default%201433%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22FonsecaSergio_0-1581095602203.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F169726i45C5410280AC3D66%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22FonsecaSergio_0-1581095602203.png%22%20alt%3D%22FonsecaSergio_0-1581095602203.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EBy%20default%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EClients%20connect%20to%20the%20gateway%2C%20that%20has%20a%20public%20IP%20addresses%20and%20listens%20on%20port%20%3CSTRONG%3E1433%3C%2FSTRONG%3E.%3CUL%3E%0A%3CLI%3EThis%20public%20IPs%20are%20a%20shared%20resource%20per%20regions%20and%20IPs%20documented%20at%26nbsp%3B%3CFONT%20size%3D%222%22%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-connectivity-architecture%23azure-sql-database-gateway-ip-addresses%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-connectivity-architecture%23azure-sql-database-gateway-ip-addresses%3C%2FA%3E%3C%2FFONT%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FLI%3E%0A%3CLI%3EThe%20gateway%2C%20depending%20on%20the%20effective%20connection%20policy%2C%20%3CSTRONG%3Eredirects%20or%20proxies%3C%2FSTRONG%3E%20the%20traffic%20to%20the%20right%20database%20cluster.%3C%2FLI%3E%0A%3CLI%3EInside%20the%20database%20cluster%20traffic%20is%20forwarded%20to%20the%20appropriate%20Azure%20SQL%20database%20that%20will%20use%20%3CSTRONG%3Eports%2011000%20-%2011999%3C%2FSTRONG%3E.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBy%20default%20the%20connection%20policy%20will%20be%20set%20as%20DEFAULT%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CEM%3E%22This%20is%20the%20connection%20policy%20in%20effect%20on%20all%20servers%20after%20creation%20unless%20you%20explicitly%20alter%20the%20connection%20policy%20to%20either%26nbsp%3B%3CCODE%3EProxy%3C%2FCODE%3E%26nbsp%3Bor%26nbsp%3B%3CCODE%3ERedirect%3C%2FCODE%3E.%20The%20default%20policy%20is%3CCODE%3ERedirect%3C%2FCODE%3E%26nbsp%3Bfor%20all%20client%20connections%20originating%20inside%20of%20Azure%20(e.g.%20from%20an%20Azure%20Virtual%20Machine)%20and%26nbsp%3B%3CCODE%3EProxy%3C%2FCODE%3Efor%20all%20client%20connections%20originating%20outside%20(e.g.%20connections%20from%20your%20local%20workstation).%22%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWith%20this%20knowledge%20we%20can%20start%20troubleshooting%2C%20but%20it%20will%20all%20depend%20on%20the%20error%20message%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20this%20test%20I%20have%20used%20a%20server%20name%20that%20I%20know%20that%20is%20correct%20from%20an%20Azure%20VM%20and%20was%20expected%20to%20work.%20But%20I%20receive%20an%20error%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3CBR%20%2F%3ECannot%20connect%20to%20SERVERNAME.database.windows.net.%3CBR%20%2F%3E%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3CBR%20%2F%3EA%20network-related%20or%20instance-specific%20error%20occurred%20while%20establishing%20a%20connection%20to%20SQL%20Server.%20%3CBR%20%2F%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3EThe%20server%20was%20not%20found%20or%20was%20not%20accessible.%3C%2FSTRONG%3E%20%3C%2FFONT%3E%3CBR%20%2F%3EVerify%20that%20the%20instance%20name%20is%20correct%20and%20that%20SQL%20Server%20is%20configured%20to%20allow%20remote%20connections.%20(provider%3A%20TCP%20Provider%2C%20error%3A%200%20-%20A%20connection%20attempt%20failed%20because%20the%20connected%20party%20did%20not%20properly%20respond%20after%20a%20period%20of%20time%2C%20or%20established%20connection%20failed%20because%20connected%20host%20has%20failed%20to%20respond.)%20(.Net%20SqlClient%20Data%20Provider)%3CBR%20%2F%3E------------------------------%3CBR%20%2F%3EError%20Number%3A%2010060%3CBR%20%2F%3ESeverity%3A%2020%3CBR%20%2F%3EState%3A%200%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20the%20server%20is%20not%20found%20we%20need%20to%20check%20if%20we%20can%20reach%20the%20%3CSTRONG%3Efirst%20point%20of%20contact%20(the%20GATEWAY)%20on%20PORT%201433.%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EPing%20is%20not%20expected%20to%20respond%2C%20so%20this%20is%20not%20a%20good%20tool%20to%20test%20it%3C%2FSTRONG%3E.%26nbsp%3BIf%20you%20test%20a%20simple%20PING%20you%20can%20check%20that%20you%20are%20resolving%20correct%20the%20gateway%20name%20to%20the%20public%20IP%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22FonsecaSergio_1-1581098074954.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F169735i06C0FB74AD0954F1%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22FonsecaSergio_1-1581098074954.png%22%20alt%3D%22FonsecaSergio_1-1581098074954.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EWe%20can%20see%20this%20server%20is%20in%20West%20Europe%20and%20this%20match%20the%20documented%20IP%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22FonsecaSergio_2-1581098118191.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F169736i2781D854DECC0D8B%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22FonsecaSergio_2-1581098118191.png%22%20alt%3D%22FonsecaSergio_2-1581098118191.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ETo%20%3CSTRONG%3Echeck%20if%20you%20can%20reach%20the%20Port%201433%3C%2FSTRONG%3E%2C%20you%20can%20use%20multiple%20tools%20like%20%3CSTRONG%3ETELNET%20%2F%20PSPING%20%2F%20POWERSHELL%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFind%20below%20a%20simple%20test%20using%20Powershell%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3ETest-NetConnection%20-Port%201433%20-ComputerName%20SERVERNAME.database.windows.net%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWe%20can%20see%20that%20I'm%20failing%20to%20reach%201433%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22FonsecaSergio_3-1581098554936.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F169737i23EB715A919AB796%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22FonsecaSergio_3-1581098554936.png%22%20alt%3D%22FonsecaSergio_3-1581098554936.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20now%20will%20need%20to%20open%20firewall%20in%20the%20path%20from%20Server%20to%20Azure%20SQL%20DB%2C%20like%20Windows%20Firewall%2C%20Corporate%20Firewall%20and%2For%20Azure%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fvirtual-network%2Fsecurity-overview%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3ENSG%20(Network%20security%20group)%3C%2FA%3E%20to%20allow%20this%20communication.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20this%20case%20for%20Azure%20VM%20will%20open%20NSG%2C%20and%20you%20can%20use%20%3CA%20title%3D%22Service%20Tags%22%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fvirtual-network%2Fservice-tags-overview%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3E%3CSTRONG%3EService%20Tags%3C%2FSTRONG%3E%3C%2FA%3E%20to%20simplify%20this%20configuration.%20With%20Service%20TAGs%20you%20do%20not%20need%20to%20hard%20code%20the%20SQL%20Gateway%20IPs.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECheck%20the%20%3CSTRONG%3EOutbound%20security%20rules%3C%2FSTRONG%3E%20and%20add%20one%20to%20open%20port%20%3CSTRONG%3E1433%3C%2FSTRONG%3E%20to%20Service%20Tag%20%3CSTRONG%3ESQL.WestEurope%20(Only%20Azure%20SQL%20DBs%20located%20in%20West%20Europe)%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22FonsecaSergio_0-1582213573695.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F172246i07D1204BD793E6E6%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22FonsecaSergio_0-1582213573695.png%22%20alt%3D%22FonsecaSergio_0-1582213573695.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETesting%20again%20we%20receive%20error%20below%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3CBR%20%2F%3ECannot%20connect%20to%20SERVERNAME.database.windows.net.%3CBR%20%2F%3E%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3CBR%20%2F%3EA%20network-related%20or%20instance-specific%20error%20occurred%20while%20establishing%20a%20connection%20to%20SQL%20Server.%20%3CBR%20%2F%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3EThe%20server%20was%20not%20found%20or%20was%20not%20accessible.%3C%2FSTRONG%3E%20%3C%2FFONT%3E%3CBR%20%2F%3EVerify%20that%20the%20instance%20name%20is%20correct%20and%20that%20SQL%20Server%20is%20configured%20to%20allow%20remote%20connections.%20(provider%3A%20TCP%20Provider%2C%20error%3A%200%20-%20A%20connection%20attempt%20failed%20because%20the%20connected%20party%20did%20not%20properly%20respond%20after%20a%20period%20of%20time%2C%20or%20established%20connection%20failed%20because%20connected%20host%20has%20failed%20to%20respond.)%20(.Net%20SqlClient%20Data%20Provider)%3CBR%20%2F%3E------------------------------%3CBR%20%2F%3EError%20Number%3A%2010060%3CBR%20%2F%3ESeverity%3A%2020%3CBR%20%2F%3EState%3A%200%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20error%20looks%20similar%20with%20the%20port%201433%20closed.%20But%20in%20this%20case%20%3CFONT%20color%3D%22%23FF0000%22%3Ewe%20could%20reach%201433%20(Gateway)%20but%20you%20%3CSTRONG%3Ecould%20not%20reach%20the%20SQL%20Server%20host%20that%20the%20gateway%20REDIRECTED%20your%20connection%20to%3C%2FSTRONG%3E%20on%20some%20port%20from%2011000-11999%2C%20and%20you%20still%20did%20not%20open%20it%20yet%20in%20the%20NSG%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20also%20%3CSTRONG%3Echange%20the%20connection%20policy%20to%20proxy%3C%2FSTRONG%3E%2C%20this%20simplify%20the%20ports%20usage%20but%20will%20be%20%3CSTRONG%3Eslower%20than%20redirect%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-connectivity-architecture%23script-to-change-connection-settings-via-powershell%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-connectivity-architecture%23script-to-change-connection-settings-via-powershell%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20help%20troubleshooting%20we%20created%20a%20Powershell%20script%20to%20help%20this%20kind%20of%20scenario.%20Just%20open%20a%20Powershell%20ISE%20and%20execute%20the%20following%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E%3CSPAN%20class%3D%22pl-smi%22%3E%24parameters%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22pl-k%22%3E%3D%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22pl-k%22%3E%40%3C%2FSPAN%3E%7B%0A%20%20%20%20%3CSPAN%20class%3D%22pl-smi%22%3EServer%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22pl-k%22%3E%3D%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22pl-s%22%3E%3CSPAN%20class%3D%22pl-pds%22%3E'%3C%2FSPAN%3E.database.windows.net%3CSPAN%20class%3D%22pl-pds%22%3E'%3C%2FSPAN%3E%3C%2FSPAN%3E%0A%20%20%20%20%3CSPAN%20class%3D%22pl-smi%22%3EDatabase%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22pl-k%22%3E%3D%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22pl-s%22%3E%3CSPAN%20class%3D%22pl-pds%22%3E'%3C%2FSPAN%3E%3CSPAN%20class%3D%22pl-pds%22%3E'%3C%2FSPAN%3E%3C%2FSPAN%3E%20%20%3CSPAN%20class%3D%22pl-c%22%3E%23%20Set%20the%20name%20of%20the%20database%20you%20wish%20to%20test%2C%20'master'%20will%20be%20used%20by%20default%20if%20nothing%20is%20set%3C%2FSPAN%3E%0A%20%20%20%20%3CSPAN%20class%3D%22pl-smi%22%3EUser%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22pl-k%22%3E%3D%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22pl-s%22%3E%3CSPAN%20class%3D%22pl-pds%22%3E'%3C%2FSPAN%3E%3CSPAN%20class%3D%22pl-pds%22%3E'%3C%2FSPAN%3E%3C%2FSPAN%3E%20%20%3CSPAN%20class%3D%22pl-c%22%3E%23%20Set%20the%20login%20username%20you%20wish%20to%20use%2C%20'AzSQLConnCheckerUser'%20will%20be%20used%20by%20default%20if%20nothing%20is%20set%3C%2FSPAN%3E%0A%20%20%20%20%3CSPAN%20class%3D%22pl-smi%22%3EPassword%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22pl-k%22%3E%3D%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22pl-s%22%3E%3CSPAN%20class%3D%22pl-pds%22%3E'%3C%2FSPAN%3E%3CSPAN%20class%3D%22pl-pds%22%3E'%3C%2FSPAN%3E%3C%2FSPAN%3E%20%20%3CSPAN%20class%3D%22pl-c%22%3E%23%20Set%20the%20login%20password%20you%20wish%20to%20use%2C%20'AzSQLConnCheckerPassword'%20will%20be%20used%20by%20default%20if%20nothing%20is%20set%3C%2FSPAN%3E%0A%0A%20%20%20%20%3CSPAN%20class%3D%22pl-c%22%3E%23%23%20Optional%20parameters%20(default%20values%20will%20be%20used%20if%20ommited)%3C%2FSPAN%3E%0A%20%20%20%20%3CSPAN%20class%3D%22pl-smi%22%3ESendAnonymousUsageData%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22pl-k%22%3E%3D%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22pl-c1%22%3E%24true%3C%2FSPAN%3E%20%20%3CSPAN%20class%3D%22pl-c%22%3E%23%20Set%20as%20%24true%20(default)%20or%20%24false%3C%2FSPAN%3E%0A%20%20%20%20%3CSPAN%20class%3D%22pl-smi%22%3ERunAdvancedConnectivityPolicyTests%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22pl-k%22%3E%3D%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22pl-c1%22%3E%24true%3C%2FSPAN%3E%20%20%3CSPAN%20class%3D%22pl-c%22%3E%23%20Set%20as%20%24true%20(default)%20or%20%24false%2C%20this%20will%20download%20the%20library%20needed%20for%20running%20advanced%20connectivity%20tests%3C%2FSPAN%3E%0A%20%20%20%20%3CSPAN%20class%3D%22pl-smi%22%3ECollectNetworkTrace%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22pl-k%22%3E%3D%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22pl-c1%22%3E%24true%3C%2FSPAN%3E%20%20%3CSPAN%20class%3D%22pl-c%22%3E%23%20Set%20as%20%24true%20(default)%20or%20%24false%3C%2FSPAN%3E%0A%20%20%20%20%3CSPAN%20class%3D%22pl-c%22%3E%23EncryptionProtocol%20%3D%20''%20%23%20Supported%20values%3A%20'Tls%201.0'%2C%20'Tls%201.1'%2C%20'Tls%201.2'%3B%20Without%20this%20parameter%20operating%20system%20will%20choose%20the%20best%20protocol%20to%20use%3C%2FSPAN%3E%0A%7D%0A%0A%3CSPAN%20class%3D%22pl-c1%22%3E%24ProgressPreference%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22pl-k%22%3E%3D%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22pl-s%22%3E%3CSPAN%20class%3D%22pl-pds%22%3E%22%3C%2FSPAN%3ESilentlyContinue%3CSPAN%20class%3D%22pl-pds%22%3E%22%3C%2FSPAN%3E%3C%2FSPAN%3E%3B%0A%3CSPAN%20class%3D%22pl-smi%22%3E%24scriptUrlBase%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22pl-k%22%3E%3D%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22pl-s%22%3E%3CSPAN%20class%3D%22pl-pds%22%3E'%3C%2FSPAN%3Eraw.githubusercontent.com%2FAzure%2FSQL-Connectivity-Checker%2Fmaster%3CSPAN%20class%3D%22pl-pds%22%3E'%3C%2FSPAN%3E%3C%2FSPAN%3E%0A%3CSPAN%20class%3D%22pl-c1%22%3EInvoke-Command%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22pl-k%22%3E-%3C%2FSPAN%3EScriptBlock%20(%5B%3CSPAN%20class%3D%22pl-k%22%3EScriptblock%3C%2FSPAN%3E%5D%3A%3ACreate((iwr%20(%3CSPAN%20class%3D%22pl-smi%22%3E%24scriptUrlBase%3C%2FSPAN%3E%3CSPAN%20class%3D%22pl-k%22%3E%2B%3C%2FSPAN%3E%3CSPAN%20class%3D%22pl-s%22%3E%3CSPAN%20class%3D%22pl-pds%22%3E'%3C%2FSPAN%3E%2FAzureSQLConnectivityChecker.ps1%3CSPAN%20class%3D%22pl-pds%22%3E'%3C%2FSPAN%3E%3C%2FSPAN%3E)).Content))%20%3CSPAN%20class%3D%22pl-k%22%3E-%3C%2FSPAN%3EArgumentList%20%3CSPAN%20class%3D%22pl-smi%22%3E%24parameters%3C%2FSPAN%3E%0A%3CSPAN%20class%3D%22pl-c%22%3E%23end%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3E*Last%20Version%20at%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fgithub.com%2FAzure%2FSQL-Connectivity-Checker%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fgithub.com%2FAzure%2FSQL-Connectivity-Checker%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnd%20using%20this%20script%20we%20can%20see%20that%20we%20can%20reach%20gateways%20on%20port%201433%2C%20but%20could%20not%20reach%20the%20SQL%20Host%20on%20ports%2011000%20-%2011999%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22FonsecaSergio_0-1583170272836.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F174610i607AAFF71EF70AC2%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22FonsecaSergio_0-1583170272836.png%22%20alt%3D%22FonsecaSergio_0-1583170272836.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENow%20opened%20ports%201433%20%2B%2011XXX%2C%20you%20may%20also%20want%20to%20add%201434%2B14XXX%20to%20use%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Fdatabase-engine%2Fconfigure-windows%2Fdiagnostic-connection-for-database-administrators%3Fview%3Dsql-server-ver15%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3EDAC%20connection%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22FonsecaSergio_1-1583170443361.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F174611iA6B103C3982C3A3C%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22FonsecaSergio_1-1583170443361.png%22%20alt%3D%22FonsecaSergio_1-1583170443361.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWe%20can%20see%20the%20ports%20are%20now%20open%20where%20the%20script%20will%20test%20some%20random%20ports%20(Not%20exactly%20yours)%20and%20you%20should%20see%20most%20of%20them%20succeeding.%20I%20will%20also%20check%20other%20ports%20for%20AAD%20auth%2C%20etc.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHowever%20there%20are%20still%20some%20error%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22FonsecaSergio_1-1583255771075.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F174737i6A77F6C452234799%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22FonsecaSergio_1-1583255771075.png%22%20alt%3D%22FonsecaSergio_1-1583255771075.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETesting%20on%20SSMS%20we%20can%20error%20below%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3CBR%20%2F%3ECannot%20connect%20to%20SERVERNAME.database.windows.net.%3CBR%20%2F%3E%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3CBR%20%2F%3ECannot%20open%20server%20'SERVERNAME'%20requested%20by%20the%20login.%20%3CBR%20%2F%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3EClient%20is%20not%20allowed%20to%20access%20the%20server%3C%2FSTRONG%3E%3C%2FFONT%3E.%20(.Net%20SqlClient%20Data%20Provider)%3CBR%20%2F%3E------------------------------%3CBR%20%2F%3EServer%20Name%3A%20fb869e9ddbfc.tr4030.westeurope1-a.worker.database.windows.net%2C11007%3CBR%20%2F%3EError%20Number%3A%2040914%3CBR%20%2F%3ESeverity%3A%2014%3CBR%20%2F%3EState%3A%201%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20the%20outbound%20ports%20are%20open%20the%20problem%20now%20is%20at%20inbound%20side%20of%20Azure%20SQL%20DB%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20use%20some%20procedures%20to%20open%20Azure%20SQL%20DB%20firewall%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CSTRONG%3EIP%20firewall%20rule%3C%2FSTRONG%3E%20(Great%20for%20external%20resources)%0A%3CUL%3E%0A%3CLI%3E%3CA%20style%3D%22font-family%3A%20inherit%3B%20background-color%3A%20%23ffffff%3B%22%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-firewall-configure%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-firewall-configure%3C%2FA%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FLI%3E%0A%3CLI%3E%3CSTRONG%3EVNET%20Endpoints%3C%2FSTRONG%3E%20(Great%20for%20Azure%20resources%20that%20are%20VNET%20integrated)%0A%3CUL%3E%0A%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-vnet-service-endpoint-rule-overview%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-vnet-service-endpoint-rule-overview%3C%2FA%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FLI%3E%0A%3CLI%3E%3CSTRONG%3EPrivate%20Endpoint%20%3C%2FSTRONG%3E(Great%20for%20environment%20with%20more%20restrictions)%0A%3CUL%3E%0A%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-private-endpoint-overview%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-private-endpoint-overview%3C%2FA%3E%3C%2FLI%3E%0A%3CLI%3EMore%20info%20at%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-database-support-blog%2Fazure-sql-db-private-link-private-endpoint-connectivity%2Fba-p%2F1235573%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-database-support-blog%2Fazure-sql-db-private-link-private-endpoint-connectivity%2Fba-p%2F1235573%3C%2FA%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CDIV%20id%3D%22tinyMceEditorFonsecaSergio_2%22%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%20id%3D%22tinyMceEditorFonsecaSergio_4%22%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%20id%3D%22tinyMceEditorFonsecaSergio_3%22%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3EFor%20this%20test%20used%26nbsp%3B%3CSTRONG%3EVNET%20Endpoints%3C%2FSTRONG%3E%26nbsp%3Bas%20my%20client%20was%20an%20Azure%20VM%20and%20after%20enabled%20the%20connection%20was%20ok.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E*Update%202020-03-26%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EVNET%20endpoint%20only%20works%20for%20Azure%20Resources%20and%20its%20more%20secure%20because%20all%20traffic%20flows%20inside%20Azure%20Backbone.%20And%20its%20simpler%20because%20you%20just%20need%20to%20open%20connections%20from%20clients%20coming%20from%20specific%20subnet%2C%20instead%20of%20using%20IP%20range.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%222020-03-26%2015_09_19-VNet%20Service%20Endpoints%20for%20Azure%20SQL%20Database%20now%20generally%20available%20_%20Blogue%20e.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F179728i606D8960AF0440C7%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%222020-03-26%2015_09_19-VNet%20Service%20Endpoints%20for%20Azure%20SQL%20Database%20now%20generally%20available%20_%20Blogue%20e.png%22%20alt%3D%222020-03-26%2015_09_19-VNet%20Service%20Endpoints%20for%20Azure%20SQL%20Database%20now%20generally%20available%20_%20Blogue%20e.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E*OnPrem%20resources%20still%20going%20to%20use%20Public%20endpoint%20and%20the%20company%20public%20oubound%20IP%20range%26nbsp%3Bneed%20to%20be%20added%20to%20IP%20firewall%20rules%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEven%20though%20you%20are%20going%20through%20private%20network%2C%20For%20VNET%20endpoint%20you%20need%20to%20keep%20%22%3CSTRONG%3EDeny%20public%20network%20access%3C%2FSTRONG%3E%22%20as%20NO%20(Check%20image%20below).%20This%20option%20is%20only%20used%20for%20private%20link%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20make%20this%20connection%20flow%20using%20Azure%20Backbone%20you%20will%20need%20to%20%3CSTRONG%3Eenable%20service%20endpoint%20in%20the%20client%20subnet%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%222020-03-26%2015_22_04-Clipboard.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F179742i2F8FBC91334A1A10%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%222020-03-26%2015_22_04-Clipboard.png%22%20alt%3D%222020-03-26%2015_22_04-Clipboard.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnd%20you%20can%20check%20status%20of%20the%20endpoint%20in%20the%20SQL%20firewall%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%222020-03-26%2015_13_29-Clipboard.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F179740i535EAC991ED0776A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%222020-03-26%2015_13_29-Clipboard.png%22%20alt%3D%222020-03-26%2015_13_29-Clipboard.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20full%20procedure%20on%20enabling%20VNET%20Endpoint%20check%20documentation%20below%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-vnet-service-endpoint-rule-overview%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%3CFONT%20size%3D%222%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-vnet-service-endpoint-rule-overview%3C%2FFONT%3E%3C%2FA%3E%3C%2FLI%3E%0A%3CLI%3E%3CFONT%20size%3D%222%22%3E%3CA%20href%3D%22https%3A%2F%2Fazure.microsoft.com%2Fpt-pt%2Fblog%2Fvnet-service-endpoints-for-azure-sql-database-now-generally-available%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fazure.microsoft.com%2Fpt-pt%2Fblog%2Fvnet-service-endpoints-for-azure-sql-database-now-generally-available%2F%3C%2FA%3E%3C%2FFONT%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPS%3A%20%3CSTRONG%3EAzure%20Portal%20Query%20Editor%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EWhen%20connecting%20using%20query%20editor%20at%20Azure%20Portal%20you%20are%20going%20use%20ports%20different%20than%20default%201433%20as%20documented%20at%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-connect-query-portal%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-connect-query-portal%3C%2FA%3E%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%22%3CEM%3EThe%20query%20editor%20uses%20%3CSTRONG%3Eports%20443%20and%201443%3C%2FSTRONG%3E%20to%20communicate.%20Please%20ensure%20you%20have%20enabled%20outbound%20HTTPS%20traffic%20on%20these%20ports.%20You%20will%20also%20need%20to%20add%20your%20outbound%20IP%20address%20to%20the%20server's%20allowed%20firewall%20rules%20to%20access%20your%20databases%20and%20data%20warehouses.%3C%2FEM%3E%22%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Microsoft

Working in support we deal daily with connectivity issues to Azure SQL DB, find below some information to help you troubleshoot for connectivity issues.

 

First you need to understand the Azure SQL DB connectivity architecture

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-connectivity-architecture

 

When connecting to Azure SQL DB depending on where is the client (Azure / On-premises) and also depending on Connection policy you set you may need extra ports than default 1433

FonsecaSergio_0-1581095602203.png

By default

 

By default the connection policy will be set as DEFAULT

 

"This is the connection policy in effect on all servers after creation unless you explicitly alter the connection policy to either Proxy or Redirect. The default policy isRedirect for all client connections originating inside of Azure (e.g. from an Azure Virtual Machine) and Proxyfor all client connections originating outside (e.g. connections from your local workstation)."

 

With this knowledge we can start troubleshooting, but it will all depend on the error message

 

For this test I have used a server name that I know that is correct from an Azure VM and was expected to work. But I receive an error

 

===================================
Cannot connect to SERVERNAME.database.windows.net.
===================================
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 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) (.Net SqlClient Data Provider)
------------------------------
Error Number: 10060
Severity: 20
State: 0

 

If the server is not found we need to check if we can reach the first point of contact (the GATEWAY) on PORT 1433.

 

Ping is not expected to respond, so this is not a good tool to test it. If you test a simple PING you can check that you are resolving correct the gateway name to the public IP

FonsecaSergio_1-1581098074954.png

We can see this server is in West Europe and this match the documented IP

FonsecaSergio_2-1581098118191.png

To check if you can reach the Port 1433, you can use multiple tools like TELNET / PSPING / POWERSHELL

 

Find below a simple test using Powershell

 

Test-NetConnection -Port 1433 -ComputerName SERVERNAME.database.windows.net

 

We can see that I'm failing to reach 1433

FonsecaSergio_3-1581098554936.png

 

You now will need to open firewall in the path from Server to Azure SQL DB, like Windows Firewall, Corporate Firewall and/or Azure NSG (Network security group) to allow this communication.

 

In this case for Azure VM will open NSG, and you can use Service Tags to simplify this configuration. With Service TAGs you do not need to hard code the SQL Gateway IPs.

 

Check the Outbound security rules and add one to open port 1433 to Service Tag SQL.WestEurope (Only Azure SQL DBs located in West Europe)

 

FonsecaSergio_0-1582213573695.png

 

 

Testing again we receive error below

 

===================================
Cannot connect to SERVERNAME.database.windows.net.
===================================
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 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) (.Net SqlClient Data Provider)
------------------------------
Error Number: 10060
Severity: 20
State: 0

 

The error looks similar with the port 1433 closed. But in this case we could reach 1433 (Gateway) but you could not reach the SQL Server host that the gateway REDIRECTED your connection to on some port from 11000-11999, and you still did not open it yet in the NSG

 

You can also change the connection policy to proxy, this simplify the ports usage but will be slower than redirect

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-connectivity-architecture#script-to...

 

 

To help troubleshooting we created a Powershell script to help this kind of scenario. Just open a Powershell ISE and execute the following

 

$parameters = @{
    Server = '.database.windows.net'
    Database = ''  # Set the name of the database you wish to test, 'master' will be used by default if nothing is set
    User = ''  # Set the login username you wish to use, 'AzSQLConnCheckerUser' will be used by default if nothing is set
    Password = ''  # Set the login password you wish to use, 'AzSQLConnCheckerPassword' will be used by default if nothing is set

    ## Optional parameters (default values will be used if ommited)
    SendAnonymousUsageData = $true  # Set as $true (default) or $false
    RunAdvancedConnectivityPolicyTests = $true  # Set as $true (default) or $false, this will download the library needed for running advanced connectivity tests
    CollectNetworkTrace = $true  # Set as $true (default) or $false
    #EncryptionProtocol = '' # Supported values: 'Tls 1.0', 'Tls 1.1', 'Tls 1.2'; Without this parameter operating system will choose the best protocol to use
}

$ProgressPreference = "SilentlyContinue";
$scriptUrlBase = 'raw.githubusercontent.com/Azure/SQL-Connectivity-Checker/master'
Invoke-Command -ScriptBlock ([Scriptblock]::Create((iwr ($scriptUrlBase+'/AzureSQLConnectivityChecker.ps1')).Content)) -ArgumentList $parameters
#end

*Last Version at https://github.com/Azure/SQL-Connectivity-Checker

 

And using this script we can see that we can reach gateways on port 1433, but could not reach the SQL Host on ports 11000 - 11999

FonsecaSergio_0-1583170272836.png

 

Now opened ports 1433 + 11XXX, you may also want to add 1434+14XXX to use DAC connection

FonsecaSergio_1-1583170443361.png

 

We can see the ports are now open where the script will test some random ports (Not exactly yours) and you should see most of them succeeding. I will also check other ports for AAD auth, etc.

 

However there are still some error

FonsecaSergio_1-1583255771075.png

 

Testing on SSMS we can error below

 

===================================
Cannot connect to SERVERNAME.database.windows.net.
===================================
Cannot open server 'SERVERNAME' requested by the login.
Client is not allowed to access the server. (.Net SqlClient Data Provider)
------------------------------
Server Name: fb869e9ddbfc.tr4030.westeurope1-a.worker.database.windows.net,11007
Error Number: 40914
Severity: 14
State: 1

 

If the outbound ports are open the problem now is at inbound side of Azure SQL DB

 

You can use some procedures to open Azure SQL DB firewall:

 
 
 

For this test used VNET Endpoints as my client was an Azure VM and after enabled the connection was ok.

 

*Update 2020-03-26

 

VNET endpoint only works for Azure Resources and its more secure because all traffic flows inside Azure Backbone. And its simpler because you just need to open connections from clients coming from specific subnet, instead of using IP range.

 

2020-03-26 15_09_19-VNet Service Endpoints for Azure SQL Database now generally available _ Blogue e.png

 

*OnPrem resources still going to use Public endpoint and the company public oubound IP range need to be added to IP firewall rules

 

 

Even though you are going through private network, For VNET endpoint you need to keep "Deny public network access" as NO (Check image below). This option is only used for private link

 

To make this connection flow using Azure Backbone you will need to enable service endpoint in the client subnet

 

2020-03-26 15_22_04-Clipboard.png

 

And you can check status of the endpoint in the SQL firewall 

2020-03-26 15_13_29-Clipboard.png

 

For full procedure on enabling VNET Endpoint check documentation below

 

 

PS: Azure Portal Query Editor

When connecting using query editor at Azure Portal you are going use ports different than default 1433 as documented at https://docs.microsoft.com/en-us/azure/sql-database/sql-database-connect-query-portal

"The query editor uses ports 443 and 1443 to communicate. Please ensure you have enabled outbound HTTPS traffic on these ports. You will also need to add your outbound IP address to the server's allowed firewall rules to access your databases and data warehouses."