tasks/SqlTasks.ps1


Function Invoke-SetSqlMixedModeTask {
<#
.SYNOPSIS
    Enables a SQL Server Authentication.
 
.DESCRIPTION
    The Invoke-SetSqlMixedModeTask is registered as SetSqlMixedMode task.
 
.EXAMPLE
    Json task configuration for Sitecore Install Framework:
 
    "SetDbMixedMode": {
      "Type": "SetSqlMixedMode",
      "Params": {
        "SQLServerName": "[parameter('SqlServerName')]"
      }
    }
#>

    [CmdletBinding(SupportsShouldProcess=$true)]
    param(
        [Parameter(Mandatory=$true)]
        [string]$SQLServerName
    )

    #region "Get MSSQL server instance"
    $sqlServerSmo = Get-SqlServerSmo -SQLServerName $SQLServerName
    
    if( $sqlServerSmo -eq $null )
    {
        Write-Error "Cannot find MSSQL server $SQLServerName"
        return;
    }
    #endregion

    [string]$nm = $sqlServerSmo.Name
    [string]$mode = $sqlServerSmo.Settings.LoginMode

    Write-Verbose "Instance Name: $nm, login mode $mode"

    if($pscmdlet.ShouldProcess($SQLServerName, "Set server login mode to mixed"))
    {
        #Change to Mixed Mode
        $sqlServerSmo.Settings.LoginMode = [Microsoft.SqlServer.Management.SMO.ServerLoginMode]::Mixed

        # Make the changes
        $sqlServerSmo.Alter()
    }

    if($pscmdlet.ShouldProcess($SQLServerName, "Restart"))
    {
        $service = Get-Service mssqlserver -ErrorAction SilentlyContinue

        if( $service -ne $null )
        {
            Restart-Service -Force "MSSQLSERVER"
        }

        $service = Get-Service SQLEXPRESS -ErrorAction SilentlyContinue

        if( $service -ne $null )
        {
            Restart-Service -Force "SQLEXPRESS"
        }
    }
}

<#
.SYNOPSIS
    Creates a login account on the instance of SQL Server
 
.DESCRIPTION
    The Invoke-CreateDbUserTask is registered as CreateDbUser task.
 
.EXAMPLE
    Json task configuration for Sitecore Install Framework:
 
    "CreateDatabaseUser": {
      "Type": "CreateDbUser",
      "Params": {
        "SQLServerName": "[parameter('SqlServerName')]",
        "UserName": "[parameter('SqlUser')]",
        "Password": "[parameter('SqlPassword')]"
      }
    }
#>

function Invoke-CreateSqlUserTask {
    [CmdletBinding(SupportsShouldProcess=$true)]
    param(
        [Parameter(Mandatory=$true)]
        [string]$SQLServerName,
        [Parameter(Mandatory=$true)]
        [string]$UserName,
        [Parameter(Mandatory=$true)]
        [string]$Password
    )

    Write-TaskInfo -Message "Create user $UserName on server $SQLServerName" -Tag 'MSSQL'

    $sqlServerSmo = Get-SqlServerSmo -SQLServerName $SQLServerName

    if( $sqlServerSmo -eq $null )
    {
        Write-Error "Cannot find MSSQL server $SQLServerName"
        return;
    }

    if($pscmdlet.ShouldProcess($SQLServerName, "Create user $UserName"))
    {
        $login = $sqlServerSmo.Logins[$UserName]
        if($login -eq $null)
        {
            $login = new-object Microsoft.SqlServer.Management.Smo.Login($sqlServerSmo.Name, $UserName)
            $login.LoginType = 'SqlLogin'
            $login.PasswordPolicyEnforced = $false
            $login.PasswordExpirationEnabled = $false
            $login.Create($Password)
            
        }
        else
        {
            Write-Warning "User exist: $UserName ..."
        }
    }
}

function Invoke-DeleteSqlUserTask {
    [CmdletBinding(SupportsShouldProcess=$true)]
    param(
        [Parameter(Mandatory=$true)]
        [string]$SQLServerName,
        [Parameter(Mandatory=$true)]
        [string]$UserName
    )

    Write-TaskInfo -Message "Delete user $UserName on server $SQLServerName" -Tag 'MSSQL'

    $sqlServerSmo = Get-SqlServerSmo -SQLServerName $SQLServerName

    if( $sqlServerSmo -eq $null )
    {
        Write-Error "Cannot find MSSQL server $SQLServerName"
        return;
    }

    if($pscmdlet.ShouldProcess($SQLServerName, "Delete user $UserName"))
    {
        $login = $sqlServerSmo.Logins[$UserName]
        if($login -eq $null)
        {
            Write-Warning "User $UserName not exist"
        }
        else
        {
            $login.DropIfExists()
        }
    }
}


<#
.SYNOPSIS
    Add a roles to the database user
 
.DESCRIPTION
    The Invoke-SqlSetDatabaseRolesTask function adds a roles to the specified database or databases.
 
    The Invoke-SqlSetDatabaseRolesTask is registered as SetDatabaseRoles task.
 
.EXAMPLE
    Json task configuration for Sitecore Install Framework:
 
    "AddRolesForDatabases": {
      "Type": "SetDatabaseRoles",
      "Params": {
        "SQLServerName": "[parameter('SqlServerName')]",
        "Databases": [
          "[variable('Sql.Database.Analytics')]",
          "[variable('Sql.Database.Master')]",
          "[variable('Sql.Database.Web')]"
        ],
        "Login": "[parameter('SqlUser')]",
        "Roles": [ "db_datareader", "db_datawriter", "public" ]
      }
    }
 
#>

function Invoke-SetSqlDatabaseRolesTask   {
    [CmdletBinding(SupportsShouldProcess=$true)]
    param(
        [Parameter(Mandatory=$true)]
        [string]$SQLServerName,
        [Parameter(Mandatory=$true)]
        [string[]]$Databases,
        [Parameter(Mandatory=$true)]
        [string]$Login,
        [Parameter(Mandatory=$true)]
        [string[]]$Roles
    )

    #region "Get MSSQL server instance"
    $sqlServerSmo = Get-SqlServerSmo -SQLServerName $SQLServerName

    if( $sqlServerSmo -eq $null )
    {
        Write-Error "Cannot find MSSQL server $SQLServerName"
        return;
    }
    #endregion

    if ($Login -eq "sa")
    {
        Write-Warning "The login '$Login' is the built-in sysadmin for SQL. Skip setting roles for this user."
        return
    }

    foreach( $db in $Databases )
    {
        $database = $sqlServerSmo.Databases[$db]
        $dbUser = $database.Users | Where-Object {$_.Login -eq "$Login"}
        if ($dbUser -eq $null)
        {
            Write-TaskInfo -Message "Adding user $Login in $($database.Name)" -Tag 'MSSQL'

            $dbUser = New-Object -TypeName Microsoft.SqlServer.Management.Smo.User($database, $Login)
            $dbUser.Login = $Login
            $dbUser.Create()
        }

        # Assign database roles user
        foreach ($roleName in $roles)
        {
            Write-TaskInfo -Message "Adding $roleName role for $($dbUser.Name) on $db" -Tag 'MSSQL'

            $dbrole = $database.Roles[$roleName]
            $dbrole.AddMember($dbUser.Name)
            $dbrole.Alter | Out-Null
        }
    }
}

function Invoke-AttachSqlDatabaseTask {
<#
.SYNOPSIS
    Attach a database to the MSSQL server.
 
.DESCRIPTION
    The Invoke-AttachSqlDatabaseTask attach 'DBDataFilePath' to 'SQLServerName' as 'DBName'.
 
    The Invoke-AttachSqlDatabaseTask is registered as AttachDB task.
 
.EXAMPLE
    Json task configuration for Sitecore Install Framework:
 
 "AttachCoreDatabase": {
      "Type": "AttachDB",
      "Params": {
        "SQLServerName": "[parameter('SqlServerName')]",
        "DBName": "[variable('Sql.Database.Core')]",
        "DBDataFilePath": "[variable('Sql.MDF.Core')]",
        "DBLogFilePath": "[variable('Sql.LDF.Core')]"
      }
    }
#>

    [CmdletBinding(SupportsShouldProcess=$true)]
    param(
        [Parameter(Mandatory=$true)]
        [string]$SQLServerName,
        [Parameter(Mandatory=$true)]
        [string]$DBName,
        [Parameter(Mandatory=$true)]
        [string]$DBDataFilePath,
        [Parameter(Mandatory=$true)]
        [string]$DBLogFilePath
    )

    #region "Get MSSQL server instance"
    $sqlServerSmo = Get-SqlServerSmo -SQLServerName $SQLServerName

    if( $sqlServerSmo -eq $null )
    {
        Write-Error "Cannot find MSSQL server $SQLServerName"
        return;
    }
    #endregion

    if($pscmdlet.ShouldProcess($SQLServerName, "Attach $DBDataFilePath as database $DBName"))
    {
        if ($sqlServerSmo.databases[$DBName] -eq $null)
        {
            Write-TaskInfo -Message "Attaching $DBDataFilePath to $SQLServerName as $DBName" -Tag 'MSSQL'
            Write-TaskInfo -Message "Attaching $DBLogFilePath to $SQLServerName as $DBName" -Tag 'MSSQL'

            $files = New-Object System.Collections.Specialized.StringCollection 
            $files.Add($DBDataFilePath) | Out-Null; 
            $files.Add($DBLogFilePath) | Out-Null;

            # Try attaching
            try
            {
                $sqlServerSmo.AttachDatabase($DBName, $files)
            }
            catch
            {
                Write-Error $_.Exception
            }
        }
        else
        {
            $message = "Database $DBName already exists on " + $sqlServerSmo.Name
            Write-Warning $message
        }
    }

}


function Invoke-SetSqlDatabasePermisionsTask {

[CmdletBinding(SupportsShouldProcess=$true)]
    param(
        [Parameter(Mandatory=$true)]
        [string]$SQLServerName,
        [Parameter(Mandatory=$true)]
        [string[]]$Databases,
        [Parameter(Mandatory=$true)]
        [string]$UserName
    )

    #region "Get MSSQL server instance"
    $sqlServerSmo = Get-SqlServerSmo -SQLServerName $SQLServerName

    if( $sqlServerSmo -eq $null )
    {
        Write-Error "Cannot find MSSQL server $SQLServerName"
        return;
    }
    #endregion

    foreach( $db in $Databases )
    {
        $database = $sqlServerSmo.Databases[$db]
        if ($UserName -eq "sa")
        {
            Write-Warning "The login '$UserName' is the built-in sysadmin for SQL. Skip setting roles for this user."
            return
        }

        $dbUser = $database.Users | Where-Object {$_.Login -eq "$UserName"}

        if ($dbUser -eq $null)
        {
            Write-Warning "Could not find a user '$UserName' for the login . Cannot grant permissions."
            return
        }
    
        $permset = New-Object Microsoft.SqlServer.Management.Smo.DatabasePermissionSet 
        $permset.Execute = $true
        $database.Grant($permset, $UserName)
        $database.Alter();

        $message = "Granted Execute permission to $UserName on $db" 
        Write-TaskInfo -Message $message -Tag 'MSSQL'
    }
}


function Invoke-DeleteSqlDatabaseTask {
<#
.SYNOPSIS
    Removes a SQL databases on server
 
.DESCRIPTION
    The Invoke-DeleteSqlDatabaseTask function removes a databases on MSSQL server.
 
    The Invoke-DeleteSqlDatabaseTask is registered as SqlDeleteDatabase task.
 
.EXAMPLE
Json task configuration for Sitecore Install Framework
"DeleteDatabases": {
      "Type": "DeleteSqlDatabase",
      "Params": {
        "SQLServerName": "[parameter('SqlServerName')]",
        "Databases": [
          "[variable('Sql.Database.Analytics')]",
          "[variable('Sql.Database.Master')]",
          "[variable('Sql.Database.Web')]"
        ],
    }
}
 
#>

    [CmdletBinding(SupportsShouldProcess=$true)]
    param(
        [Parameter(Mandatory=$true)]
        [string]$SQLServerName,
        [Parameter(Mandatory=$true)]
        [string[]]$Databases
    )

    #region "Get MSSQL server instance"
    $sqlServerSmo = Get-SqlServerSmo -SQLServerName $SQLServerName

    if( $sqlServerSmo -eq $null )
    {
        Write-Error "Cannot find MSSQL server $SQLServerName"
        return;
    }
    #endregion

    foreach( $database in $Databases )
    {
        if($pscmdlet.ShouldProcess($SQLServerName, "Remove $database"))
        {
            if ($sqlServerSmo.databases[$database] -ne $null)
            {
                Write-TaskInfo -Message "Remove $database" -Tag 'MSSQL'

                Invoke-SQLcmd -ServerInstance $SQLServerName -Query ("EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'" + $database + "'")
                Invoke-SQLcmd -ServerInstance $SQLServerName -Query ("DROP DATABASE [" + $database + "]")
            }
            else
            {
                $message = "Database $database not exists on $SQLServerName"
                Write-Warning $message
            }
        }
    }
}


Export-ModuleMember Invoke-SetSqlMixedModeTask
Export-ModuleMember Invoke-AttachSqlDatabaseTask
Export-ModuleMember Invoke-DeleteSqlDatabaseTask
Export-ModuleMember Invoke-SetSqlDatabaseRolesTask
Export-ModuleMember Invoke-SetSqlDatabasePermisionsTask
Export-ModuleMember Invoke-CreateSqlUserTask
Export-ModuleMember Invoke-DeleteSqlUserTask