functions/Add-DbrCredential.ps1

Function Add-DbrCredential
{
<#
.SYNOPSIS
Adds a credential and proxy for SQL Agent Jobs to the dbareports server

.DESCRIPTION
This will create a credential and a proxy on the dbareports server using the install configuration and add the PowerShell and CmdExec subsystems

.PARAMETER JobCredential
The Username and password for the credential in a PSCredential object. Will be prompted if it does not exist

.PARAMETER CredentialName
The name of the credential Defaults to 'DBAreports',

.PARAMETER ProxyName
The name of the proxy. Defaults to 'PowerShell Proxy Account for dbareports'

.NOTES
dbareports PowerShell module (https://dbareports.io, SQLDBAWithABeard.com)
Copyright (C) 2016 Rob Sewell

This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with this program. If not, see <http://www.gnu.org/licenses/>.

.LINK
https://dbareports.io/functions/Add-DbrCredential

.EXAMPLE
Add-DbrCredential -CredentialName TheBeard -ProxyName Rob

This will create a credential called TheBeard and a Proxy named Rob for that credential and add the PowerShell and CmdExec subsystems
#>

    [CmdletBinding()]
    Param (
        [object]$JobCredential,
        [string]$CredentialName = 'DBAreports',
        [string]$ProxyName = 'PowerShell Proxy Account for dbareports'
    )
    
    PROCESS
    {
        
        Get-Config
        $SqlServer = $script:SqlServer
        $InstallDatabase = $script:InstallDatabase
        $SqlCredential = $script:SqlCredential
        
        if ($SqlServer.length -eq 0)
        {
            throw "No config file found. Have you installed dbareports? Please run Install-DbaReports or Install-DbaReportsClient"
        }
        
        $sourceserver = Connect-SqlServer -SqlServer $sqlserver -SqlCredential $SqlCredential
        $source = $sourceserver.DomainInstanceName
        $jobserver = $sourceserver.JobServer
        
        if ($JobCredential -eq $null)
        {
            $msg = "Enter the credential username and password for a Windows account that has permissions on all servers."
            $JobCredential = $Host.UI.PromptForCredential("Credential username and password", $msg, "$env:userdomain\$env:username", $null)
        }
        
        if ($JobCredential -eq $null) { return }
        
        $username = $JobCredential.UserName
        $password = $JobCredential.Password
        
        if ($username -notmatch '\\')
        {
            throw "Username must be a Windows domain account that can authenticate to other servers. Cannot add $username. Quitting."
        }
        
        $login = $sourceserver.Logins[$username]
        
        if ($login -eq $null)
        {
            try
            {
                $title = "$username does not exist on $sqlserver."
                $message = "Adding now as non-privileged login. Continue? (Y/N)"
                $yes = New-Object System.Management.Automation.Host.ChoiceDescription "&Yes", "Will continue"
                $no = New-Object System.Management.Automation.Host.ChoiceDescription "&No", "Will exit"
                $options = [System.Management.Automation.Host.ChoiceDescription[]]($yes, $no)
                $result = $host.ui.PromptForChoice($title, $message, $options, 0)
                
                if ($result -eq 1)
                {
                    return "FINE!"
                }
                
                $login = New-Object Microsoft.SqlServer.Management.Smo.Login($sourceserver, $username)
                $login.LoginType = "WindowsUser"
                If ($Pscmdlet.ShouldProcess($source, "Adding login $username to Server"))
                {
                    $login.Create()
                }
                
                Write-Output "Created login for $username"
            }
            catch
            {
                Write-Exception $_
                throw "Cannot create login for $username. Quitting."
            }
        }
        
        $sqlcredential = $sourceserver.Credentials[$CredentialName]
        
        if ($SqlCredential -eq $null)
        {
            try
            {
                $sqlcredential = New-Object Microsoft.SqlServer.Management.SMO.Credential($source, $CredentialName)
                $SqlCredential.Identity = $username
                
                If ($Pscmdlet.ShouldProcess($source, "Adding Credential $username to Server"))
                {
                    $sqlcredential.Create()
                }
                
                Write-Output "Created credential for $username as $CredentialName"
            }
            catch
            {
                Write-Exception $_
                throw "Cannot create credential for $username. Quitting."
            }
        }
        else
        {
            Write-Output "Credential '$CredentialName' already exists."
        }
        
        $proxyaccount = $sourceserver.JobServer.ProxyAccounts[$ProxyName]
        
        if ($proxyaccount -ne $null)
        {
            if (!$login.IsMember("sysadmin") -and $proxyaccount.CredentialIdentity -ne $username)
            {
                try
                {
                    $proxy.AddLogin($username)
                    $proxy.Alter()
                    
                    Write-Output "Added username $username to '$ProxyName' logins"
                }
                catch
                {
                    Write-Exception $_
                    throw "Cannot add login to proxy account."
                }
            }
        }
        
        if ($sourceserver.JobServer.ProxyAccounts[$ProxyName] -ne $null)
        {
            Write-Output "Proxy account already exists! Nothing left to do."
            return
        }
        
        try
        {
            $proxy = New-Object Microsoft.SqlServer.Management.Smo.Agent.ProxyAccount($jobserver, $ProxyName)
            $proxy.credentialName = $CredentialName
            $proxy.Description = "This account is used to execute PowerShell scripts locally and was created by dbareports."
            $proxy.isEnabled = $true
            $proxy.Create()
            $proxy.AddLogin($username)
            
            Write-Output "Created Proxy Account for credential as '$ProxyName'"
        }
        catch
        {
            Write-Exception $_
            throw "Cannot create proxy for credential '$CredentialName'. Quitting."
        }
        
        # Add both subsystems just in case
        $pssubsystem = [Microsoft.SqlServer.Management.Smo.Agent.AgentSubSystem]::PowerShell
        $cmdsubsystem = [Microsoft.SqlServer.Management.Smo.Agent.AgentSubSystem]::CmdExec
        try
        {
            $proxy.AddSubSystem($pssubsystem)
            $proxy.AddSubSystem($cmdsubsystem)
            
            Write-Output "Added PowerShell and CmdExec subsystems to '$ProxyName'"
        }
        catch
        {
            Write-Exception $_
            throw "Cannot add PowerShell and CmdExec subsystems to proxy account. Quitting."
        }
        
        $sourceserver.ConnectionContext.Disconnect()
    }
}