-
Notifications
You must be signed in to change notification settings - Fork 38
/
AzureSQLConnectivityChecker.ps1
1805 lines (1583 loc) · 106 KB
/
AzureSQLConnectivityChecker.ps1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
## Copyright (c) Microsoft Corporation.
#Licensed under the MIT license.
#Azure SQL Connectivity Checker
#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.
using namespace System
using namespace System.Net
using namespace System.net.Sockets
using namespace System.Collections.Generic
using namespace System.Diagnostics
using namespace System.Diagnostics.Tracing
using namespace System.Data.SqlClient
# Parameter region for when script is run directly
# Supports Single, Elastic Pools and Managed Instance (please provide FQDN, MI public endpoint is supported)
# Supports Azure Synapse / Azure SQL Data Warehouse (*.sql.azuresynapse.net / *.database.windows.net)
# Supports Public Cloud (*.database.windows.net), Azure China (*.database.chinacloudapi.cn), Azure Germany (*.database.cloudapi.de) and Azure Government (*.database.usgovcloudapi.net)
# Set the type of authentication you wish to use:
# 'SQL Server Authentication' (default),
# 'Active Directory Password', (supported only with MSAL)
# 'Active Directory Integrated',
# 'Active Directory Interactive',
# 'Active Directory Managed Identity' ('Active Directory MSI') NOTE: Managed Identity authentication works only when your application is running as an Azure resource, not with your personal account
$AuthenticationType = ''
# Set the authentication library you wish to use: 'ADAL' or 'MSAL'. Default is 'ADAL'.
$AuthenticationLibrary = ''
$Server = '.database.windows.net' # or any other supported FQDN
$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
$UserAssignedIdentityClientId = '' # Set the Client ID of the User Assigned Identity you wish to use, if nothing is set, the script will use the system-assigned identity
# In case you want to hide the password (like during a remote session), uncomment the 2 lines below (by removing leading #) and password will be asked during execution
# $Credentials = Get-Credential -Message "Credentials to test connections to the database (optional)" -User $User
# $Password = $Credentials.GetNetworkCredential().password
# Optional parameters (default values will be used if omitted)
$SendAnonymousUsageData = $true # Set as $true (default) or $false
$RunAdvancedConnectivityPolicyTests = $true # Set as $true (default) or $false#Set as $true (default) or $false, this will download library needed for running advanced connectivity policy tests
$ConnectionAttempts = 1
$DelayBetweenConnections = 1
$CollectNetworkTrace = $true # Set as $true (default) or $false
$EncryptionProtocol = 'Tls 1.2' # Supported values: 'Tls 1.0', 'Tls 1.1', 'Tls 1.2'; Without this parameter operating system will choose the best protocol to use
# Parameter region when Invoke-Command -ScriptBlock is used
$parameters = $args[0]
if ($null -ne $parameters) {
$AuthenticationType = $parameters['AuthenticationType']
$AuthenticationLibrary = $parameters['AuthenticationLibrary']
$Server = $parameters['Server']
$Database = $parameters['Database']
$User = $parameters['User']
$Password = $parameters['Password']
if ($null -ne $parameters['SendAnonymousUsageData']) {
$SendAnonymousUsageData = $parameters['SendAnonymousUsageData']
}
if ($null -ne $parameters['RunAdvancedConnectivityPolicyTests']) {
$RunAdvancedConnectivityPolicyTests = $parameters['RunAdvancedConnectivityPolicyTests']
}
if ($null -ne $parameters['CollectNetworkTrace']) {
$CollectNetworkTrace = $parameters['CollectNetworkTrace']
}
if ($null -ne $parameters['EncryptionProtocol']) {
$EncryptionProtocol = $parameters['EncryptionProtocol']
}
if ($null -ne $parameters['Local']) {
$Local = $parameters['Local']
}
if ($null -ne $parameters['LocalPath']) {
$LocalPath = $parameters['LocalPath']
}
if ($null -ne $parameters['RepositoryBranch']) {
$RepositoryBranch = $parameters['RepositoryBranch']
}
if ($null -ne $parameters['ConnectionAttempts']) {
$ConnectionAttempts = $parameters['ConnectionAttempts']
}
if ($null -ne $parameters['DelayBetweenConnections']) {
$DelayBetweenConnections = $parameters['DelayBetweenConnections']
}
}
# Setting default parameters if not provided
if ($null -eq $AuthenticationType -or '' -eq $AuthenticationType) {
$AuthenticationType = 'SQL Server Authentication'
$AuthenticationLibrary = ''
}
if ($null -eq $AuthenticationLibrary -or '' -eq $AuthenticationLibrary) {
$AuthenticationLibrary = 'MSAL'
}
if (($null -eq $User -or '' -eq $User) -and $AuthenticationType -ne "Active Directory Integrated") {
$User = 'AzSQLConnCheckerUser'
}
if ($null -eq $Password -or '' -eq $Password) {
$Password = 'AzSQLConnCheckerPassword'
}
if ($null -eq $Database -or '' -eq $Database) {
$Database = 'master'
}
if ($null -eq $Local) {
$Local = $false
}
if ($null -eq $RepositoryBranch) {
$RepositoryBranch = 'master'
}
$CustomerRunningInElevatedMode = $false
if ($PSVersionTable.Platform -eq 'Unix') {
if ((id -u) -eq 0) {
$CustomerRunningInElevatedMode = $true
}
}
else {
$currentPrincipal = New-Object Security.Principal.WindowsPrincipal([Security.Principal.WindowsIdentity]::GetCurrent())
if ($currentPrincipal.IsInRole([Security.Principal.WindowsBuiltInRole]::Administrator)) {
$CustomerRunningInElevatedMode = $true
}
}
$SQLDBGateways = @(
New-Object PSObject -Property @{Region = "Australia Central"; Gateways = ("20.36.104.6", "20.36.104.7", "20.36.105.33", "20.36.105.34"); TRs = ('tr1', 'tr3', 'tr27', 'tr136'); Cluster = 'australiacentral1-a.worker.database.windows.net'; }
New-Object PSObject -Property @{Region = "Australia Central2"; Gateways = ("20.36.112.6", "20.36.113.33", "20.36.113.34"); TRs = ('tr21', 'tr51'); Cluster = 'australiacentral2-a.worker.database.windows.net'; }
New-Object PSObject -Property @{Region = "Australia East"; Gateways = ("40.79.161.1", "13.70.112.9", "20.53.46.128", "20.53.46.129"); TRs = ('tr39', 'tr2000', 'tr2215', 'tr3259'); Cluster = 'australiaeast1-a.worker.database.windows.net'; }
New-Object PSObject -Property @{Region = "Australia South East"; Gateways = ("13.77.48.10", "13.77.49.32", "13.77.49.34", "13.77.49.35"); TRs = ('tr3', 'tr4', 'tr70', 'tr373'); Cluster = 'australiasoutheast1-a.worker.database.windows.net'; }
New-Object PSObject -Property @{Region = "Brazil South"; Gateways = ("191.233.200.14", "191.234.144.16", "191.234.152.3"); TRs = ('tr85', 'tr272', 'tr323', 'tr435'); Cluster = 'brazilsouth1-a.worker.database.windows.net'; }
New-Object PSObject -Property @{Region = "Canada Central"; Gateways = ("52.246.152.0", "20.38.144.1", "13.71.168.33"); TRs = ('tr1044', 'tr2061', 'tr2099', 'tr2320'); Cluster = 'canadacentral1-a.worker.database.windows.net'; }
New-Object PSObject -Property @{Region = "Canada East"; Gateways = ("40.69.105.9", "40.69.105.10", "40.69.105.33", "40.69.105.34"); TRs = ('tr11', 'tr210', 'tr211', 'tr290'); Cluster = 'canadaeast1-a.worker.database.windows.net'; }
New-Object PSObject -Property @{Region = "Central US"; Gateways = ("104.208.21.1", "13.89.169.20", "20.40.228.128", "20.40.228.129"); TRs = ('tr8', 'tr9', 'tr11', 'tr15'); Cluster = 'centralus1-a.worker.database.windows.net'; }
New-Object PSObject -Property @{Region = "China East"; Gateways = ("52.130.112.139"); TRs = ('tr2', 'tr3'); Cluster = 'chinaeast1-a.worker.database.chinacloudapi.cn'; }
New-Object PSObject -Property @{Region = "China East 2"; Gateways = ("40.73.82.1"); TRs = ('tr1', 'tr5', 'tr11'); Cluster = 'chinaeast2-a.worker.database.chinacloudapi.cn'; }
New-Object PSObject -Property @{Region = "China North"; Gateways = ("52.130.128.89"); TRs = ('tr2', 'tr3'); Cluster = 'chinanorth1-a.worker.database.chinacloudapi.cn'; }
New-Object PSObject -Property @{Region = "China North 2"; Gateways = ("40.73.50.0", "52.130.128.89"); TRs = ('tr1', 'tr67', 'tr119'); Cluster = 'chinanorth2-a.worker.database.chinacloudapi.cn'; }
New-Object PSObject -Property @{Region = "East Asia"; Gateways = ("13.75.32.4", "13.75.32.14", "20.205.77.200", "20.205.83.224"); TRs = ('tr220', 'tr832', 'tr850', 'tr1038'); Cluster = 'eastasia1-a.worker.database.windows.net'; }
New-Object PSObject -Property @{Region = "East US"; Gateways = ("40.121.158.30", "40.79.153.12", "40.78.225.32", "20.62.132.162", "20.62.132.163", "20.62.132.164", "20.62.132.165"); TRs = ('tr20', 'tr21', 'tr22', 'tr29'); Cluster = 'eastus1-a.worker.database.windows.net'; }
New-Object PSObject -Property @{Region = "East US 2"; Gateways = ("104.208.150.3", "40.70.144.193", "20.62.58.128"); TRs = ('tr12332', 'tr12219', 'tr5548', 'tr12110'); Cluster = 'eastus2-a.worker.database.windows.net'; }
New-Object PSObject -Property @{Region = "France Central"; Gateways = ("40.79.129.1", "40.79.137.8", "40.79.145.12"); TRs = ('tr1', 'tr4', 'tr307', 'tr390'); Cluster = 'francecentral1-a.worker.database.windows.net'; }
New-Object PSObject -Property @{Region = "France South"; Gateways = ("40.79.177.10", "40.79.177.12", "40.79.176.41", "40.79.176.42"); TRs = ('tr3', 'tr4', 'tr35', 'tr37'); Cluster = 'francesouth1-a.worker.database.windows.net'; }
New-Object PSObject -Property @{Region = "Germany North"; Gateways = ("51.116.56.0"); TRs = ('tr1', 'tr3', 'tr79', 'tr84'); Cluster = 'germanynorth1-a.worker.database.windows.net'; }
New-Object PSObject -Property @{Region = "Germany West Central"; Gateways = ("51.116.152.0", "51.116.240.0", "51.116.248.0"); TRs = ('tr206', 'tr188', 'tr495', 'tr669'); Cluster = 'germanywestcentral1-a.worker.database.windows.net'; }
New-Object PSObject -Property @{Region = "India Central"; Gateways = ("104.211.86.30", "104.211.86.31", "40.80.48.32", "20.192.96.32", "20.192.43.160", "20.192.43.161"); TRs = ('tr755', 'tr3', 'tr724', 'tr104'); Cluster = 'indiacentral1-a.worker.database.windows.net'; }
New-Object PSObject -Property @{Region = "India South"; Gateways = ("104.211.224.146", "40.78.192.33", "40.78.192.34"); TRs = ('tr3', 'tr464', 'tr490', 'tr474'); Cluster = 'indiasouth1-a.worker.database.windows.net'; }
New-Object PSObject -Property @{Region = "India West"; Gateways = ("104.211.144.4"); TRs = ('tr7', 'tr99', 'tr214', 'tr227'); Cluster = 'indiawest1-a.worker.database.windows.net'; }
New-Object PSObject -Property @{Region = "Japan East"; Gateways = ("40.79.184.8", "40.79.192.5", "13.78.104.32", "40.79.184.32", "20.191.165.160", "20.191.165.161", "20.191.165.162"); TRs = ('tr1945', 'tr2260', 'tr2004', 'tr1944'); Cluster = 'japaneast1-a.worker.database.windows.net'; }
New-Object PSObject -Property @{Region = "Japan West"; Gateways = ("104.214.148.156", "40.74.97.10", "20.18.179.192", "20.18.179.193"); TRs = ('tr11', 'tr12', 'tr13'); Cluster = 'japanwest1-a.worker.database.windows.net'; }
New-Object PSObject -Property @{Region = "Korea Central"; Gateways = ("52.231.17.22", "52.231.17.23", "20.44.24.32", "20.194.64.33"); TRs = ('tr760', 'tr10', 'tr118', 'tr698'); Cluster = 'koreacentral1-a.worker.database.windows.net'; }
New-Object PSObject -Property @{Region = "Korea South"; Gateways = ("52.231.151.96"); TRs = ('tr149', 'tr3', 'tr75', 'tr77'); Cluster = 'koreasouth1-a.worker.database.windows.net'; }
New-Object PSObject -Property @{Region = "North Central US"; Gateways = ("52.162.104.33", "52.162.105.9", "52.162.105.200", "20.125.171.192"); TRs = ('tr13', 'tr15', 'tr16', 'tr1871'); Cluster = 'northcentralus1-a.worker.database.windows.net'; }
New-Object PSObject -Property @{Region = "North Europe"; Gateways = ("52.138.224.1", "13.74.104.113"); TRs = ('tr24', 'tr31', 'tr1579', 'tr3180'); Cluster = 'northeurope1-a.worker.database.windows.net'; }
New-Object PSObject -Property @{Region = "Norway East"; Gateways = ("51.120.96.0", "51.120.96.33", "51.120.104.32", "51.120.208.32"); TRs = ('tr1', 'tr45', 'tr14'); Cluster = 'norwayeast1-a.worker.database.windows.net'; }
New-Object PSObject -Property @{Region = "Norway West"; Gateways = ("51.120.216.0"); TRs = ('tr1', 'tr17', 'tr14'); Cluster = 'norwaywest1-a.worker.database.windows.net'; }
New-Object PSObject -Property @{Region = "South Africa North"; Gateways = ("102.133.152.0", "102.133.120.2", "102.133.152.32"); TRs = ('tr544', 'tr299', 'tr445', 'tr480'); Cluster = 'southafricanorth1-a.worker.database.windows.net'; }
New-Object PSObject -Property @{Region = "South Africa West"; Gateways = ("102.133.24.0", "102.133.25.32"); TRs = ('tr1', 'tr18', 'tr22'); Cluster = 'southafricawest1-a.worker.database.windows.net'; }
New-Object PSObject -Property @{Region = "South Central US"; Gateways = ("104.214.16.32", "20.45.121.1", "20.49.88.1"); TRs = ('tr22', 'tr24', 'tr2465', 'tr2554'); Cluster = 'southcentralus1-a.worker.database.windows.net'; }
New-Object PSObject -Property @{Region = "South East Asia"; Gateways = ("40.78.232.3", "13.67.16.193", "20.195.65.33"); TRs = ('tr3335', 'tr2135', 'tr3866', 'tr3572'); Cluster = 'southeastasia1-a.worker.database.windows.net'; }
New-Object PSObject -Property @{Region = "Switzerland North"; Gateways = ("51.107.56.0", "20.208.19.192", "51.103.203.192"); TRs = ('tr1', 'tr2', 'tr54'); Cluster = 'switzerlandnorth1-a.worker.database.windows.net'; }
New-Object PSObject -Property @{Region = "Switzerland West"; Gateways = ("51.107.152.0"); TRs = ('tr1', 'tr2', 'tr52'); Cluster = 'switzerlandwest1-a.worker.database.windows.net'; }
New-Object PSObject -Property @{Region = "UAE Central"; Gateways = ("20.37.72.64", "20.37.72.96"); TRs = ('tr4', 'tr23', 'tr49'); Cluster = 'uaecentral1-a.worker.database.windows.net'; }
New-Object PSObject -Property @{Region = "UAE North"; Gateways = ("65.52.248.0", "65.52.248.32", "20.38.152.24"); TRs = ('tr1', 'tr4', 'tr76', 'tr410'); Cluster = 'uaenorth1-a.worker.database.windows.net'; }
New-Object PSObject -Property @{Region = "UK South"; Gateways = ("51.105.64.0", '51.140.144.36', '51.105.72.32', "51.143.209.224"); TRs = ('tr5', 'tr6', 'tr1666', 'tr2811'); Cluster = 'uksouth1-a.worker.database.windows.net'; }
New-Object PSObject -Property @{Region = "UK West"; Gateways = ("51.140.208.96", "51.140.208.97", "51.140.208.99"); TRs = ('tr14', 'tr127', 'tr529', 'tr623'); Cluster = 'ukwest1-a.worker.database.windows.net'; }
New-Object PSObject -Property @{Region = "West Central US"; Gateways = ("13.78.248.43", '13.71.193.32', '13.71.193.33'); TRs = ('tr11', 'tr359', 'tr409', 'tr1367'); Cluster = 'westcentralus1-a.worker.database.windows.net'; }
New-Object PSObject -Property @{Region = "West Europe"; Gateways = ("104.40.168.105", "52.236.184.163", "20.61.99.192", "20.61.99.193"); TRs = ('tr29', 'tr30', 'tr33', 'tr34'); Cluster = 'westeurope1-a.worker.database.windows.net'; }
New-Object PSObject -Property @{Region = "West US"; Gateways = ("104.42.238.205", "13.86.216.196","13.86.217.224","13.86.217.225","20.168.163.192","20.168.163.193"); TRs = ('tr37', 'tr38', 'tr41', 'tr47'); Cluster = 'westus1-a.worker.database.windows.net'; }
New-Object PSObject -Property @{Region = "West US 2"; Gateways = ("40.78.240.8", "40.78.248.10", "20.51.9.128", "20.51.9.129"); TRs = ('tr4709', 'tr6453', 'tr6469', 'tr7228'); Cluster = 'westus2-a.worker.database.windows.net'; }
New-Object PSObject -Property @{Region = "West US 3"; Gateways = ("20.150.168.0", "20.150.184.2"); TRs = ('tr1', 'tr4', 'tr1235'); Cluster = 'westus3-a.worker.database.windows.net'; }
New-Object PSObject -Property @{Region = "US DoD East"; Gateways = ("52.126.200.3"); TRs = ('tr3', 'tr4', 'tr5'); Cluster = 'usdodeast1-a.worker.database.usgovcloudapi.net'; }
New-Object PSObject -Property @{Region = "US DoD Central"; Gateways = ("52.182.88.34"); TRs = ('tr1', 'tr4', 'tr7'); Cluster = 'usdodcentral1-a.worker.database.usgovcloudapi.net'; }
New-Object PSObject -Property @{Region = "US Gov Texas"; Gateways = ("52.127.33.32"); TRs = ('tr1', 'tr2', 'tr29'); Cluster = 'usgovsouthcentral1-a.worker.database.usgovcloudapi.net'; }
New-Object PSObject -Property @{Region = "US Gov Arizona"; Gateways = ("52.127.0.14"); TRs = ('tr1', 'tr4', 'tr13'); Cluster = 'usgovsouthwest1-a.worker.database.usgovcloudapi.net'; }
New-Object PSObject -Property @{Region = "US Gov Virginia"; Gateways = ("13.72.48.140", "52.127.41.2", "20.140.88.2"); TRs = ('tr1', 'tr3', 'tr5'); Cluster = 'usgoveast1-a.worker.database.usgovcloudapi.net';
}
$TRPorts = @('11000', '11001', '11003', '11005', '11006')
$summaryLog = New-Object -TypeName "System.Text.StringBuilder"
$summaryRecommendedAction = New-Object -TypeName "System.Text.StringBuilder"
$AnonymousRunId = ([guid]::NewGuid()).Guid
# Error Messages
$DNSResolutionFailed = ' Please make sure the server name FQDN is correct and that your machine can resolve it.
Failure to resolve domain name for your logical server is almost always the result of specifying an invalid/misspelled server name,
or a client-side networking issue that you will need to pursue with your local network administrator.'
$DNSResolutionGotMultipleAddresses = ' While testing DNS resolution from multiples sources (hosts file/cache/your DNS server/external DNS service) we got multiple addresses.
To connect to SQL Database or Azure Synapse, you need to allow network traffic to and from all Gateways for the region.
The Gateway used is not static, configuring a single specific address (like in hosts file) may lead to total lack of connectivity or intermittent connectivity issues (now or in the future).
Having DNS resolution switching between a couple of Gateway addresses is expected.
If you are using Private Link, a mismatch between your DNS server and OpenDNS is expected.
Please review the DNS results.'
$DNSResolutionGotMultipleAddressesMI = ' While testing DNS resolution from multiples sources (hosts file/cache/your DNS server/external DNS service) we got multiple addresses.
SQL Managed Instance IP address may change, see more at https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/frequently-asked-questions-faq#connectivity
Configuring a specific IP address (like in hosts file) may lead to total lack of connectivity or intermittent connectivity issues (now or in the future).
Please review the DNS results.'
$DNSResolutionFailedSQLMIPublicEndpoint = ' Please make sure the server name FQDN is correct and that your machine can resolve it.
You seem to be trying to connect using Public Endpoint, this error can be caused if the Public Endpoint is Disabled.
See how to enable public endpoint for your managed instance at https://aka.ms/mimanage-publicendpoint
If public endpoint is enabled, failure to resolve domain name for your logical server is almost always the result of specifying an invalid/misspelled server name,
or a client-side networking issue that you will need to pursue with your local network administrator.'
$SQLDB_InvalidGatewayIPAddress = ' In case you are not using Private Endpoint, please make sure the server name FQDN is correct and that your machine can resolve it to a valid gateway IP address (DNS configuration).
In case you are not using Private Link, failure to resolve domain name for your logical server is almost always the result of specifying an invalid/misspelled server name,
or a client-side networking issue that you will need to pursue with your local network administrator.
See the valid gateway addresses at https://docs.microsoft.com/azure/azure-sql/database/connectivity-architecture#gateway-ip-addresses
See more about Private Endpoint at https://docs.microsoft.com/en-us/azure/azure-sql/database/private-endpoint-overview'
$SQLDB_GatewayTestFailed = ' Failure to reach the Gateway 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.
See more about connectivity architecture at https://docs.microsoft.com/azure/azure-sql/database/connectivity-architecture'
$SQLDB_Redirect = " Servers in SQL Database and Azure Synapse support Redirect, Proxy or Default for the server's connection policy setting:
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 is Redirect for all client connections originating inside of Azure (for example, from an Azure Virtual Machine)
and Proxy for all client connections originating outside (for example, connections from your local workstation).
Redirect (recommended): Clients establish connections directly to the node hosting the database, leading to reduced latency and improved throughput.
For connections to use this mode, clients need to:
- Allow outbound communication from the client to all Azure SQL IP addresses in the region on ports in the range of 11000-11999.
- Allow outbound communication from the client to Azure SQL Database gateway IP addresses on port 1433.
Proxy: In this mode, all connections are proxied via the Azure SQL Database gateways, leading to increased latency and reduced throughput.
For connections to use this mode, clients need to allow outbound communication from the client to Azure SQL Database gateway IP addresses on port 1433.
If you are using Proxy, the Redirect Policy related tests would not be a problem.
If you are using Redirect, failure to reach ports in the range of 11000-11999 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.
Please check more about connection policies at https://docs.microsoft.com/en-us/azure/azure-sql/database/connectivity-architecture#connection-policy"
$SQLMI_GatewayTestFailed = " You can connect to SQL Managed Instance via private endpoint if you are connecting from one of the following:
- machine inside the same virtual network
- machine in a peered virtual network
- machine that is network connected by VPN or Azure ExpressRoute
Failure to reach the Gateway 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.
We strongly recommend you request assistance from your network administrator, some validations you may do together are:
- The host name is valid and port used for the connection is 1433, format is tcp:<mi_name>.<dns_zone>.database.windows.net,1433
- The Network Security Groups (NSG) on the managed instance subnet allows access on port 1433.
- If you are unable to connect from an Azure hosted client (like an Azure virtual machine), check if you have a Network Security Group set on the client subnet that might be blocking *outbound* access on port 1433.
- If the connection type is Redirect:
- Ensure the Network Security Groups (NSG) on the managed instance subnet allows access on ports **11000-11999**.
- If you are unable to connect from an Azure hosted client (like an Azure virtual machine), check if you have a Network Security Group set on the client subnet that might be blocking *outbound* access on ports **11000-11999**.
- Any networking device used (like firewalls, NVAs) do not block the traffic mentioned above.
- Routing is properly configured, and asymmetric routing is avoided.
A route with the 0.0.0.0/0 address prefix instructs Azure how to route traffic destined for an IP address that is not within the address prefix of any other route in a subnet's route table. When a subnet is created, Azure creates a default route to the 0.0.0.0/0 address prefix, with the **Internet** next hop type. Check if this route was overridden. See the details about impact of changes on this default route at https://docs.microsoft.com/azure/virtual-network/virtual-networks-udr-overview#default-route
- If you are using virtual network peering between different regions, ensure that **global virtual network peering** is supported. See more at https://docs.microsoft.com/azure/azure-sql/managed-instance/connect-application-instance#connect-inside-a-different-vnet
- If you are using peering via VPN gateway, ensure the two virtual networks are properly peered, see more at https://docs.microsoft.com/azure/azure-sql/managed-instance/connect-application-instance#connect-from-on-premises
Learn more about how to connect your application to Azure SQL Managed Instance at https://docs.microsoft.com/azure/azure-sql/managed-instance/connect-application-instance
"
$SQLMI_PublicEndPoint_GatewayTestFailed = " This usually indicates a client-side networking issue (like DNS issue or a port being blocked) that you will need to pursue with your local network administrator.
We strongly recommend you request assistance from your network administrator, some validations you may do together are:
- You have Public Endpoint enabled, see https://docs.microsoft.com/azure/azure-sql/managed-instance/public-endpoint-configure#enabling-public-endpoint-for-a-managed-instance-in-the-azure-portal
- You have allowed public endpoint traffic on the network security group, see https://docs.microsoft.com/azure/azure-sql/managed-instance/public-endpoint-configure#allow-public-endpoint-traffic-on-the-network-security-group
- The host name contains .public. and that port used in the connection string is 3342, format is <mi_name>.public.<dns_zone>.database.windows.net,3342
- Network traffic to this endpoint and port is allowed from the source and any networking appliances you may have (firewalls, etc.).
- Routing is properly configured, and asymmetric routing is avoided.
A route with the 0.0.0.0/0 address prefix instructs Azure how to route traffic destined for an IP address that is not within the address prefix of any other route in a subnet's route table. When a subnet is created, Azure creates a default route to the 0.0.0.0/0 address prefix, with the **Internet** next hop type. Check if this route was overridden. See the details about impact of changes on this default route at https://docs.microsoft.com/azure/virtual-network/virtual-networks-udr-overview#default-route
See more about connectivity using Public Endpoint at https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/public-endpoint-configure
"
$AAD_login_windows_net = ' If you are using AAD Password or AAD Integrated Authentication please make sure you fix the connectivity from this machine to login.windows.net:443
This usually indicates a client-side networking issue (like DNS issue or a port being blocked) that you will need to pursue with your local network administrator.'
$AAD_login_microsoftonline_com = ' If you are using AAD Universal with MFA authentication please make sure you fix the connectivity from this machine to login.microsoftonline.com:443
This usually indicates a client-side networking issue (like DNS issue or a port being blocked) that you will need to pursue with your local network administrator.'
$AAD_secure_aadcdn_microsoftonline_p_com = ' If you are using AAD Universal with MFA authentication please make sure you fix the connectivity from this machine to secure.aadcdn.microsoftonline-p.com:443
This usually indicates a client-side networking issue (like DNS issue or a port being blocked) that you will need to pursue with your local network administrator.'
$error18456RecommendedSolution = ' This error indicates that the login request was rejected, the most common reasons are:
- Incorrect or empty password: Please ensure that you have provided the correct password.
- Database does not exist: Please ensure that the connection string has the correct database name.
- Insufficient permissions: The user does not have CONNECT permissions to the database. Please ensure that the user is granted the necessary permissions to login.
- Connections rejected due to DoSGuard protection: DoSGuard actively tracks failed logins from IP addresses. If there are multiple failed logins from a specific IP address within a period of time, the IP address is blocked from accessing any resources in the service for a pre-defined time period even if the password and other permissions are correct.'
$ServerNameNotSpecified = ' The parameter $Server was not specified, please set the parameters on the script, you need to set server name. Database name, user and password are optional but desirable.
You can see more details about how to use this tool at https://github.com/Azure/SQL-Connectivity-Checker'
$followUpMessage = ' If this is a database engine error code you may see more about it at https://docs.microsoft.com/sql/relational-databases/errors-events/database-engine-events-and-errors'
$SQLMI_PrivateEndpoint_Error40532 = " Error 40532 is usually related to one of the following scenarios:
- The username (login) contains the '@' symbol (e.g., a login of the form 'user@mydomain.com').
You can't currently login with usernames containing these characters. We are working on removing this limitation.
- Trying to connect using the IP address instead of the FQDN of your server.
Connecting to a managed instance using an IP address is not supported. A Managed Instance's host name maps to the load balancer in front of the Managed Instance's virtual cluster. As one virtual cluster can host multiple Managed Instances, a connection can't be routed to the proper Managed Instance without specifying its name.
- The IP address associated with your managed instance changed but you DNS record still points to previous address.
The managed instance service doesn't claim static IP address support, we strongly discourage relying on immutability of the IP address as it could cause unnecessary downtime.
"
$SQLDB_Error40532 = ' Error 40532 is usually related to one of the following scenarios:
- The username (login) contains the "@" symbol (e.g., a login of the form "user@mydomain.com").
If the {servername} value shown in the error is "mydomain.com" then you are encountering this scenario.
See how to handle this at https://techcommunity.microsoft.com/t5/azure-database-support-blog/providing-the-server-name-explicitly-in-user-names-for-azure-sql/ba-p/368942
- The subnet where you are trying to connect from has Microsoft.Sql service endpoint enabled
Turning on virtual network service endpoints to Microsoft.Sql in the subnet enables the endpoints for Azure SQL Database, Azure Synapse Analytics, Azure Database for PostgreSQL server, Azure Database for MySQL server and Azure Database for MariaDB. Attempts to connect from subnet might fail if virtual network rules are not set.
This issue is usually originated by one of the following:
- Aiming to connect to SQL Database using service endpoints, Microsoft.Sql was enabled in the subnet but the virtual network rule for the originating subnet in the Firewalls and virtual networks settings on the server was not added.
- Aiming to connect to other database service (like Azure Database for MySQL as an example), Azure SQL Database was also impacted.
To fix this issue create a virtual network rule in your server in SQL Database, for the originating subnet in the Firewalls and virtual networks.
See how to at https://docs.microsoft.com/azure/azure-sql/database/vnet-service-endpoint-rule-overview#use-the-portal-to-create-a-virtual-network-rule
You can also consider removing the service endpoint from the subnet, but you will need to take into consideration the impact in all the services mentioned above.'
$CannotDownloadAdvancedScript = ' Advanced connectivity policy tests script could not be downloaded!
Confirm this machine can access https://github.com/Azure/SQL-Connectivity-Checker/
or use a machine with Internet access to see how to run this from machines without Internet. See how at https://github.com/Azure/SQL-Connectivity-Checker/'
$DNSResolutionDNSfromHostsFile = "We detected a configuration on local cache (while including hosts file), note that Azure SQL Database and Azure Synapse Analytics doesn't have a static IP address.
Logins for Azure SQL Database or Azure Synapse Analytics can land on any of the Gateways in a region.
For this reason, we strongly discourage relying on immutability of the IP address as it could cause unnecessary downtime.
Usage of hosts file should be avoided, local cache refresh rate should be reviewed."
$DNSResolutionDNSfromHostsFileMI = "We detected a configuration on local cache (while including hosts file), note that Managed instance doesn't have a static IP address.
The managed instance service doesn't claim static IP address support and reserves the right to change it without notice as a part of regular maintenance cycles.
For this reason, we strongly discourage relying on immutability of the IP address as it could cause unnecessary downtime.
Usage of hosts file should be avoided, local cache refresh rate should be reviewed."
# PowerShell Container Image Support Start
if (!$(Get-Command 'Test-NetConnection' -errorAction SilentlyContinue)) {
function Test-NetConnection {
param(
[Parameter(Position = 0, Mandatory = $true)] $HostName,
[Parameter(Mandatory = $true)] $Port
);
process {
$client = [TcpClient]::new()
try {
$client.Connect($HostName, $Port)
$result = @{TcpTestSucceeded = $true; InterfaceAlias = 'Unsupported' }
}
catch {
$result = @{TcpTestSucceeded = $false; InterfaceAlias = 'Unsupported' }
}
$client.Dispose()
return $result
}
}
}
if (!$(Get-Command 'Resolve-DnsName' -errorAction SilentlyContinue)) {
function Resolve-DnsName {
param(
[Parameter(Position = 0)] $Name,
[Parameter()] $Server,
[switch] $CacheOnly,
[switch] $DnsOnly,
[switch] $NoHostsFile
);
process {
try {
Write-Host " Trying to resolve DNS for" $Name
return @{ IPAddress = [System.Net.DNS]::GetHostAddresses($Name).IPAddressToString };
}
catch {
TrackWarningAnonymously ('Error at Resolve-DnsName override: ' + $_.Exception.Message)
}
}
}
}
if (!$(Get-Command 'Get-NetRoute' -errorAction SilentlyContinue)) {
function Get-NetRoute {
param(
[Parameter(Position = 0, Mandatory = $true)] $InterfaceAlias
);
process {
Write-Host 'Unsupported'
}
}
}
if (!$(Get-Command 'netsh' -errorAction SilentlyContinue) -and $CollectNetworkTrace) {
Write-Host "WARNING: Current environment doesn't support network trace capture. This option is now disabled!"
$CollectNetworkTrace = $false
}
# PowerShell Container Image Support End
function PrintDNSResults($dnsResult, [string] $dnsSource, $errorVariable, $Server) {
Try {
$dnsResultIpAddress = $null
if ($errorVariable -and $errorVariable[0].Exception.Message -notmatch 'DNS record does not exist' -and $errorVariable[0].Exception.Message -notmatch 'DNS name does not exist') {
$msg = ' Error getting DNS record in ' + $dnsSource + ' (' + $errorVariable[0].Exception.Message.Replace(" : " + $Server, "") + ')'
Write-Host $msg
[void]$summaryLog.AppendLine($msg)
TrackWarningAnonymously $msg
}
else {
if ($dnsResult -and $dnsResult.IPAddress -and !([string]::IsNullOrEmpty($dnsResult.IPAddress))) {
$dnsResultIpAddress = $dnsResult.IPAddress
$msg = ' Found DNS record in ' + $dnsSource + ' (IP Address:' + $dnsResult.IPAddress + ')'
Write-Host $msg
[void]$summaryLog.AppendLine($msg)
}
else {
Write-Host ' Could not find DNS record in' $dnsSource
}
}
return $dnsResultIpAddress
}
Catch {
$msg = "Error at PrintDNSResults for " + $dnsSource + '(' + $_.Exception.Message + ')'
#Write-Host $msg -Foreground Red
#Write-Host $_.Exception.Message -ForegroundColor Red
TrackWarningAnonymously $msg
}
}
function ValidateDNS([String] $Server, [bool]$isManagedInstance) {
Try {
Write-Host 'Validating DNS record for' $Server -ForegroundColor Green
$DNSlist = New-Object Collections.Generic.List[string]
if ($PSVersionTable.PSVersion.Major -le 5 -or $IsWindows) {
Try {
$DNSfromCacheError = $null
$DNSfromCache = Resolve-DnsName -Name $Server -NoHostsFile -CacheOnly -ErrorAction SilentlyContinue -ErrorVariable DNSfromCacheError
$DNSfromCacheAddress = PrintDNSResults $DNSfromCache 'local cache (while excluding hosts file)' $DNSfromCacheError $Server
if ($DNSfromCacheAddress -and -1 -eq $DNSlist.IndexOf($DNSfromCacheAddress)) {
$DNSlist.Add($DNSfromCacheAddress);
}
}
Catch {
Write-Host "Error at ValidateDNS from local cache (while excluding hosts file)" -Foreground Red
Write-Host $_.Exception.Message -ForegroundColor Red
TrackWarningAnonymously 'Error at ValidateDNS from local cache (while excluding hosts file)'
}
Try {
$DNSfromHostsError = $null
$DNSfromHosts = Resolve-DnsName -Name $Server -CacheOnly -ErrorAction SilentlyContinue -ErrorVariable DNSfromHostsError
$DNSfromHostsAddress = PrintDNSResults $DNSfromHosts 'local cache (while including hosts file)' $DNSfromHostsError $Server
if ($DNSfromHostsAddress -and -1 -eq $DNSlist.IndexOf($DNSfromHostsAddress)) {
$DNSlist.Add($DNSfromHostsAddress);
}
}
Catch {
Write-Host "Error at ValidateDNS from local cache (while including hosts file)" -Foreground Red
Write-Host $_.Exception.Message -ForegroundColor Red
TrackWarningAnonymously 'Error at ValidateDNS from local cache (while including hosts file)'
}
Try {
$DNSfromCustomerServerError = $null
$DNSfromCustomerServer = Resolve-DnsName -Name $Server -DnsOnly -ErrorAction SilentlyContinue -ErrorVariable DNSfromCustomerServerError
$DNSfromCustomerServerAddress = PrintDNSResults $DNSfromCustomerServer 'DNS server' $DNSfromCustomerServerError $Server
if ($DNSfromCustomerServerAddress -and -1 -eq $DNSlist.IndexOf($DNSfromCustomerServerAddress)) {
$DNSlist.Add($DNSfromCustomerServerAddress);
}
}
Catch {
Write-Host "Error at ValidateDNS from DNS server" -Foreground Red
Write-Host $_.Exception.Message -ForegroundColor Red
TrackWarningAnonymously 'Error at ValidateDNS from DNS server'
}
Try {
$DNSfromOpenDNSError = $null
$DNSfromOpenDNS = Resolve-DnsName -Name $Server -DnsOnly -Server 208.67.222.222 -ErrorAction SilentlyContinue -ErrorVariable DNSfromOpenDNSError
$DNSfromOpenDNSAddress = PrintDNSResults $DNSfromOpenDNS 'Open DNS' $DNSfromOpenDNSError $Server
if ($DNSfromOpenDNSAddress -and -1 -eq $DNSlist.IndexOf($DNSfromOpenDNSAddress)) {
$DNSlist.Add($DNSfromOpenDNSAddress);
}
}
Catch {
Write-Host "Error at ValidateDNS from Open DNS" -Foreground Red
Write-Host $_.Exception.Message -ForegroundColor Red
TrackWarningAnonymously 'Error at ValidateDNS from Open DNS'
}
if ($DNSfromHostsAddress) {
if ($isManagedInstance) {
$msg = $DNSResolutionDNSfromHostsFileMI
}
else {
$msg = $DNSResolutionDNSfromHostsFile
}
Write-Host
Write-Host $msg -ForegroundColor Red
[void]$summaryLog.AppendLine()
[void]$summaryRecommendedAction.AppendLine()
[void]$summaryLog.AppendLine($msg)
[void]$summaryRecommendedAction.AppendLine($msg)
}
if (!$DNSfromCustomerServerAddress) {
Write-Host
$msg = ('DNS resolution using DNS Server could not be verified, please verify if FQDN is valid and address is getting resolved properly.');
Write-Host $msg -ForegroundColor Red
[void]$summaryLog.AppendLine()
[void]$summaryRecommendedAction.AppendLine()
[void]$summaryLog.AppendLine($msg)
[void]$summaryRecommendedAction.AppendLine($msg)
TrackWarningAnonymously 'EmptyDNSfromCustomerServer'
}
if (!$DNSfromOpenDNSAddress) {
Write-Host
$msg = ('DNS resolution using an external provider (OpenDNS) could not be verified, please verify if FQDN is valid and address is getting resolved properly.');
Write-Host $msg -ForegroundColor Red
[void]$summaryLog.AppendLine()
[void]$summaryRecommendedAction.AppendLine()
[void]$summaryLog.AppendLine($msg)
[void]$summaryRecommendedAction.AppendLine($msg)
TrackWarningAnonymously 'EmptyDNSfromOpenDNS'
}
$hasPrivateLinkAlias = HasPrivateLinkAlias $Server
if (($DNSlist.Count -gt 1) -and ($hasPrivateLinkAlias -eq $false)) {
Write-Host
$msg = ('WARNING: Distinct DNS records were found! (' + [string]::Join(", ", $DNSlist) + ')');
Write-Host $msg -ForegroundColor Red
[void]$summaryLog.AppendLine()
[void]$summaryRecommendedAction.AppendLine()
[void]$summaryLog.AppendLine($msg)
[void]$summaryRecommendedAction.AppendLine($msg)
TrackWarningAnonymously $msg
if ($isManagedInstance) {
$msg = $DNSResolutionGotMultipleAddressesMI
Write-Host $msg -Foreground Red
[void]$summaryRecommendedAction.AppendLine($msg)
}
else {
$msg = $DNSResolutionGotMultipleAddresses
Write-Host $msg -Foreground Red
[void]$summaryRecommendedAction.AppendLine($msg)
}
}
}
else {
Write-Host ' DNS resolution:' ([System.Net.DNS]::GetHostAddresses($Server).IPAddressToString)
}
}
Catch {
Write-Host "Error at ValidateDNS" -Foreground Red
Write-Host $_.Exception.Message -ForegroundColor Red
}
}
function IsManagedInstance([String] $Server) {
$hasKnownFQDN = ($Server -match '.database.windows.net') -or ($Server -match '.database.chinacloudapi.cn') -or ($Server -match '.database.usgovcloudapi.net');
$has4parts = [bool]((($Server.ToCharArray() | Where-Object { $_ -eq '.' } | Measure-Object).Count) -ge 4);
$isSQLDBPE = ($Server -match '.privatelink.database.');
$has3342 = ($Server -match ',3342') -or ($Server -match ', 3342')
return ($hasKnownFQDN -and $has4parts -and !$isSQLDBPE) -or $has3342
}
function IsSqlOnDemand([String] $Server) {
return [bool]($Server -match '-ondemand.')
}
function IsManagedInstancePublicEndpoint([String] $Server) {
$isMI = (IsManagedInstance $Server);
$hasPublic = ($Server -match '.public.');
$has3342 = ($Server -match ',3342') -or ($Server -match ', 3342');
return $isMI -and ($hasPublic -or $has3342)
}
function HasPrivateLinkAlias([String] $Server) {
[bool]((((Resolve-DnsName $Server) | Where-Object { $_.Name -Match ".privatelink." } | Measure-Object).Count) -gt 0)
}
function SanitizeString([String] $param) {
return ($param.Replace('\', '_').Replace('/', '_').Replace("[", "").Replace("]", "").Replace('.', '_').Replace(':', '_').Replace(',', '_'))
}
function FilterTranscript() {
Try {
if ($canWriteFiles) {
$lineNumber = (Select-String -Path $file -Pattern '..TranscriptStart..').LineNumber
if ($lineNumber) {
(Get-Content $file | Select-Object -Skip $lineNumber) | Set-Content $file
}
}
}
Catch {
Write-Host $_.Exception.Message -ForegroundColor Red
}
}
function TestConnectionToDatabase($Server, $gatewayPort, $Database, $AuthenticationType, $AuthenticationLibrary, $User, $Password) {
Write-Host
[void]$summaryLog.AppendLine()
Write-Host ([string]::Format("Testing connecting to {0} database (please wait):", $Database)) -ForegroundColor Green
Try {
$DbConnection = [System.Data.SqlClient.SQLConnection]::new()
$DbConnection.ConnectionString = GetConnectionString $Server $gatewayPort $Database $AuthenticationType $User $Password $UserAssignedIdentityClientId
$DbConnection.Open()
Write-Host ([string]::Format(" The connection attempt succeeded", $Database))
[void]$summaryLog.AppendLine([string]::Format(" The connection attempt to {0} database succeeded", $Database))
return $true
}
catch [System.Data.SqlClient.SqlException] {
$ex = $_.Exception
Switch ($_.Exception.Number) {
121 {
$msg = ' Connection to database ' + $Database + ' failed due to "The semaphore timeout period has expired" error.'
Write-Host ($msg) -ForegroundColor Red
[void]$summaryLog.AppendLine($msg)
[void]$summaryRecommendedAction.AppendLine()
[void]$summaryRecommendedAction.AppendLine($msg)
[void]$summaryRecommendedAction.AppendLine(' "The semaphore timeout period has expired" is a network error, not a SQL timeout.')
[void]$summaryRecommendedAction.AppendLine(' This appears as a SQL error because Windows passes this to the SQL process, so it is often mistaken to be a SQL error, when it is a client operating system level error.')
[void]$summaryRecommendedAction.AppendLine(' This error can occur for a very wide variety of reasons, but are typically due to a network or driver-related issue.')
[void]$summaryRecommendedAction.AppendLine(' We suggest you:')
[void]$summaryRecommendedAction.AppendLine(' - Verify if you are using an updated version of the client driver or tool.')
[void]$summaryRecommendedAction.AppendLine(' - Verify if you can connect using a different client driver or tool.')
if (IsManagedInstance $Server ) {
[void]$summaryRecommendedAction.AppendLine( ' See required versions of drivers and tools at https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/connect-application-instance#required-versions-of-drivers-and-tools')
}
[void]$summaryRecommendedAction.AppendLine(' - Check with your local network administrator for client-side networking issues.')
TrackWarningAnonymously ('TestConnectionToDatabase|Error121 State' + $ex.State)
}
916 {
$msg = ' Connection to database ' + $Database + ' failed, the login does not have sufficient permissions to connect to the named database.'
Write-Host ($msg) -ForegroundColor Red
[void]$summaryLog.AppendLine($msg)
[void]$summaryRecommendedAction.AppendLine()
[void]$summaryRecommendedAction.AppendLine($msg)
[void]$summaryRecommendedAction.AppendLine(' See more details and how to fix this error at https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-916-database-engine-error')
TrackWarningAnonymously ('TestConnectionToDatabase|Error916 State' + $ex.State)
}
10060 {
$msg = ' Connection to database ' + $Database + ' failed (error ' + $ex.Number + ', state ' + $ex.State + '): ' + $ex.Message
Write-Host ($msg) -ForegroundColor Red
[void]$summaryLog.AppendLine($msg)
[void]$summaryRecommendedAction.AppendLine()
[void]$summaryRecommendedAction.AppendLine($msg)
[void]$summaryRecommendedAction.AppendLine(' This usually indicates a client-side networking issue (like DNS issue or a port being blocked) that you will need to pursue with your local network administrator.')
TrackWarningAnonymously ('TestConnectionToDatabase|Error10060 State' + $ex.State)
}
18456 {
if ($User -eq 'AzSQLConnCheckerUser') {
if ($Database -eq 'master') {
$msg = [string]::Format(" Dummy login attempt reached '{0}' database, login failed as expected.", $Database)
Write-Host ($msg)
[void]$summaryLog.AppendLine($msg)
}
else {
$msg = [string]::Format(" Dummy login attempt on '{0}' database resulted in login failure.", $Database)
Write-Host ($msg)
[void]$summaryLog.AppendLine($msg)
$msg = ' This was either expected due to dummy credentials being used, or database does not exist, which also results in login failed.'
Write-Host ($msg)
[void]$summaryLog.AppendLine($msg)
}
}
else {
[void]$summaryRecommendedAction.AppendLine()
$msg = [string]::Format(" Login against database {0} failed for user '{1}'", $Database, $User)
Write-Host ($msg) -ForegroundColor Red
[void]$summaryLog.AppendLine($msg)
[void]$summaryRecommendedAction.AppendLine($msg)
$msg = $error18456RecommendedSolution
Write-Host ($msg) -ForegroundColor Red
[void]$summaryRecommendedAction.AppendLine($msg)
TrackWarningAnonymously 'FailedLogin18456UserCreds'
}
}
40532 {
if (IsManagedInstance $Server ) {
if ($gatewayPort -eq 3342) {
$msg = ' You seem to be trying to connect to MI using Public Endpoint but Public Endpoint may be disabled'
Write-Host ($msg) -ForegroundColor Red
[void]$summaryLog.AppendLine($msg)
[void]$summaryRecommendedAction.AppendLine($msg)
$msg = ' Learn how to configure public endpoint at https://docs.microsoft.com/en-us/azure/sql-database/sql-database-managed-instance-public-endpoint-configure'
Write-Host ($msg) -ForegroundColor Red
[void]$summaryRecommendedAction.AppendLine($msg)
TrackWarningAnonymously ('SQLMI|PublicEndpoint|Error40532 State' + $ex.State)
}
else {
$msg = ' Connection to database ' + $Database + ' failed (error ' + $ex.Number + ', state ' + $ex.State + '): ' + $ex.Message
Write-Host ($msg) -ForegroundColor Red
[void]$summaryLog.AppendLine($msg)
[void]$summaryRecommendedAction.AppendLine()
[void]$summaryRecommendedAction.AppendLine($msg)
[void]$summaryRecommendedAction.AppendLine($SQLMI_PrivateEndpoint_Error40532)
TrackWarningAnonymously ('SQLMI|PrivateEndpoint|Error40532 State' + $ex.State)
}
}
else {
$msg = ' Connection to database ' + $Database + ' failed (error ' + $ex.Number + ', state ' + $ex.State + '): ' + $ex.Message
Write-Host ($msg) -ForegroundColor Red
[void]$summaryLog.AppendLine($msg)
[void]$summaryRecommendedAction.AppendLine()
[void]$summaryRecommendedAction.AppendLine($msg)
[void]$summaryRecommendedAction.AppendLine($SQLDB_Error40532)
TrackWarningAnonymously ('SQLDB|Error40532 State' + $ex.State)
}
}
40615 {
$msg = ' Connection to database ' + $Database + ' failed (error ' + $ex.Number + ', state ' + $ex.State + '): ' + $ex.Message
Write-Host ($msg) -ForegroundColor Red
[void]$summaryLog.AppendLine($msg)
[void]$summaryRecommendedAction.AppendLine()
[void]$summaryRecommendedAction.AppendLine($msg)
[void]$summaryRecommendedAction.AppendLine(' The client is trying to connect from an IP address that is not authorized to connect to the server. The server firewall has no IP address rule that allows a client to communicate from the given IP address to the database.')
[void]$summaryRecommendedAction.AppendLine(' Add the IP address as an IP rule, see how at https://docs.microsoft.com/en-us/azure/azure-sql/database/firewall-configure')
TrackWarningAnonymously ('TestConnectionToDatabase|Error40615 State' + $ex.State)
}
47073 {
$msg = ' Connection to database ' + $Database + ' was denied since Deny Public Network Access is set to Yes.
When Deny Public Network Access setting is set to Yes, only connections via private endpoints are allowed.
When this setting is set to No (default), clients can connect using either public endpoints (IP-based firewall rules, VNET-based firewall rules) or private endpoints (using Private Link).
See more at https://docs.microsoft.com/azure/azure-sql/database/connectivity-settings#deny-public-network-access'
Write-Host ($msg) -ForegroundColor Red
[void]$summaryLog.AppendLine($msg)
[void]$summaryRecommendedAction.AppendLine()
[void]$summaryRecommendedAction.AppendLine($msg)
TrackWarningAnonymously ('TestConnectionToDatabase|47073 State' + $ex.State)
}
40914 {
$msg = ' Connection to database ' + $Database + ' failed, client is not allowed to access the server.'
Write-Host ($msg) -ForegroundColor Red
[void]$summaryLog.AppendLine($msg)
[void]$summaryRecommendedAction.AppendLine()
[void]$summaryRecommendedAction.AppendLine($msg)
[void]$summaryRecommendedAction.AppendLine(' The client is in a subnet that has virtual network server endpoints. But the server has no virtual network rule that grants to the subnet the right to communicate with the database.')
[void]$summaryRecommendedAction.AppendLine(' On the Firewall pane of the Azure portal, use the virtual network rules control to add a virtual network rule for the subnet.')
[void]$summaryRecommendedAction.AppendLine(' See how at https://docs.microsoft.com/en-us/azure/azure-sql/database/vnet-service-endpoint-rule-overview#use-the-portal-to-create-a-virtual-network-rule')
TrackWarningAnonymously ('TestConnectionToDatabase|Error40914 State' + $ex.State)
}
default {
$msg = ' Connection to database ' + $Database + ' failed (error ' + $ex.Number + ', state ' + $ex.State + '): ' + $ex.Message
Write-Host ($msg) -ForegroundColor Red
[void]$summaryLog.AppendLine($msg)
[void]$summaryRecommendedAction.AppendLine()
[void]$summaryRecommendedAction.AppendLine($msg)
[void]$summaryRecommendedAction.AppendLine($followUpMessage)
TrackWarningAnonymously ('TestConnectionToDatabase|Error:' + $ex.Number + 'State:' + $ex.State)
}
}
return $false
}
Catch {
Write-Host $_.Exception.Message -ForegroundColor Yellow
TrackWarningAnonymously 'TestConnectionToDatabase|Exception'
return $false
}
}
function GetConnectionString ($Server, $gatewayPort, $Database, $AuthenticationType, $User, $Password, $UserAssignedIdentityClientId) {
if ('Active Directory Password' -eq $AuthenticationType) {
return [string]::Format("Server=tcp:{0},{1};Initial Catalog={2};Persist Security Info=False;User ID='{3}';Password='{4}';MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Application Name=Azure-SQL-Connectivity-Checker;Authentication='Active Directory Password'",
$Server, $gatewayPort, $Database, $User, $Password)
}
if ('Active Directory Interactive' -eq $AuthenticationType) {
return [string]::Format("Server=tcp:{0},{1};Initial Catalog={2};Persist Security Info=False;User ID='{3}';MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Application Name=Azure-SQL-Connectivity-Checker;Authentication='Active Directory Interactive'",
$Server, $gatewayPort, $Database, $User)
}
if ('Active Directory Integrated' -eq $AuthenticationType) {
return [string]::Format("Server=tcp:{0},{1};Initial Catalog={2};Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Application Name=Azure-SQL-Connectivity-Checker;Authentication='Active Directory Integrated'",
$Server, $gatewayPort, $Database)
}
if ('Active Directory Managed Identity' -eq $AuthenticationType -or 'Active Directory MSI' -eq $AuthenticationType) {
if ($null -ne $UserAssignedIdentityClientId -and '' -ne $UserAssignedIdentityClientId) {
return [string]::Format("Server=tcp:{0},{1};Initial Catalog={2};Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Application Name=Azure-SQL-Connectivity-Checker;Authentication='Active Directory MSI';User ID={3}",
$Server, $gatewayPort, $Database, $UserAssignedIdentityClientId)
}
else {
return [string]::Format("Server=tcp:{0},{1};Initial Catalog={2};Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Application Name=Azure-SQL-Connectivity-Checker;Authentication='Active Directory MSI'",
$Server, $gatewayPort, $Database)
}
}
return [string]::Format("Server=tcp:{0},{1};Initial Catalog={2};Persist Security Info=False;User ID='{3}';Password='{4}';MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Application Name=Azure-SQL-Connectivity-Checker;",
$Server, $gatewayPort, $Database, $User, $Password)
}
function PrintSupportedCiphers() {
Try {
if ( ($PSVersionTable.PSVersion.Major -le 5 ) -or ($PSVersionTable.Platform -eq 'Windows')) {
$suites = Get-TlsCipherSuite | Format-Table Name -HideTableHeaders -Wrap -AutoSize | Out-String -Width 4096
Write-Host
Write-Host 'Client Tls Cipher Suites:'
Write-Host $suites.Trim()
Write-Host
$suites = Get-TlsCipherSuite
$supportedSuites = @(
'TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384',
'TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256',
'TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384',
'TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256',
'TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA',
'TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA',
'TLS_RSA_WITH_AES_256_CBC_SHA256',
'TLS_RSA_WITH_AES_128_CBC_SHA256',
'TLS_RSA_WITH_AES_256_CBC_SHA',
'TLS_RSA_WITH_AES_128_CBC_SHA',
'TLS_RSA_WITH_3DES_EDE_CBC_SHA'
)
if (($suites | Where-Object Name -in $supportedSuites | Measure-Object).Count -eq 0) {
Write-Host
$msg = "WARNING: Client machine may not have any supported cipher suite enabled!"
Write-Host $msg -Foreground Red
[void]$summaryLog.AppendLine($msg)
[void]$summaryRecommendedAction.AppendLine($msg)
Write-Host
Write-Host
$msg = 'Supported Tls Cipher Suites:'
Write-Host $msg
[void]$summaryRecommendedAction.AppendLine($msg)
$msg = $supportedSuites | Format-Table Name -HideTableHeaders -Wrap -AutoSize | Out-String -Width 4096
Write-Host $msg
[void]$summaryRecommendedAction.AppendLine($msg)
TrackWarningAnonymously 'TLS|CipherSuites|Unsupported'
}
}
}
Catch {
Write-Host "Error at PrintSupportedCiphers" -Foreground Red
Write-Host $_.Exception.Message -ForegroundColor Red
TrackWarningAnonymously 'PrintSupportedCiphers|Exception'
}
}
function PrintLocalNetworkConfiguration() {
if (![System.Net.NetworkInformation.NetworkInterface]::GetIsNetworkAvailable()) {
Write-Host "There's no network connection available!" -ForegroundColor Red
throw
}
$computerProperties = [System.Net.NetworkInformation.IPGlobalProperties]::GetIPGlobalProperties()
$networkInterfaces = [System.Net.NetworkInformation.NetworkInterface]::GetAllNetworkInterfaces()
Write-Host 'Interface information for '$computerProperties.HostName'.'$networkInterfaces.DomainName -ForegroundColor Green
foreach ($networkInterface in $networkInterfaces) {
if ($networkInterface.NetworkInterfaceType -eq 'Loopback') {
continue
}
$properties = $networkInterface.GetIPProperties()
Write-Host ' Interface name: ' $networkInterface.Name
Write-Host ' Interface description: ' $networkInterface.Description
Write-Host ' Interface type: ' $networkInterface.NetworkInterfaceType
Write-Host ' Operational status: ' $networkInterface.OperationalStatus
Write-Host ' Unicast address list:'
Write-Host $(' ' + [String]::Join([Environment]::NewLine + ' ', [System.Linq.Enumerable]::Select($properties.UnicastAddresses, [Func[System.Net.NetworkInformation.UnicastIPAddressInformation, IPAddress]] { $args[0].Address })))
Write-Host ' DNS server address list:'
Write-Host $(' ' + [String]::Join([Environment]::NewLine + ' ', $properties.DnsAddresses))
Write-Host
}
}
function RunSqlMIPublicEndpointConnectivityTests($resolvedAddress) {
Try {
$msg = 'Detected as Managed Instance using Public Endpoint'
Write-Host $msg -ForegroundColor Yellow
[void]$summaryLog.AppendLine($msg)
Write-Host 'Public Endpoint connectivity test:' -ForegroundColor Green
$testResult = Test-NetConnection $resolvedAddress -Port 3342 -WarningAction SilentlyContinue
if ($testResult.TcpTestSucceeded) {
Write-Host ' -> TCP test succeed' -ForegroundColor Green
PrintAverageConnectionTime $resolvedAddress 3342
$msg = ' Gateway connectivity to ' + $resolvedAddress + ':3342 succeed'
[void]$summaryLog.AppendLine($msg)
TrackWarningAnonymously 'SQLMI|PublicEndpoint|GatewayTestSucceeded'
RunConnectionToDatabaseTestsAndAdvancedTests $Server '3342' $Database $AuthenticationType $AuthenticationLibrary $User $Password
}
else {
Write-Host ' -> TCP test FAILED' -ForegroundColor Red
$msg = ' Gateway connectivity to ' + $resolvedAddress + ':3342 FAILED'
Write-Host $msg -Foreground Red
[void]$summaryLog.AppendLine($msg)
$msg = ' Please make sure you fix the connectivity from this machine to ' + $resolvedAddress + ':3342 (SQL MI Public Endpoint)'
Write-Host $msg -Foreground Red
[void]$summaryRecommendedAction.AppendLine($msg)
$msg = $SQLMI_PublicEndPoint_GatewayTestFailed
Write-Host $msg -Foreground Red
[void]$summaryRecommendedAction.AppendLine($msg)
TrackWarningAnonymously 'SQLMI|PublicEndpoint|GatewayTestFailed'
}
}
Catch {
Write-Host "Error at RunSqlMIPublicEndpointConnectivityTests" -Foreground Red
Write-Host $_.Exception.Message -ForegroundColor Red
TrackWarningAnonymously 'RunSqlMIPublicEndpointConnectivityTests|Exception'
}
}
function RunSqlMIVNetConnectivityTests($resolvedAddress) {
Try {
Write-Host 'Detected as Managed Instance' -ForegroundColor Yellow
$hasPrivateLinkAlias = HasPrivateLinkAlias $Server
if ($hasPrivateLinkAlias) {
Write-Host ' This instance has a privatelink alias, confirm if IP is resolving to privatelink or regular VNet internal endpoint' -ForegroundColor Yellow
TrackWarningAnonymously 'SQLMI|PrivateLinkAliasExists'
}
Write-Host
Write-Host 'Gateway connectivity tests (please wait):' -ForegroundColor Green
$testResult = Test-NetConnection $resolvedAddress -Port 1433 -WarningAction SilentlyContinue
if ($testResult.TcpTestSucceeded) {
Write-Host ' -> TCP test succeed' -ForegroundColor Green
PrintAverageConnectionTime $resolvedAddress 1433
TrackWarningAnonymously 'SQLMI|PrivateEndpoint|GatewayTestSucceeded'
RunConnectionToDatabaseTestsAndAdvancedTests $Server '1433' $Database $AuthenticationType $AuthenticationLibrary $User $Password
return $true
}
else {
Write-Host ' -> TCP test FAILED' -ForegroundColor Red
Write-Host
Write-Host ' Trying to get IP routes for interface:' $testResult.InterfaceAlias
Get-NetRoute -InterfaceAlias $testResult.InterfaceAlias -ErrorAction SilentlyContinue -ErrorVariable ProcessError
If ($ProcessError) {
Write-Host ' Could not to get IP routes for this interface'
}
Write-Host
$msg = ' Gateway connectivity to ' + $resolvedAddress + ':1433 FAILED'
Write-Host $msg -Foreground Red
[void]$summaryLog.AppendLine()
[void]$summaryLog.AppendLine($msg)
[void]$summaryRecommendedAction.AppendLine()
[void]$summaryRecommendedAction.AppendLine($msg)
$msg = ' Please fix the connectivity from this machine to ' + $resolvedAddress + ':1433'
Write-Host $msg -Foreground Red
[void]$summaryRecommendedAction.AppendLine($msg)
$msg = $SQLMI_GatewayTestFailed
Write-Host $msg -Foreground Red
[void]$summaryRecommendedAction.AppendLine($msg)
TrackWarningAnonymously 'SQLMI|PrivateEndpoint|GatewayTestFailed'
return $false
}
}
Catch {
Write-Host "Error at RunSqlMIVNetConnectivityTests" -Foreground Red
Write-Host $_.Exception.Message -ForegroundColor Red
TrackWarningAnonymously 'RunSqlMIVNetConnectivityTests|Exception'
return $false
}
}
function PrintAverageConnectionTime($addressList, $port) {
Write-Host ' Average TCP connection times:'
$stopwatch = [StopWatch]::new()
foreach ($ipAddress in $addressList) {
[double]$sum = 0
[int]$numFailed = 0
[int]$numSuccessful = 0
Write-Host ' IP Address:'$ipAddress' Port:'$port