D365FOLBDAdmin

2.1.38.0

functions/New-D365MSSQLSelfCert.ps1

function New-D365MSSQLSelfCert {
    <#
    .SYNOPSIS
 
   .DESCRIPTION
  
   .EXAMPLE
 
   .EXAMPLE
 
    .EXAMPLE
 
   .PARAMETER ComputerName
   String
   The name of the D365 LBD Server to grab the environment details; needed if a config is not specified and will default to local machine.
   .PARAMETER Config
    Custom PSObject
    Config Object created by either the Get-D365LBDConfig or Get-D365TestConfigData function inside this module
   #>
    [alias("New-MSSQLSelfCert")]
    [CmdletBinding()]
    param([Parameter(ValueFromPipeline = $True,
            ValueFromPipelineByPropertyName = $True,
            Mandatory = $false,
            HelpMessage = 'D365FO Local Business Data Server Name',
            ParameterSetName = 'NoConfig')]
        [PSFComputer]$ComputerName = "$env:COMPUTERNAME",
        [Parameter(ParameterSetName = 'Config',
            ValueFromPipeline = $True)]
        [psobject]$Config,
        [string]$CertPassword
    )
    ##Gather Information from the Dynamics 365 Orchestrator Server Config
    BEGIN {
    } 
    PROCESS {
        if (!$Config -or $Config.OrchestratorServerNames.Count -eq 0) {
            Write-PSFMessage -Level VeryVerbose -Message "Config not defined or Config is invalid. Trying to Get new config using $ComputerName"
            $Config = Get-D365LBDConfig -ComputerName $ComputerName -HighLevelOnly
        }
        ##
        <# Source: https://stackoverflow.com/questions/8423541/how-do-you-run-a-sql-server-query-from-powershell
#>
        function Invoke-SQL {
            param(
                [string] $dataSource = ".\SQLEXPRESS",
                [string] $database = "MasterData",
                [string] $sqlCommand = $(throw "Please specify a query.")
            )

            $connectionString = "Data Source=$dataSource; " +
            "Integrated Security=SSPI; " +
            "Initial Catalog=$database"

            $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
            $command = new-object system.data.sqlclient.sqlcommand($sqlCommand, $connection)
            $connection.Open()
    
            $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
            $dataset = New-Object System.Data.DataSet

            $adapter.Fill($dataSet) | Out-Null
            $connection.Close()
            $dataSet.Tables
        }

        $Listener = $Config.AXDatabaseServer

        foreach ($SQLServer in $Config.DatabaseClusterServerNames) {
            try {
                $InstanceNameSQLResults = Invoke-SQL -dataSource $sqlserver -database 'master' -sqlCommand 'SELECT @@SERVICENAME as ''Servicename'' '
            }
            catch {}
            try {
                $ProductVersionSQLResults = Invoke-SQL -dataSource $sqlserver -database 'master' -sqlCommand 'SELECT SERVERPROPERTY(''Productversion'') as ''Productversion'' '
                [string]$SQLMajorVersionNumber = $($ProductVersionSQLResults | select Productversion).Productversion
                $SQLMajorVersionNumber = $SQLMajorVersionNumber.Substring(0, 2)
            }
            catch {}
            if (!$InstanceNameSQLResults) {
                Write-PSFMessage -Level Error -Message "Check SQL DB Permissions"
            }

            $InstanceName = $($InstanceNameSQLResults | Select Servicename).Servicename
            $SQLVersionandInstance = 'MSSQL' + $SQLMajorVersionNumber + '.' + $InstanceName

            try {
                $SQLCert = $null
                $SQLCert = Invoke-Command -ScriptBlock {
                    if (!$SQLVersionandInstance) {
                        $SQLVersionandInstance = $using:SQLVersionandInstance
                    }
                    $cert = Get-ItemProperty -Path "Registry::HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\$SQLVersionandInstance\MSSQLSERVER\SuperSocketNetLib"
                    $cert.Certificate.ToUpper()
                } -ComputerName $SQLServer -ErrorAction Stop
            }
            catch {
                $WhoAmI = whoami
                Write-PSFMessage -Level Warning -Message "Warning: Can't Connect to $SQLServer registry with account $WhoAmI to gather SQL Certificate Encryption Details"
            }
            Write-PSFMessage -Level Verbose -Message "$SQLServer is currently using $SQLCert"

            $NewCert = Invoke-Command -ScriptBlock {
                $ComputerName = $env:COMPUTERNAME.ToLower()
                $Domain = $env:USERDNSDOMAIN.ToLower()
                $ListenerName = $using:Listener
                $NewCertInside = New-SelfSignedCertificate -Subject "$ComputerName.$Domain" -DnsName "$ListenerName.$Domain", $Listener, $ComputerName -Provider 'Microsoft Enhanced RSA and AES Cryptographic Provider'

                if (!(test-path -PathType Container "C:\certs")) {
                    mkdir "C:\certs"
                }
                $Thumbprint = $NewCertInside.Thumbprint
                $CertSecurePass = ConvertTo-SecureString -String $using:CertPassword -AsPlainText -Force
                $CertInsideLocalStore = Get-ChildItem -path Cert:\LocalMachine\My\$Thumbprint
                Export-PfxCertificate -Cert $CertInsideLocalStore -FilePath "C:\certs\$($CertInsideLocalStore.Thumbprint).pfx" -Force -Verbose -Password $CertSecurePass
                $CertInsideLocalStore
            } -ComputerName $SQLServer
            if (!(test-path -PathType Container "C:\certs")) {
                mkdir "C:\certs"
            }
            [string]$NewCertThumbprint = $($NewCert.Thumbprint)
            $NewCertThumbprint = $NewCertThumbprint.Trim()
            $Source = "\\$SqlServer\C$\certs\" + $NewCertThumbprint + ".pfx"
            $Destination = "C:\certs\" + $NewCertThumbprint + ".pfx"
            Copy-Item $Source -Destination $Destination -Verbose
        }
    }
    END {}
}