function/shared-function.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
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
<#
.Synopsis
   Connect to SQL Server
.DESCRIPTION
   Function can connect to SQL server with authetication Windows
.EXAMPLE
   Example of how to use this cmdlet
.EXAMPLE
   Another example of how to use this cmdlet
.LINK
   Author: Mateusz Nadobnik
   Link: mnadobnik.pl
   Date: 16.07.2017
   Version: 1.0.0.9
 
   Keywords: Shared function, Version, SQL Server
   Notes: 1.0.0.4 - Without change.
          1.0.0.6 - Repaired syntax
          1.0.0.9 - Added SQL Server 2017 to Get-SQLServerFullName function
          1.1.0.1 - Added parameter - SqlCredential
#>


Function Get-SQLServerFullName($param)
{
    switch ($param)
    {
        9 { return "SQL Server 2005" }
        10 { return "SQL Server 2008" }
        10.50 { return "SQL Server 2008 R2" }
        11 { return "SQL Server 2012" }
        12 { return "SQL Server 2014" }
        13 { return "SQL Server 2016" }
        14 { return "SQL Server 2017" }
        15 { return "SQL Server 2019" }
    }
}

function Get-SqlServerVersion
{
    [CmdletBinding()]
    Param
    (
        # Param1 help description
        [Parameter(Mandatory = $true, ValueFromPipeline = $true)]
        [string]$ServerInstance,
        [Parameter()]
        [PSCredential]$SqlCredential
    )

    Begin
    {

    }
    Process
    {
        try
        {
            $connectsqlserver = New-Object Microsoft.SqlServer.Management.Smo.Server $ServerInstance
            $connectsqlserver.ConnectionContext.ApplicationName = "SqlServerUpdatesModule"
            $connectsqlserver.ConnectionContext.ConnectTimeout = 10

            Write-Verbose "Connect to server $ServerInstance"
            if ($connectsqlserver.ConnectionContext.IsOpen -eq $false)
            {

                if ($null -ne $SqlCredential)
                {
                    $username = ($SqlCredential.UserName).TrimStart("\")

                    # support both ad\username and username@ad
                    if ($username -like "*\*" -or $username -like "*@*")
                    {
                        if ($username -like "*\*")
                        {
                            $domain, $login = $username.Split("\")
                            if ($domain)
                            {
                                $formatteduser = "$login@$domain"
                            }
                            else
                            {
                                $formatteduser = $username.Split("\")[1]
                            }
                        }
                        else
                        {
                            $formatteduser = $SqlCredential.UserName
                        }

                        $connectsqlserver.ConnectionContext.LoginSecure = $true
                        $connectsqlserver.ConnectionContext.ConnectAsUser = $true
                        $connectsqlserver.ConnectionContext.ConnectAsUserName = $formatteduser
                        $connectsqlserver.ConnectionContext.ConnectAsUserPassword = ($SqlCredential).GetNetworkCredential().Password
                    }
                    else
                    {
                        $connectsqlserver.ConnectionContext.LoginSecure = $false
                        $connectsqlserver.ConnectionContext.set_Login($username)
                        $connectsqlserver.ConnectionContext.set_SecurePassword($SqlCredential.Password)
                    }
                }
                else
                {
                    $connectsqlserver.ConnectionContext.LoginSecure = $true
                }
                Write-Verbose "[Get-SqlServerVersion] ConnectionString:$($connectsqlserver.ConnectionContext)"
                $connectsqlserver.ConnectionContext.Connect()
            }

            $connectsqlserver | Select-Object Name, Product, Edition, ProductLevel, VersionMajor,
            @{L = "VersionName"; E = { Get-SQLServerFullName $_.versionmajor } }, @{L = "Build"; E = { $_.VersionString } }

        }
        catch
        {
            Write-Debug -Message $_.Exception
            Write-Output $_.Exception.Message -ForegroundColor Yellow
        }
    }
    End
    {
        Write-Verbose "The disconnect connection with $ServerInstance"
        try
        {
            if ($connectsqlserver.ConnectionContext.IsOpen -eq $true)
            {
                $connectsqlserver.ConnectionContext.Disconnect()
            }
        }
        catch
        {
            Write-Output $_.Exception.Message -ForegroundColor Yellow
        }
    }
}