public/VSetSQLConnectionDetails.ps1

<#
.Synopsis
   SET SQL CONNECTION DETAILS
   CREATED BY: Vadim Melamed, EMAIL: vmelamed5@gmail.com
.DESCRIPTION
   USE THIS FUNCTION TO SET THE DATABASE CONNECTION DETAILS
.EXAMPLE
   $SetSQLConnectionDetails = VSetSQLConnectionDetails -SQLServer {SQLSERVER VALUE} -SQLDatabase {SQLDATABASE VALUE} -SQLUsername {SQLUSERNAME VALUE} -AAM {AAM VALUE} -AppID {APPID VALUE} -Folder {FOLDER VALUE} -SafeID {SAFEID VALUE} -ObjectName {OBJECTNAME VALUE} -AIMServer {AIMSERVER VALUE}
.EXAMPLE
   $SetSQLConnectionDetails = VSetSQLConnectionDetails
.OUTPUTS
   $true if successful
   $false if failed
#>

function VSetSQLConnectionDetails{
    [CmdletBinding()]
    Param(

        [Parameter(Mandatory=$false,ValueFromPipelineByPropertyName=$true,Position=0)]
        [String]$SQLServer,

        [Parameter(Mandatory=$false,ValueFromPipelineByPropertyName=$true,Position=1)]
        [String]$SQLDatabase,

        [Parameter(Mandatory=$false,ValueFromPipelineByPropertyName=$true,Position=2)]
        [String]$SQLUsername,

        [Parameter(Mandatory=$false,ValueFromPipelineByPropertyName=$true,Position=3)]
        [String]$SQLPassword,

        [Parameter(Mandatory=$false,ValueFromPipelineByPropertyName=$true,Position=4)]
        [ValidateSet('CCP','CP','NONE')]
        [String]$AAM,

        [Parameter(Mandatory=$false,ValueFromPipelineByPropertyName=$true,Position=5)]
        [String]$AppID,

        [Parameter(Mandatory=$false,ValueFromPipelineByPropertyName=$true,Position=6)]
        [String]$Folder,

        [Parameter(Mandatory=$false,ValueFromPipelineByPropertyName=$true,Position=7)]
        [String]$SafeID,

        [Parameter(Mandatory=$false,ValueFromPipelineByPropertyName=$true,Position=8)]
        [String]$ObjectName,

        [Parameter(Mandatory=$false,ValueFromPipelineByPropertyName=$true,Position=9)]
        [String]$AIMServer,

        [Parameter(Mandatory=$false,ValueFromPipelineByPropertyName=$true,Position=10)]
        [String]$PasswordSDKPath,

        [Parameter(Mandatory=$false,ValueFromPipelineByPropertyName=$true,Position=11)]
        [Switch]$SkipConfirmation,

        [Parameter(Mandatory=$false,ValueFromPipelineByPropertyName=$true,Position=12)]
        [Switch]$NoSSL
    
    )
    
    $curUser = $env:UserName
    $ConfigFilePath = "C:\Users\$curUser\AppData\Local\VPASModuleOutputs\SQL"
    $ConfigFile = "C:\Users\$curUser\AppData\Local\VPASModuleOutputs\SQL\SQLConfigFile.txt"

    Write-Verbose "CONSTRUCTING FILEPATHS FOR SQLConfigFile"

    #FILE CREATION
    try{
        if(Test-Path -Path $ConfigFilePath){
            #DO NOTHING
            Write-Verbose "SQLConfigFile DIRECTORY EXISTS"
        }
        else{
            Write-Verbose "SQLConfigFile DIRECTORY DOES NOT EXIST...CREATING NOW"
            $MakeDirectory = New-Item -Path $ConfigFilePath -ItemType Directory
            Write-Verbose "DIRECTORY CREATED"
        }

        if(Test-Path -Path $ConfigFile){
            
            if(!$SkipConfirmation){
                write-host "SQL CONFIG FILE ALREADY EXISTS...OVERWRITE (Y/N) [Y]: " -ForegroundColor Yellow -NoNewline
                $choice = Read-Host
                if([String]::IsNullOrEmpty($choice)){$choice = "Y"}
            }
            else{
                Write-Verbose "SKIPPING CONFIRMATION FLAG PASSED...ENTERING Y"
                $choice = "Y"
            }

            if($choice -eq "Y" -or $choice -eq "y"){
                Write-Output "<#SQLConfigFile#>" | Set-Content $ConfigFile
                Write-Verbose "SQLConfigFile CREATED"
            }
            else{
                write-host "EXITING UTILITY" -ForegroundColor Red
                return $false
            }
        }
        else{
            Write-Output "<#SQLConfigFile#>" | Set-Content $ConfigFile
            Write-Verbose "SQLConfigFile CREATED"
        }
    }catch{
        Write-Host "ERROR CREATING SQLConfigFile" -ForegroundColor Red
        write-host $_ -ForegroundColor Red
        return $false
    }  

    #POPULATE FILE
    try{
        while([String]::IsNullOrEmpty($SQLServer)){
            Write-Host "ENTER FQDN OF SQL SERVER: " -ForegroundColor Yellow -NoNewline
            $SQLServer = Read-Host
        }
        Write-Output "SQLServer=$SQLServer" | Add-Content $ConfigFile
        Write-Verbose "ADDED SQLSERVER VALUE TO SQLConfigFile: $SQLServer"

        while([String]::IsNullOrEmpty($SQLDatabase)){
            Write-Host "ENTER DATABASE NAME: " -ForegroundColor Yellow -NoNewline
            $SQLDatabase = Read-Host
        }
        Write-Output "SQLDatabase=$SQLDatabase" | Add-Content $ConfigFile
        Write-Verbose "ADDED SQLDATABASE VALUE TO SQLConfigFile: $SQLDatabase"

        while([String]::IsNullOrEmpty($SQLUsername)){
            Write-Host "ENTER SQL ACCOUNT USERNAME: " -ForegroundColor Yellow -NoNewline
            $SQLUsername = Read-Host
        }
        Write-Output "SQLUsername=$SQLUsername" | Add-Content $ConfigFile
        Write-Verbose "ADDED SQLUSERNAME VALUE TO SQLConfigFile: $SQLUsername"

        while([String]::IsNullOrEmpty($AAM)){
            Write-Host "ENTER METHOD OF AAM WILL BE USED (CCP, CP, NONE): " -ForegroundColor Yellow -NoNewline
            $AAM = Read-Host
        }
        write-output "AAM=$AAM" | Add-Content $ConfigFile
        Write-Verbose "ADDED AAM VALUE TO SQLConfigFile: $AAM"

        if($AAM -eq "CCP"){
            while([String]::IsNullOrEmpty($AppID)){
                Write-Host "ENTER APPID THAT WILL RETRIEVE SQL SECRET: " -ForegroundColor Yellow -NoNewline
                $AppID = Read-Host
            }
            write-output "AppID=$AppID" | Add-Content $ConfigFile
            Write-Verbose "ADDED APPID VALUE TO SQLConfigFile: $AppID"

            while([String]::IsNullOrEmpty($Folder)){
                Write-Host "ENTER FOLDER OF SQL SECRET: " -ForegroundColor Yellow -NoNewline
                $Folder = Read-Host
            }
            write-output "Folder=$Folder" | Add-Content $ConfigFile
            Write-Verbose "ADDED FOLDER VALUE TO SQLConfigFile: $Folder"

            while([String]::IsNullOrEmpty($SafeID)){
                Write-Host "ENTER SAFEID OF SQL SECRET: " -ForegroundColor Yellow -NoNewline
                $SafeID = Read-Host
            }
            write-output "SafeID=$SafeID" | Add-Content $ConfigFile
            Write-Verbose "ADDED SAFEID VALUE TO SQLConfigFile: $SafeID"

            while([String]::IsNullOrEmpty($ObjectName)){
                Write-Host "ENTER OBJECT NAME OF SQL SECRET: " -ForegroundColor Yellow -NoNewline
                $ObjectName = Read-Host
            }
            write-output "ObjectName=$ObjectName" | Add-Content $ConfigFile
            Write-Verbose "ADDED OBJECTNAME VALUE TO SQLConfigFile: $ObjectName"

            while([String]::IsNullOrEmpty($AIMServer)){
                Write-Host "ENTER FQDN OF AIM SERVER: " -ForegroundColor Yellow -NoNewline
                $AIMServer = Read-Host
            }
            write-output "AIMServer=$AIMServer" | Add-Content $ConfigFile
            Write-Verbose "ADDED AIMSERVER VALUE TO SQLConfigFile: $AIMServer"
        }
        elseif($AAM -eq "CP"){
            while([String]::IsNullOrEmpty($AppID)){
                Write-Host "ENTER APPID THAT WILL RETRIEVE SQL SECRET: " -ForegroundColor Yellow -NoNewline
                $AppID = Read-Host
            }
            write-output "AppID=$AppID" | Add-Content $ConfigFile
            Write-Verbose "ADDED APPID VALUE TO SQLConfigFile: $AppID"

            while([String]::IsNullOrEmpty($Folder)){
                Write-Host "ENTER FOLDER OF SQL SECRET: " -ForegroundColor Yellow -NoNewline
                $Folder = Read-Host
            }
            write-output "Folder=$Folder" | Add-Content $ConfigFile
            Write-Verbose "ADDED FOLDER VALUE TO SQLConfigFile: $Folder"

            while([String]::IsNullOrEmpty($SafeID)){
                Write-Host "ENTER SAFEID OF SQL SECRET: " -ForegroundColor Yellow -NoNewline
                $SafeID = Read-Host
            }
            write-output "SafeID=$SafeID" | Add-Content $ConfigFile
            Write-Verbose "ADDED SAFEID VALUE TO SQLConfigFile: $SafeID"

            while([String]::IsNullOrEmpty($ObjectName)){
                Write-Host "ENTER OBJECT NAME OF SQL SECRET: " -ForegroundColor Yellow -NoNewline
                $ObjectName = Read-Host
            }
            write-output "ObjectName=$ObjectName" | Add-Content $ConfigFile
            Write-Verbose "ADDED OBJECTNAME VALUE TO SQLConfigFile: $ObjectName"

            while([String]::IsNullOrEmpty($PasswordSDKPath)){
                Write-Host "ENTER FULL PATH OF CLIPasswordSDK.exe (GENERALLY FOUND HERE - C:\Program Files (x86)\CyberArk\ApplicationPasswordSdk\CLIPasswordSDK.exe): " -ForegroundColor Yellow -NoNewline
                $PasswordSDKPath = Read-Host
                $PasswordSDKPath = $PasswordSDKPath -replace '"',''
            }
            write-output "PasswordSDK=$PasswordSDKPath" | Add-Content $ConfigFile
            Write-Verbose "ADDED PASSWORDSDK VALUE TO SQLConfigFile: $PasswordSDK"
        }
        else{
            while([String]::IsNullOrEmpty($SQLPassword)){
                Write-Host "ENTER PASSWORD OF SQL ACCOUNT (THIS WILL BE ENCRYPTED *MINIMALLY...CP OR CCP IS HEAVILY RECOMMENDED): " -ForegroundColor Yellow -NoNewline
                $SQLPassword = Read-Host
            }
            $encryptPass = ConvertTo-SecureString -String $SQLPassword -AsPlainText -Force | ConvertFrom-SecureString
            write-output "SQLPassword=$encryptPass" | Add-Content $ConfigFile
            Write-Verbose "ADDED PASSWORD VALUE TO SQLConfigFile: *****"
        }

    }catch{
        write-host "ERROR POPULATING SQLConfigFile" -ForegroundColor Red
        write-host $_ -ForegroundColor Red
        return $false
    }

    write-host "SQLConfigFile HAS BEEN CREATED: $ConfigFile" -ForegroundColor Cyan
    write-host "RUNNING PRECHECKS..." -ForegroundColor Cyan

    #TESTING AAM CONNECTION
    if($AAM -eq "CCP"){
        try{
            if($NoSSL){
                $uri = "http://$AIMServer/AIMWebService/api/accounts?AppID=$AppID&Safe=$SafeID&Folder=$Folder&Object=$ObjectName"
                Write-Verbose "NO SSL ENABLED, USING HTTP INSTEAD OF HTTPS"
            }
            else{
                $uri = "https://$AIMServer/AIMWebService/api/accounts?AppID=$AppID&Safe=$SafeID&Folder=$Folder&Object=$ObjectName"
                Write-Verbose "SSL ENABLED BY DEFAULT, USING HTTPS"
            }
            $CCPResult = Invoke-RestMethod -Uri $uri
            $Secret = $CCPResult.Content
            if($Secret){
                write-host "CCP TEST SUCCESSFULL" -ForegroundColor Cyan
            }
            else{
                Write-Host "FAILED TO RETRIEVE SQL SECRET...PLEASE CONFIRM SQLConfigFile ($ConfigFile) CONTENT AND CCP FUNCTIONALITY" -ForegroundColor Red
                return $false
            }
        }catch{
            Write-Host "FAILED TO RETRIEVE SQL SECRET...PLEASE CONFIRM SQLConfigFile ($ConfigFile) CONTENT AND CCP FUNCTIONALITY" -ForegroundColor Red
            write-host $_ -ForegroundColor Red
            return $false
        }
    }
    if($AAM -eq "CP"){
        try{
            $Secret = & $PasswordSDKPath GetPassword /p AppDescs.AppID=$AppID /p Query="Safe=$SafeID;Folder=$Folder;Object=$ObjectName" /o Password
            if($Secret){
                write-host "CP TEST SUCCESSFULL" -ForegroundColor Cyan
            }
            else{
                Write-Host "FAILED TO RETRIEVE SQL SECRET...PLEASE CONFIRM SQLConfigFile ($ConfigFile) CONTENT AND CP FUNCTIONALITY" -ForegroundColor Red
                return $false
            }
        }catch{
            Write-Host "FAILED TO RETRIEVE SQL SECRET...PLEASE CONFIRM SQLConfigFile ($ConfigFile) CONTENT AND CP FUNCTIONALITY" -ForegroundColor Red
            write-host $_ -ForegroundColor Red
            return $false
        }
    }
    if($AAM -eq "NONE"){
        $Secret = $SQLPassword
    }

    #TESTING SQL MODULE
    try{
        import-module sqlserver -ErrorAction Stop
        write-host "SQLServer MODULE PREREQ PASSED" -ForegroundColor Cyan
    }catch{
        write-host "FAILED TO LOAD SQLServer MODULE..." -ForegroundColor Red
        write-host $_ -ForegroundColor Red
        write-host "FAILED TO FIND SQLServer MODULE IN THE FOLLOWING DIRECTORIES:" -ForegroundColor Red
    
        $str = $env:PSModulePath -split ";"
        foreach($strsplit in $str){
            write-host $strsplit -ForegroundColor Red
        }

        write-host "DOWNLOAD THE MODULE BY TYPING IN 'Install-Module -Name SqlServer' THEN RERUN VSetSQLConnectionDetails" -ForegroundColor Red
        write-host "YOU CAN ALSO VIEW THIS LINK FOR MORE INFORMATION: 'https://www.powershellgallery.com/packages/SqlServer/21.1.18256'" -ForegroundColor Red
        write-host "PROCESS TERMINATED" -ForegroundColor Red
        return $false
    }

    #TESTING SQL CONNECTIVITY
    try{
        $output = @()
        $result = Invoke-Sqlcmd -ServerInstance $SQLServer -Database $SQLDatabase -Query "SELECT DB_NAME()" -Username $SQLUsername -Password $Secret

        if($result.Column1 -eq $SQLDatabase){
            write-host "SQL CONNECTIVITY TEST SUCCESSFUL" -ForegroundColor Cyan
        }
        else{
            Write-Host "FAILED TO CONNECT TO SQL DATABASE...PLEASE CONFIRM SQLConfigFile ($ConfigFile) CONTENT" -ForegroundColor Red
            return $false
        }
    }catch{
        Write-Host "FAILED TO CONNECT TO SQL DATABASE...PLEASE CONFIRM SQLConfigFile ($ConfigFile) CONTENT" -ForegroundColor Red
        write-host $_ -ForegroundColor Red
        return $false
    }

    return $true
}