Examples/Resources/xSQLServerScript/2-RunScriptUsingWindowsAuthentication.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
<#
.EXAMPLE
    These two example shows how to run SQL script using Windows Authentication.
    First example shows how the resource is run as account SYSTEM. And the second example shows how the resource is run with a user account.
#>


Configuration Example
{
    param(
        [Parameter(Mandatory = $true)]
        [PSCredential]
        $WindowsCredential
    )

    Import-DscResource -ModuleName xSQLServer

    Node localhost
    {
        xSQLServerScript 'RunSQLScript-AsSYSTEM'
        {
            ServerInstance = 'localhost\SQL2016'

            SetFilePath    = 'C:\DSCTemp\SQLScripts\Set-RunSQLScript-AsSYSTEM.sql'
            TestFilePath   = 'C:\DSCTemp\SQLScripts\Test-RunSQLScript-AsSYSTEM.sql'
            GetFilePath    = 'C:\DSCTemp\SQLScripts\Get-RunSQLScript-AsSYSTEM.sql'
            Variable       = @("FilePath=C:\temp\log\AuditFiles")
        }

        xSQLServerScript 'RunSQLScript-AsUSER'
        {
            ServerInstance       = 'localhost\SQL2016'

            SetFilePath          = 'C:\DSCTemp\SQLScripts\Set-RunSQLScript-AsUSER.sql'
            TestFilePath         = 'C:\DSCTemp\SQLScripts\Test-RunSQLScript-AsUSER.sql'
            GetFilePath          = 'C:\DSCTemp\SQLScripts\Get-RunSQLScript-AsUSER.sql'
            Variable             = @("FilePath=C:\temp\log\AuditFiles")

            PsDscRunAsCredential = $WindowsCredential
        }

        xSQLServerScript 'RunSQLScript-With30SecondTimeout'
        {
            ServerInstance       = 'localhost\SQL2016'

            SetFilePath          = 'C:\DSCTemp\SQLScripts\Set-RunSQLScript-WithQueryTimeout.sql'
            TestFilePath         = 'C:\DSCTemp\SQLScripts\Test-RunSQLScript-WithQueryTimeout.sql'
            GetFilePath          = 'C:\DSCTemp\SQLScripts\Get-RunSQLScript-WithQueryTimeout.sql'
            QueryTimeout         = 30
            Variable             = @("FilePath=C:\temp\log\AuditFiles")

            PsDscRunAsCredential = $WindowsCredential
        }
    }
}