DSCResources/ROACommon/Src/Get-ROAEnvironment.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
function Get-ROAEnvironment {
<#
    .SYNOPSIS
        Retrieves a RES ONE Automation environment GUID from the database.
    .NOTES
        This cmdlet currently only support Microsoft SQL servers.
#>

    [CmdletBinding(DefaultParameterSetName = 'SQLAuth')]
    [OutputType([System.Guid])]
    param (
        # Database server hosting the RES ONE Automation database
        [Parameter(Mandatory, ValueFromPipeline, ValueFromPipelineByPropertyName)]
        [ValidateNotNullOrEmpty()]
        [System.String] $Server,

        # Name of the RES ONE Automation database/instance
        [Parameter(Mandatory, ValueFromPipelineByPropertyName)]
        [ValidateNotNullOrEmpty()]
        [System.String] $Database,

        # SQL authentication username and password
        [Parameter(Mandatory, ParameterSetName = 'SQLAuth')]
        [ValidateNotNull()]
        [System.Management.Automation.PSCredential]
        [System.Management.Automation.Credential()] $Credential,

        # Use the current Windows credentials for authentication
        [Parameter(ParameterSetName = 'WindowsAuth')]
        [System.Management.Automation.SwitchParameter]
        $UseWindowsAuthentication
    )
    process {

        if ($PSCmdlet.ParameterSetName -eq 'SQLAuth') {

            $sqlConnectionString = 'Data Source={0};Initial Catalog={1};User Id={2};Password={3};' -f $Server, $Database, $Credential.UserName, $Credential.GetNetworkCredential().Password;
        }
        else {

            $sqlConnectionString = 'Data Source={0};Initial Catalog={1};Integrated Security=SSPI;' -f $Server, $Database;
        }

        try {

            $sqlConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection -Property @{ 'ConnectionString' = $sqlConnectionString; };
            $sqlConnection.Open();

            $sqlCommand = $sqlConnection.CreateCommand();
            $sqlCommand.CommandText = 'SELECT strValue AS License FROM tblSettings WHERE lngSetting = 6';

            $sqlDataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $sqlCommand;
            $dataSet = New-Object System.Data.DataSet;
            [ref] $null = $sqlDataAdapter.Fill($dataSet);

            $sqlConnection.Close();
            $roaEnvironmentGuid = New-Object -TypeName System.Guid($dataSet.Tables[0].License);
            return $roaEnvironmentGuid;

        }
        catch {

            throw;
        }

    } #end process
} #end function Get-ROAEnvironment