Use-AzSqlGui

0.0.4

Use-AzSqlGui.ps1

<#PSScriptInfo
.VERSION 0.0.4
.GUID
    4c3a3f4b-1351-488d-88f4-fcda52e93278
.AUTHOR
    alex.rudolph.1987@gmail.com
.COMPANYNAME
.COPYRIGHT
    (c) 2021 Alex Rudolph. All rights reserved.
.TAGS
    Public
.LICENSEURI
.PROJECTURI
.ICONURI
.REQUIREDMODULES
.EXTERNALMODULEDEPENDENCIES
.REQUIREDSCRIPTS
.EXTERNALSCRIPTDEPENDENCIES
.RELEASENOTES
#>

<#
.SYNOPSIS
.DESCRIPTION
    This script is designed to use powershell to call azure using your azure credentials and SSO. Some prerequisite configurations are needed.
.EXAMPLE
.NOTES
    To allow pulling credentials from KV make sure that the context for the KV is set prior to executing the script as well as update the Get-Auth call in in the "Globals" section of the script.
#>
#requires -version 5.1

# ==============================================================
# FUNCTIONS
# ==============================================================
#this is here because using .Requiredmodules can be a little hinky
If(-not $installedModules){Write-Host "Getting installed modules"; $installedModules = Get-InstalledModule}

Write-Host "Validating existing modules and installing missing modules"
If(-not ($installedModules | Where-Object {$_.Name -eq "Az"})){Install-Module Az -force}

Write-Host "Importing required modules"
Import-Module Az

Function Get-AllSqlServers
{
    Write-Verbose "Loading SQL Servers"
    $Global:SQLServerList = @()
    $Contexts = Get-AzContext -ListAvailable
    ForEach($Context in $Contexts)
    {
        Write-Verbose "Checking subscription $($Context.Name)"
        $Context | Select-AzContext | out-null
        $temp = Get-AzSqlServer | Select-Object ServerName, ResourceGroupName 
        $temp | Add-Member -MemberType NoteProperty "Subscription" -Value $Context.Name
        $Global:SQLServerList += $temp
        Remove-Variable temp
    }
    $Global:selServer.Items.AddRange(($Global:SQLServerList.ServerName | Sort-Object))
}

Function Get-AllSqlDbs
{
    $Global:selDatabase.Clear()

    $subscription = $Global:SQLServerList | Where-Object {$_.ServerName -eq $Global:SelectedServer}
    Write-Verbose "Selecting subscription $($subscription.subscription)"
    #Get-AzSubscription -SubscriptionName $subscription | Select-AzSubscription
    Get-AzContext -name $subscription.Subscription | Set-AzContext

    $ResourceGroupName = ($Global:SQLServerList | Where-Object {$_.ServerName -eq $Global:SelectedServer.ToString()}).ResourceGroupName
    Write-Verbose "Checking $ResourceGroupName"

    $Global:SQLDBList = (Get-AzSqlDatabase -ServerName $Global:SelectedServer -ResourceGroupName $ResourceGroupName).DatabaseName
 
    $Global:DBColumn = $Global:selDatabase.Columns.Add('Database',205)
    $Global:selDatabase.Items.AddRange(($Global:SQLDBList))
}

Function User-Selector
{
    $userList = Get-AzADUser | Out-GridView -PassThru -Title "User Selection"
    Return $UserList
}

Function AAD-Selector
{
    $GroupList = Get-AzADGroup | Out-GridView -PassThru -Title "Group Selection"
    Return $GroupList
}

Function Add-AzSqlPermission
{
    Param($users)
    
    If($users.count -gt 0)
    {
        If($users.UserPrincipalName){$Looper = $users.UserPrincipalName}
        ElseIf($users.DisplayName){$Looper = $users.DisplayName}
        ForEach($user in $Looper)
        {
            $UserQuery = "IF not exists(select name from sys.database_principals where name = '$user')`r`n CREATE USER [$user] FROM EXTERNAL PROVIDER;"
            $splatParams = @{
                ServerInstance = "$($Global:SelectedServer).database.windows.net"
                Database = 'master'
                Query = $UserQuery
            }
            $QueryObject = Invoke-AzSqlcmd @splatParams

            $splatParams.Query +="`r`nALTER ROLE db_datareader ADD MEMBER [$user];"
            $splatParams.Database = $Global:SelectedDatabase
            $QueryObject = Invoke-AzSqlcmd @splatParams
        }
        Set-UserList
    }
    Else
    {
        write-Verbose "nothing select... moving on..."
    }
}

Function Remove-AzSqlPermission
{
    $splatParams = @{
        ServerInstance = "$($Global:SelectedServer).database.windows.net"
        Database = $Global:SelectedDatabase
        Query = "IF exists(select name from sys.database_principals where name = '$global:SelectedUser')`r`n DROP USER [$global:SelectedUser];"
    }

    If($Global:selUser.SelectedItems.count -gt 1)
    {
        ForEach($item in $Global:selUser.SelectedItems)
        {
            $splatParams.Query = "`r`nDROP USER [$($item.Text)];"
            $QueryObject = Invoke-AzSqlcmd @splatParams
        }
    }
    Else
    {
        $QueryObject = Invoke-AzSqlcmd @splatParams
    }

    Set-UserList
}

Function Get-AzSqlPermission
{
    $UserQuery = "Select * from sys.database_principals where type not in ('A', 'G', 'R') and type_desc in ('EXTERNAL_USER','EXTERNAL_GROUP') and name not in ('dbo','guest','sys','INFORMATION_SCHEMA') order by name;"
    $splatParams = @{
        ServerInstance = "$($Global:SelectedServer).database.windows.net"
        Database = $Global:SelectedDatabase
        Query = $UserQuery
    }

    $QueryObject = Invoke-AzSqlcmd @splatParams
    $PermissionsObjs += $QueryObject.dataset.tables[0]
    
    return $PermissionsObjs
}

Function Set-UserList
{
    $Global:selUser.Clear()

    $users = Get-AzSqlPermission

    if(-not $users)
    {
        [System.Windows.MessageBox]::Show('No assigned permissions found')
    }

    $Global:selUser.Columns.Add('UserName',400)
    $Global:selUser.Items.AddRange(($users.name))
}

Function Get-KeyVaultSQLCredentials
{
    <#
    .EXAMPLE
        Get-KeyVaultSQLCredentials -keyVaultName ExampleKeyVault -sqlKeyName sqlUserName -passKeyName sqlPassword
    #>    

    Param
    (
        [Parameter(Mandatory=$true)]
        $keyVaultName,
        [Parameter(Mandatory=$true)]
        $sqlKeyName,
        [Parameter(Mandatory=$true)]
        $passKeyName
    )

    [string]$SQLuser = (Get-AzKeyVaultSecret -VaultName $keyVaultName -Name $sqlKeyName).SecretValueText
    [string]$SQLpass = (Get-AzKeyVaultSecret -VaultName $keyVaultName -Name $passKeyName).SecretValueText

    #generate random
    #$Key = Get-RandomExtended -length 20 -type "Hex"

    #build PS Credential
    $secpasswd = ConvertTo-SecureString $SQLpass -AsPlainText -Force
    $SqlCred = New-Object System.Management.Automation.PSCredential ($SQLuser, $secpasswd)
    
    $return = @{cred = $sqlCred; String = $Key; UN = $SQLuser; PW = $SQLpass}

    return $return
}


Function Invoke-AzSqlRunAsUser
{
    $sUsername = $Global:SelectedUser

    [void][Reflection.Assembly]::LoadWithPartialName('Microsoft.VisualBasic')

    $title = "Running as $sUsername"
    $msg   = 'Enter your Query:'

    $text = [Microsoft.VisualBasic.Interaction]::InputBox($msg, $title)

    If (-not $text){Return}

    $UserQuery = "EXECUTE AS USER = '$sUsername'; $text;"
    $splatParams = @{
        ServerInstance = "$($Global:SelectedServer).database.windows.net"
        Database = $Global:SelectedDatabase
        Query = $UserQuery
    }

    $QueryObject = Invoke-AzSqlcmd @splatParams
    $PermissionsObjs += $QueryObject.dataset.tables[0]
    
    $PermissionsObjs | Out-GridView -Title "Results for $sUsername"
}



Function Invoke-AzSqlGetUserPermissions
{
    $sUsername = $Global:SelectedUser
    $UserQuery = "EXECUTE AS USER = '$sUsername'; SELECT * FROM fn_my_permissions (NULL, 'DATABASE');"
    $splatParams = @{
        ServerInstance = "$($Global:SelectedServer).database.windows.net"
        Database = $Global:SelectedDatabase
        Query = $UserQuery
    }

    $QueryObject = Invoke-AzSqlcmd @splatParams
    $PermissionsObjs += $QueryObject.dataset.tables[0]
    
    $PermissionsObjs | Out-GridView -Title "Permissions for $sUsername"
}

Function Invoke-AzSqlCmd
{
    Param($serverInstance
         ,$dbName
         ,$Query
         ,$context
    )
    # Build off this article https://thomasthornton.cloud/2020/10/06/query-azure-sql-database-using-service-principal-with-powershell/

    $ConnectionString="Data Source=$serverInstance; Initial Catalog=$dbName;"

    $dexResourceUrl='https://database.windows.net/'
    $token = [Microsoft.Azure.Commands.Common.Authentication.AzureSession]::Instance.AuthenticationFactory.Authenticate($context.Account, `
                                    $context.Environment, 
                                    $context.Tenant.Id.ToString(),
                                    $null, 
                                    [Microsoft.Azure.Commands.Common.Authentication.ShowDialog]::Never, 
                                    $null, $dexResourceUrl).AccessToken
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection                
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    try 
    {
        $SqlConnection.ConnectionString = $ConnectionString
        if ($token)
        {
            $SqlConnection.AccessToken = $token
        }
        $SqlConnection.Open()
         
        $SqlCmd.Connection = $SqlConnection 
        
        $SqlCmd.CommandText = $Query
        $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
        $SqlAdapter.SelectCommand = $SqlCmd
        $DataSet = New-Object System.Data.DataSet
        $SqlAdapter.Fill($DataSet)

        # based on https://www.stefanroth.net/2018/04/11/powershell-create-clean-customobjects-from-datatable-object/
        $result = @()
        ForEach ($Row in $DataSet.Tables[0])
        {
            $Properties = @{}
            For($i = 0;$i -le $Row.ItemArray.Count - 1;$i++)
            {
                $Properties.Add($DataSet[0].Tables[0].Columns[$i], $Row.ItemArray[$i])
            }
            $result += New-Object -TypeName PSObject -Property $Properties  
        }
    }
    finally
    {
        $SqlAdapter.Dispose()
        $SqlCmd.Dispose()
        $SqlConnection.Dispose()
    }
    return $result
}

Function Get-Auth
{
    Param($sqlUserKeyName, $sqlKeyPassword)
# Add-Type -AssemblyName PresentationFramework
    $error.Clear()
    If((Get-AzContext))
    {
        $disconnect = Read-Host -Prompt "You are already connected. Would you like to reconnect? Enter 'Yes' to force reconnect." 
        If($disconnect -eq 'Yes')
        {
            Disconnect-AzAccount
        }
        Do
        {
            $Global:cred = Get-Credential -Message "Please enter your Azure Credentials"
            If(-not $Global:Cred)
            {
                If($forceAzLogin)
                {
                    $msgBoxInput =  [System.Windows.Forms.MessageBox]::Show('You did not specify the credentials. The Servers and Databases will use your local CLI Connect-AzAccount so the credentials are required.','Missing credentials','OkCancel','Error')
                }
                Else
                {
                    $msgBoxInput =  [System.Windows.Forms.MessageBox]::Show('You did not specify the credentials. Would you like to try again? Select no if you would like to use keyvault.','Missing credentials','YesNoCancel','Error')
                }
                switch  ($msgBoxInput) {
                    'Yes' {}
                    'Ok' {}
                    'No' {$continue = $true}
                    'Cancel' {exit}
                }
            }
        }
        Until($continue -or $Global:cred)
        $Global:PlainPWD = $Global:cred.GetNetworkCredential().Password
    
        If($Global:cred)
        {
            Write-Verbose "Forcing Connect-AzAccount..."
            Connect-AzAccount -Credential $Global:cred -ErrorAction Stop | Out-null
        }
        
        If($Global:cred){Write-Verbose "Manual credential created"; return}
        [void][Reflection.Assembly]::LoadWithPartialName('Microsoft.VisualBasic')
        $title = 'Keyvault'
        $msg   = 'Enter your Keyvault Name:'
        $text = [Microsoft.VisualBasic.Interaction]::InputBox($msg, $title)
        If($text -eq ""){Write-Error -Message "No Keyvault specified... exiting" ;Exit}
        $Global:cred = (Get-KeyVaultSQLCredentials -keyVaultName $text -sqlKeyName $sqlUserKeyName -passKeyName $sqlKeyPassword).cred
        if($Error){Write-Error -Message $Error.Exception; exit}
    }
}

# ==============================================================
# GLOBALS
# ==============================================================
#$ErrorActionPreference = 'Stop'
$VerbosePreference = 'Continue'

$error.Clear()
$stopWatch = [System.Diagnostics.Stopwatch]::StartNew()


Add-Type -AssemblyName System.Windows.Forms
[System.Windows.Forms.Application]::EnableVisualStyles()

Get-Auth

# ==============================================================
# INIT
# ==============================================================

$Global:Form                            = New-Object system.Windows.Forms.Form
$Global:Form.Icon                       = [Drawing.Icon]::ExtractAssociatedIcon((Get-Command powershell).Path)
$Global:Form.ClientSize                 = New-Object System.Drawing.Point(724,441)
$Global:Form.text                       = "Azure SQL User Manager"
$Global:Form.TopMost                    = $false

$labServer                              = New-Object system.Windows.Forms.Label
$labServer.text                         = "Server:"
$labServer.AutoSize                     = $true
$labServer.width                        = 25
$labServer.height                       = 10
$labServer.location                     = New-Object System.Drawing.Point(6,9)
$labServer.Font                         = New-Object System.Drawing.Font('Microsoft Sans Serif',10)

$Global:selServer                       = New-Object system.Windows.Forms.ComboBox
$Global:selServer.width                 = 372
$Global:selServer.height                = 20
$Global:selServer.location              = New-Object System.Drawing.Point(56,5)
$Global:selServer.Font                  = New-Object System.Drawing.Font('Microsoft Sans Serif',10)

$Global:selDatabase                     = New-Object system.Windows.Forms.ListView
$Global:selDatabase.View                = "Details"
$Global:selDatabase.width               = 206
$Global:selDatabase.height              = 371
$Global:selDatabase.location            = New-Object System.Drawing.Point(13,57)

# Add items to the ListView
Get-AllSqlServers

$Global:selUser                         = New-Object system.Windows.Forms.ListView
$Global:selUser.View                    = "Details"
$Global:selUser.width                   = 477
$Global:selUser.height                  = 332
$Global:selUser.location                = New-Object System.Drawing.Point(235,57)

$btnAdd                                 = New-Object system.Windows.Forms.Button
$btnAdd.Enabled                         = $false
$btnAdd.text                            = "Add User"
$btnAdd.width                           = 90
$btnAdd.height                          = 30
$btnAdd.location                        = New-Object System.Drawing.Point(237,399)
$btnAdd.Font                            = New-Object System.Drawing.Font('Microsoft Sans Serif',10)

$btnAddGrp                              = New-Object system.Windows.Forms.Button
$btnAddGrp.Enabled                      = $false
$btnAddGrp.text                         = "Add Group"
$btnAddGrp.width                        = 90
$btnAddGrp.height                       = 30
$btnAddGrp.location                     = New-Object System.Drawing.Point(337,399)
$btnAddGrp.Font                         = New-Object System.Drawing.Font('Microsoft Sans Serif',10)

$btnTest                                 = New-Object system.Windows.Forms.Button
$btnTest.Enabled                         = $true
$btnTest.text                            = "Test"
$btnTest.width                           = 60
$btnTest.height                          = 30
$btnTest.location                        = New-Object System.Drawing.Point(400,399)
$btnTest.Font                            = New-Object System.Drawing.Font('Microsoft Sans Serif',10)

$btnUserPermissions                      = New-Object system.Windows.Forms.Button
$btnUserPermissions.Enabled              = $false
$btnUserPermissions.text                 = "Permissions"
$btnUserPermissions.width                = 90
$btnUserPermissions.height               = 30
$btnUserPermissions.location             = New-Object System.Drawing.Point(488,399)
$btnUserPermissions.Font                 = New-Object System.Drawing.Font('Microsoft Sans Serif',10)

$btnRunAsUser                            = New-Object system.Windows.Forms.Button
$btnRunAsUser.Enabled                    = $false
$btnRunAsUser.text                       = "RunAs"
$btnRunAsUser.width                      = 65
$btnRunAsUser.height                     = 30
$btnRunAsUser.location                   = New-Object System.Drawing.Point(580,399)
$btnRunAsUser.Font                       = New-Object System.Drawing.Font('Microsoft Sans Serif',10)


$btnRemove                              = New-Object system.Windows.Forms.Button
$btnRemove.Enabled                      = $false
$btnRemove.text                         = "Remove"
$btnRemove.width                        = 65
$btnRemove.height                       = 30
$btnRemove.location                     = New-Object System.Drawing.Point(647,399)
$btnRemove.Font                         = New-Object System.Drawing.Font('Microsoft Sans Serif',10)

$Global:Form.controls.AddRange(@($labServer,$selServer,$selDatabase,$selUser,$btnAdd,$btnAddGrp,$btnRemove,$btnUserPermissions,$btnRunAsUser))

$btnRunAsUser.Add_Click({ If(-not $Global:SelectedUser){[System.Windows.Forms.MessageBox]::Show('Please select a user to run as first')}Else{Invoke-AzSqlRunAsUser} })
$btnUserPermissions.Add_Click({ If(-not $Global:SelectedUser){[System.Windows.Forms.MessageBox]::Show('Please select a user to get permissions first')}Else{Invoke-AzSqlGetUserPermissions} })
$btnRemove.Add_Click({ If(-not $Global:SelectedUser){[System.Windows.Forms.MessageBox]::Show('Please select a user to remove first')}Else{Remove-AzSqlPermission} })
$btnAdd.Add_Click({ $ADUsers=User-Selector; If(-not $ADUsers){[System.Windows.Forms.MessageBox]::Show('No user selected')}Else{Add-AzSqlPermission -Users $ADUsers} })
$btnAddGrp.Add_Click({ $ADGroups=AAD-Selector; If(-not $ADGroups){[System.Windows.Forms.MessageBox]::Show('No group selected')}Else{Add-AzSqlPermission -Users $ADGroups} })
If($debugEnabled){ $Global:Form.controls.Add($btnTEST) ;$btnTEST.Add_Click({ If(-not $Global:SelectedUser){[System.Windows.Forms.MessageBox]::Show('Please select a user to remove first')}Else{TESTFUNCTION} }) }
$Global:selServer.Add_SelectedValueChanged({ $Global:SelectedServer = $Global:selServer.SelectedItem; Get-AllSqlDbs; write-host $SelectedServer })
$Global:selDatabase.Add_Click({ $Global:SelectedDatabase = $Global:selDatabase.SelectedItems[0].Text; $btnAdd.Enabled=$True; $btnAddGrp.Enabled=$True; Set-UserList})
$Global:selUser.Add_Click({ $Global:SelectedUser = $Global:selUser.SelectedItems[0].Text; $btnRemove.Enabled=$True; $btnRunAsUser.Enabled=$True; $btnUserPermissions.Enabled=$True;  })

$Global:Form.ShowDialog()