AzureAssess

0.1.24

Databases/Get-AzureAssessResSQLServers.ps1

                                <#

.SYNOPSIS

    Gets the SQL Servers for specified subscription and resource group

.EXAMPLE

    PS C:\Get-AzureAssessResSQLServers -SubscriptionId 3395068f-a9b5-41a9-af54-bd362b69e19a -ResourceGroupName "rg-app-service"

#>

function Get-AzureAssessResSQLServers() {

    [CmdletBinding()]

    param (

        [Parameter(Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true, Position = 1)]

        [string]$SubscriptionId,

        [Parameter(Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true, Position = 2)]

        [string]$ResourceGroupName

    )

    # Get the current context

    $context = Get-AzContext

    if ($context.Subscription.Name -ne $SubscriptionId -and $context.Subscription.Id -ne $SubscriptionId) {   

        # Set the desired subscription

        $context = Set-AzContext -Subscription $SubscriptionId

    }

    # Be sure to use subsciptionids as guid

    if ($SubscriptionId -notmatch "^(\{){0,1}[0-9a-fA-F]{8}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{12}(\}){0,1}$") {

        $SubscriptionId = $context.Subscription.Id

    }

    # Get the in the resource group

    $sqlservers = Invoke-RetryCommand -ScriptBlock { Get-AzSqlServer -ResourceGroupName $ResourceGroupName }

    foreach ($sqlserver in $sqlservers) {

        $id = $sqlserver.ResourceId -split "/"

        $Type = $id[6..($id.Count - 2)] -join "/"

        # SQL server is always protected by firewall rules except if it has a private endpoint

        $hasfwrules = $true

        # By default public network access is disabled

        $publicnetworkaccess = $true

        if ($sqlserver.PublicNetworkAccess -eq "Disabled") {

            $publicnetworkaccess = $false

        }        

        # columns to return

        # ResourceGroupId,Type,Name,Link,Location,PublicNetworkAccess,HasFirewallRules,HasPrivateEndpoint,FullyQualifiedDomainName,MinimalTlsVersion,AdministratorType,EntraOnlyAuth

        "" | select-object `

            @{N="Id";E={$sqlserver.ResourceId}}, `

            @{N="ResourceGroupId";E={"/subscriptions/$SubscriptionId/resourceGroups/$ResourceGroupName"}}, `

            @{N="Type";E={$Type}}, `

            @{N="Name";E={$sqlserver.ServerName}}, `

            @{N="Link";E={"https://portal.azure.com/#@$($context.Tenant.Id)/resource$($sqlserver.ResourceId)"}}, `

            @{N="Location";E={$sqlserver.Location}}, `

            @{N="PublicNetworkAccess";E={$publicnetworkaccess}}, `

            @{N="HasFirewallRules";E={$hasfwrules}}, `

            @{N="HasPrivateEndpoint";E={$null}}, `

            @{N="FullyQualifiedDomainName";E={$sqlserver.FullyQualifiedDomainName}}, `

            @{N="MinimalTlsVersion";E={$sqlserver.MinimalTlsVersion}}, `

            @{N="AdministratorType";E={$sqlserver.Administrators.AdministratorType}}, `

            @{N="EntraOnlyAuth";E={$sqlserver.Administrators.AzureAdOnlyAuthentication -eq $true}}

    }

}

# SIG # Begin signature block

# MIIRWAYJKoZIhvcNAQcCoIIRSTCCEUUCAQExDzANBglghkgBZQMEAgEFADB5Bgor

# BgEEAYI3AgEEoGswaTA0BgorBgEEAYI3AgEeMCYCAwEAAAQQH8w7YFlLCE63JNLG

# KX7zUQIBAAIBAAIBAAIBAAIBADAxMA0GCWCGSAFlAwQCAQUABCAcWh9iKGbcfPjq

# mndAapDVwV6JkHSVrMLzSANewXqR16CCDZIwgga5MIIEoaADAgECAhEAmaOACiZV

# O2Wr3G6EprPqOTANBgkqhkiG9w0BAQwFADCBgDELMAkGA1UEBhMCUEwxIjAgBgNV

# BAoTGVVuaXpldG8gVGVjaG5vbG9naWVzIFMuQS4xJzAlBgNVBAsTHkNlcnR1bSBD

# ZXJ0aWZpY2F0aW9uIEF1dGhvcml0eTEkMCIGA1UEAxMbQ2VydHVtIFRydXN0ZWQg

# TmV0d29yayBDQSAyMB4XDTIxMDUxOTA1MzIxOFoXDTM2MDUxODA1MzIxOFowVjEL

# MAkGA1UEBhMCUEwxITAfBgNVBAoTGEFzc2VjbyBEYXRhIFN5c3RlbXMgUy5BLjEk

# MCIGA1UEAxMbQ2VydHVtIENvZGUgU2lnbmluZyAyMDIxIENBMIICIjANBgkqhkiG

# 9w0BAQEFAAOCAg8AMIICCgKCAgEAnSPPBDAjO8FGLOczcz5jXXp1ur5cTbq96y34

# vuTmflN4mSAfgLKTvggv24/rWiVGzGxT9YEASVMw1Aj8ewTS4IndU8s7VS5+djSo

# McbvIKck6+hI1shsylP4JyLvmxwLHtSworV9wmjhNd627h27a8RdrT1PH9ud0IF+

# njvMk2xqbNTIPsnWtw3E7DmDoUmDQiYi/ucJ42fcHqBkbbxYDB7SYOouu9Tj1yHI

# ohzuC8KNqfcYf7Z4/iZgkBJ+UFNDcc6zokZ2uJIxWgPWXMEmhu1gMXgv8aGUsRda

# CtVD2bSlbfsq7BiqljjaCun+RJgTgFRCtsuAEw0pG9+FA+yQN9n/kZtMLK+Wo837

# Q4QOZgYqVWQ4x6cM7/G0yswg1ElLlJj6NYKLw9EcBXE7TF3HybZtYvj9lDV2nT8m

# FSkcSkAExzd4prHwYjUXTeZIlVXqj+eaYqoMTpMrfh5MCAOIG5knN4Q/JHuurfTI

# 5XDYO962WZayx7ACFf5ydJpoEowSP07YaBiQ8nXpDkNrUA9g7qf/rCkKbWpQ5bou

# fUnq1UiYPIAHlezf4muJqxqIns/kqld6JVX8cixbd6PzkDpwZo4SlADaCi2JSplK

# ShBSND36E/ENVv8urPS0yOnpG4tIoBGxVCARPCg1BnyMJ4rBJAcOSnAWd18Jx5n8

# 58JSqPECAwEAAaOCAVUwggFRMA8GA1UdEwEB/wQFMAMBAf8wHQYDVR0OBBYEFN10

# XUwA23ufoHTKsW73PMAywHDNMB8GA1UdIwQYMBaAFLahVDkCw6A/joq8+tT4HKbR

# Og79MA4GA1UdDwEB/wQEAwIBBjATBgNVHSUEDDAKBggrBgEFBQcDAzAwBgNVHR8E

# KTAnMCWgI6Ahhh9odHRwOi8vY3JsLmNlcnR1bS5wbC9jdG5jYTIuY3JsMGwGCCsG

# AQUFBwEBBGAwXjAoBggrBgEFBQcwAYYcaHR0cDovL3N1YmNhLm9jc3AtY2VydHVt

# LmNvbTAyBggrBgEFBQcwAoYmaHR0cDovL3JlcG9zaXRvcnkuY2VydHVtLnBsL2N0

# bmNhMi5jZXIwOQYDVR0gBDIwMDAuBgRVHSAAMCYwJAYIKwYBBQUHAgEWGGh0dHA6

# Ly93d3cuY2VydHVtLnBsL0NQUzANBgkqhkiG9w0BAQwFAAOCAgEAdYhYD+WPUCia

# U58Q7EP89DttyZqGYn2XRDhJkL6P+/T0IPZyxfxiXumYlARMgwRzLRUStJl490L9

# 4C9LGF3vjzzH8Jq3iR74BRlkO18J3zIdmCKQa5LyZ48IfICJTZVJeChDUyuQy6rG

# DxLUUAsO0eqeLNhLVsgw6/zOfImNlARKn1FP7o0fTbj8ipNGxHBIutiRsWrhWM2f

# 8pXdd3x2mbJCKKtl2s42g9KUJHEIiLni9ByoqIUul4GblLQigO0ugh7bWRLDm0Cd

# Y9rNLqyA3ahe8WlxVWkxyrQLjH8ItI17RdySaYayX3PhRSC4Am1/7mATwZWwSD+B

# 7eMcZNhpn8zJ+6MTyE6YoEBSRVrs0zFFIHUR08Wk0ikSf+lIe5Iv6RY3/bFAEloM

# U+vUBfSouCReZwSLo8WdrDlPXtR0gicDnytO7eZ5827NS2x7gCBibESYkOh1/w1t

# VxTpV2Na3PR7nxYVlPu1JPoRZCbH86gc96UTvuWiOruWmyOEMLOGGniR+x+zPF/2

# DaGgK2W1eEJfo2qyrBNPvF7wuAyQfiFXLwvWHamoYtPZo0LHuH8X3n9C+xN4YaNj

# t2ywzOr+tKyEVAotnyU9vyEVOaIYMk3IeBrmFnn0gbKeTTyYeEEUz/Qwt4HOUBCr

# W602NCmvO1nm+/80nLy5r0AZvCQxaQ4wggbRMIIEuaADAgECAhBNn7NsFkgeJAAl

# SKxTsSjWMA0GCSqGSIb3DQEBCwUAMFYxCzAJBgNVBAYTAlBMMSEwHwYDVQQKExhB

# c3NlY28gRGF0YSBTeXN0ZW1zIFMuQS4xJDAiBgNVBAMTG0NlcnR1bSBDb2RlIFNp

# Z25pbmcgMjAyMSBDQTAeFw0yNDAxMTYxMjAwMzRaFw0yNTAxMTUxMjAwMzNaMHcx

# CzAJBgNVBAYTAkJFMRgwFgYDVQQIDA9XYWxsb29uIEJyYWJhbnQxHjAcBgNVBAoM

# FU9wZW4gU291cmNlIERldmVsb3BlcjEuMCwGA1UEAwwlT3BlbiBTb3VyY2UgRGV2

# ZWxvcGVyLCBDZWRyaWMgQmxvbWFydDCCAiIwDQYJKoZIhvcNAQEBBQADggIPADCC

# AgoCggIBALp1WzPLBGmSjvJ2se9K+HWIzxlPIh9vmddaj0Sc6Yz3jJizVN7WphR7

# 1kR4KFEfCFL6eleZlIkqDMkNfwBTpfVCx7MYR85YSceHo8ieEIV5MmWSZ1CR36/A

# wXrUvXzlX85Rf/RuotfluSmdhTRoBbCN9aIsSm50pEZixvUOcipTNZbuY29NvjXP

# JAH1RX0od6QYCGt/v+5C+hBippk+QohxmKQYLilh4+58fdZvnXaKPvEuEpbhXCeh

# /HbASrvTbZXXbh8fMbHOq+xnCemK/6aqjfqgRzkXn9unjl6wj6KT7MoEp1tCn+9z

# cmi/zW3KeI3Alld5B6IEvVfZVmaCrvSsRBNFnVpVg5eGQNRyan0xfblEwKLrsoNk

# WhUtJY3SD53kvz8OHBKRY1quaq70UefcIDuPq4+76CVovjbpT/wnmP6PFnHtNrDj

# KzMAnUWOeseIcDhp7ETzVyoh2rWofTpXUr5aEau5mkXs0MclqjimMeWf0r3NyBa5

# E3k0rG/xtpmTFB9e4w7b0VQUxNUcVdmocGL7Rj4ouWLjUDUkiE2DctxMTaPmqKJE

# nx8cFsBCrDmgNYxGOZ03ykPmPbwc3SooZeS5rRmR4v2GTVrbsmbiiggtYDjhJddo

# sC/TtJ+65JL48oTHDK1KVMtSjUBr/Q16Nzg0JaXfiZ4JS+MX45ylAgMBAAGjggF4

# MIIBdDAMBgNVHRMBAf8EAjAAMD0GA1UdHwQ2MDQwMqAwoC6GLGh0dHA6Ly9jY3Nj

# YTIwMjEuY3JsLmNlcnR1bS5wbC9jY3NjYTIwMjEuY3JsMHMGCCsGAQUFBwEBBGcw

# ZTAsBggrBgEFBQcwAYYgaHR0cDovL2Njc2NhMjAyMS5vY3NwLWNlcnR1bS5jb20w

# NQYIKwYBBQUHMAKGKWh0dHA6Ly9yZXBvc2l0b3J5LmNlcnR1bS5wbC9jY3NjYTIw

# MjEuY2VyMB8GA1UdIwQYMBaAFN10XUwA23ufoHTKsW73PMAywHDNMB0GA1UdDgQW

# BBQ2dBi+EnTsl++Xmrax5h7dyzC12TBLBgNVHSAERDBCMAgGBmeBDAEEATA2Bgsq

# hGgBhvZ3AgUBBDAnMCUGCCsGAQUFBwIBFhlodHRwczovL3d3dy5jZXJ0dW0ucGwv

# Q1BTMBMGA1UdJQQMMAoGCCsGAQUFBwMDMA4GA1UdDwEB/wQEAwIHgDANBgkqhkiG

# 9w0BAQsFAAOCAgEAZm3wL05QNFh25ll601jtST553L2VVS4ugvlHmX90PSOkjmX3

# jQ2ERwdz9MHWhoOvW1EdOrBlm31YRt2GmCz4/82vq9fTTNwLSzk3csHEIgHuAbN1

# INCVgz0l7Ya7mkxFLHoZ4BJ9LluS4p2NjvU9r7OGnBCgfFaLriC+qRY8QxCeMQOl

# l1BTTaUBMp0pgxh3XOOxOQfNeFCkXg0q4QULkaRuApJFZi/pY+PMA6p8bjOTP4YI

# 9VNsoc7ReBYMDL588oupc1CTiDaf0e6YaN80z6WoIGuNja3VPDEG4VmWZG+Of9gO

# XUNllvR8n1IXXsvWEuHRTCV9jjK89NHGaNroeXHr1C6eERnoNUPbEuIb/parUnGB

# n5MKWL+2TlL9Z34lSFz6e1Efi4oJeDznUojNZKJzOMvBS+JJyr0aVuW4lDoqbqkl

# 9vFLjHi1oGM7mBrlL8AHEC3iUNEOwgPcbsIYAqV85RyXYNQqLu1ik1MmmpKy/Tma

# Mmi/AiXxtjC6RrnrkN6tIHR7Nky9b5jWfMxCCG2b119gr7SkJ8qVOH8I9oBxMDPR

# IjBi5rEjSbDOCpRhZiqmx7K7gW2Gyp1OCIffVyiqmeZh+YLxpdP0tymxtqg/HVqV

# If3xn0Sgl7HcUpcRkY3EgTPUqNaVzOSO5h8KyzA9nVtUX2XwSx9C4vb6uqcxggMc

# MIIDGAIBATBqMFYxCzAJBgNVBAYTAlBMMSEwHwYDVQQKExhBc3NlY28gRGF0YSBT

# eXN0ZW1zIFMuQS4xJDAiBgNVBAMTG0NlcnR1bSBDb2RlIFNpZ25pbmcgMjAyMSBD

# QQIQTZ+zbBZIHiQAJUisU7Eo1jANBglghkgBZQMEAgEFAKCBhDAYBgorBgEEAYI3

# AgEMMQowCKACgAChAoAAMBkGCSqGSIb3DQEJAzEMBgorBgEEAYI3AgEEMBwGCisG

# AQQBgjcCAQsxDjAMBgorBgEEAYI3AgEVMC8GCSqGSIb3DQEJBDEiBCBTB3yK9Qqa

# 8ZAfkpDAA+01FXxtVc0nXClto/3+tkg55jANBgkqhkiG9w0BAQEFAASCAgAyqPEh

# 17pYAt6z6TR0wr4XgygrtdaCCKo5PDbFKaPpeeDILvJB30syP25PPUTLXDDqQIL5

# xgCJNMq0DfpA/CiLia9O/kbXowAGSiMfJIou+A2CejcuaV3sZJEx5+oavth7x9Hq

# aPxVaNPdUebfQMW/GshpoCxqq0Wm+2IUMXUelzl9WarVSgET7toDMefCrDrt7Ki0

# M6ugHW+n0VvaEggVE6csVwswvadHH2iczsTdpD8fc+uZzVqpZqEwEo/6bzFFNT3f

# zfLF2ytnTZ+FrCHiPY25+mZMnM5L14Nl0C5u7J1O3sfaw9ZzAhJqWPLVO+wwa+nu

# 49aqUD2SzN3GMnJF91lkq5aieNKpTb070LInW2f0POXJapZnJFcZRzpNp74RBkC4

# 4wwKFeiRSTqNAP+AHKgknwbdWcvXoLxdPJXWMOGXG/zd8pxn2413XoWOtYIaZDWA

# n1cpcEUHM7mnQ1O2zoblEPRQzG7ua4AQQTmTYQWuiS34F+RWvvBAlx+7+mV5Cama

# 6udisaakOkBdmCgerz62/kAnJgCQpH3QA9JrdxNMua6JJSDurfSqOlx+Br0Fndlu

# e0yEiFR1bbmck0KJSHQMFZ9Ec/hmumH+DnZFXJP4IGOuVT3uBAzqq7plUrsq5GVG

# PE5w6IkOJN/u/5npXUTKD+u5mbCHEcuACrhABA==

# SIG # End signature block