Sql Server Powershell

November 3, 2020 Scott Newman Powershell, SQL Server

Set Sql Server\Agent Service Account

This uses a CIMMethod to change the service accounts. The Wmi.ManagedComputer method is also included in the comments below…but I’ve found this to be not reliable at times.

 cls


[securestring]$mssqlpwd = ConvertTo-SecureString "yomamma" -AsPlainText -Force
[PSCredential]$mssqlCred = New-Object System.Management.Automation.PSCredential ("$env:userdomain\sqlserver", $mssqlpwd)

[securestring]$agentPwd = ConvertTo-SecureString "wearsarmyboots" -AsPlainText -Force
[PSCredential]$agentCred = New-Object System.Management.Automation.PSCredential ("$env:userdomain\sqlagent", $agentPwd)

Invoke-Command -ComputerName 'ComputerName' -ScriptBlock{

    $mssqlCred = $using:mssqlCred 
    $agentCred = $using:agentCred

    try{

        $ss = Get-CimInstance Win32_Service -Filter 'Name="sqlserveragent"'
        $ss | Invoke-CimMethod -MethodName Change -Arguments @{StartName=$agentCred.UserName;StartPassword=$agentCred.GetNetworkCredential().Password}

        $ss = Get-CimInstance Win32_Service -Filter 'Name="mssqlserver"'
        $ss | Invoke-CimMethod -MethodName Change -Arguments @{StartName=$mssqlCred.UserName;StartPassword=$mssqlCred.GetNetworkCredential().Password}

        <#
        [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null
        $srv = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $env:COMPUTERNAME
    
        $service = $srv.Services | Where-Object{$_.name -eq 'sqlserveragent'}
        $service.SetServiceAccount($agentCred.UserName, $agentCred.GetNetworkCredential().Password);
        Restart-Service -name sqlserveragent -Force

        $service = $srv.Services | Where-Object{$_.name -eq 'mssqlserver'}
        $service.SetServiceAccount($mssqlCred.UserName, $mssqlCred.GetNetworkCredential().Password);
        Restart-Service -name mssqlserver -Force
        #>
    }
    catch{
        throw $_ | fl -Force | Out-String
    }

    Get-Service mssqlserver | Start-Service 
    Get-Service sqlserveragent | Start-Service 


}

November 3, 2020 Scott Newman Powershell

Create PSCredential Object

[SecureString]$pwd = ConvertTo-SecureString '**P@55w0rd**' -AsPlainText -Force
[PSCredential]$cred = New-Object System.Management.Automation.PSCredential ('user', $pwd)

October 29, 2020November 3, 2020 Scott Newman Powershell

Create Scheduled Tasks

 $taskName = "Immediately"
Unregister-ScheduledTask -TaskName $taskName -Confirm:$false -ErrorAction SilentlyContinue

$action = New-ScheduledTaskAction -Execute 'powershell.exe' -Argument "C:\PatchComputer.ps1"
$trigger = New-ScheduledTaskTrigger -Once -At (get-date).AddSeconds(2)
$principal = New-ScheduledTaskPrincipal -UserID "NT AUTHORITY\SYSTEM" -LogonType ServiceAccount -RunLevel Highest 

Register-ScheduledTask -Action $action -Trigger $trigger -Principal $principal -TaskName $taskName -Description "Test" 


$taskName = "AtStartup"
Unregister-ScheduledTask -TaskName $taskName -Confirm:$false -ErrorAction SilentlyContinue
Unregister-ScheduledTask -TaskName $taskName -Confirm:$false -ErrorAction SilentlyContinue
$action = New-ScheduledTaskAction -Execute 'powershell.exe' -Argument "C:\PatchComputer.ps1"
$trigger = New-ScheduledTaskTrigger -AtStartup
$principal = New-ScheduledTaskPrincipal -UserID "NT AUTHORITY\SYSTEM" -LogonType ServiceAccount -RunLevel Highest 

Register-ScheduledTask -Action $action -Trigger $trigger -Principal $principal -TaskName $taskName -Description "Test"

October 28, 2020November 3, 2020 Scott Newman Powershell

Get WebResponse Error from Invoke-WebRequest

 $Request = $_.Exception
Write-host "Exception caught: $Request"
if ($_.Exception.Response.StatusCode.value__) {
    $RespStatusCode = ($_.Exception.Response.StatusCode.value__ ).ToString().Trim();
    Write-Host $RespStatusCode;
}
if ($_.Exception.Message) {
    $RespMessage = ($_.Exception.Message).ToString().Trim();
    Write-Host $RespMessage;
}
$stream = $_.Exception.Response.GetResponseStream()
$reader = New-Object System.IO.StreamReader($stream)
Write-Host $reader.ReadToEnd()

October 22, 2019 Scott Newman Uncategorized

Connect to Sql Server under a different login context

Here’s how to connect to sql server under a different windows login context. If you pass in the login name as ‘domain\loginname’ it will indeed fail. Only pass in the login name.

 #omit the domain name in the login, or it will fail
$cred = Get-Credential -Message "Enter your login name" -UserName $env:USERNAME

try{

    $srv = New-Object Microsoft.sqlServer.Management.smo.Server "ServerName"

    $srv.ConnectionContext.LoginSecure = $true
    $srv.ConnectionContext.ConnectAsUser = $true

    $srv.ConnectionContext.ConnectAsUserName = $cred.username 
    $srv.ConnectionContext.ConnectAsUserPassword = $cred.GetNetworkCredential().Password 
    $srv.ConnectionContext.Connect() 
    $srv.ConnectionContext.ExecuteWithResults("SELECT SUSER_SNAME() AS CurrentUser").Tables[0] | ft -AutoSize
}
catch{
    $_ | fl -Force
}
finally{
    $srv.ConnectionContext.Disconnect();
}

February 12, 2019 Scott Newman Powershell, SQL Server

Enable Sql AlwaysOn

This always seems to give me trouble, and I always forget the manner in which I run this that works correctly, so, viola!

Invoke-Command -ComputerName $name -ScriptBlock{
        Import-Module SqlPS -DisableNameChecking

        $srv = New-Object Microsoft.SqlServer.Management.smo.Server "."
        Enable-SqlAlwaysOn -InputObject $srv -Force
        $srv.ConnectionContext.Disconnect()
    }

November 27, 2018November 28, 2018 Scott Newman Powershell, SQL Server

Restore Database with SMO

Probably 3 or 4 versions of this already on here. So here is one more.

function Invoke-Restore{
    [cmdletbinding()]
    param(
        [Parameter(Mandatory)]
        [string]$ServerName,
        [Parameter(Mandatory)]
        [int]$Port,
        [string]$DatabaseName = $null,
        [string]$BackupFile = "c:\PathToBackupFile.bak",
        [string]$dataDir = "d:\mssql\data",
        [string]$logDir = "d:\mssql\tranlog",
        [switch]$Force
    )

    [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
    [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")
    [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
    [void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")
 

    try{
        $srv = New-Object Microsoft.SqlServer.Management.Smo.Server "$ServerName,$Port"
        $backup = New-Object Microsoft.SqlServer.Management.Smo.BackupDeviceItem ($BackupFile, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
         
        $restore = New-Object Microsoft.SqlServer.Management.Smo.Restore
        $restore.Devices.Add($backup);
        $restore.NoRecovery = $false;
        $restore.ReplaceDatabase = $true
 
        $dbFiles = $restore.ReadFileList($srv);
        $header = $restore.ReadBackupHeader($srv)
        if([System.String]::IsNullOrWhiteSpace($DatabaseName)){
            $DatabaseName = $header.DatabaseName
        }
        $restore.Database = $DatabaseName
        if($Force -and $srv.Databases[$DatabaseName]){
            $srv.KillDatabase($DatabaseName);
        }

        foreach($dbFile in $dbFiles){
            $fileName = [System.IO.Path]::GetFileName($dbFile.PhysicalName);
            switch($dbFile.Type){
                'D'{
                    [void]$restore.RelocateFiles.Add((New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($dbFile.LogicalName, "$dataDir\$fileName")))
                }
                'L'{
                    [void]$restore.RelocateFiles.Add((New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($dbFile.LogicalName, "$logDir\$fileName")))
                }
            }
        }
    
        $srv.ConnectionContext.StatementTimeout = 0;
        $restore.SqlRestore($srv);
        $srv.ConnectionContext.Disconnect();

    }
    catch{
        $_ | fl -Force
    }

}

November 13, 2018 Scott Newman AlwaysOn, Powershell, SQL Server

Add an AvailabilityDatabase

function Add-AvailabilityGroupDatabase{
    param(
        $ServerName,
        $Port = 1433,
        $DatabaseName,
        $AvailabilityGroup
    )
    begin{

    }
    process{

        if(!$ServerName.Contains('.')){
            $ServerName = "$ServerName.$env:USERDNSDOMAIN"
        }
        $srv = new-object Microsoft.SqlServer.Management.smo.Server "$ServerName,$Port"
        $ServerName = $srv.AvailabilityGroups[$AvailabilityGroup].PrimaryReplicaServerName
        $srv.ConnectionContext.Disconnect()
        $srv = new-object Microsoft.SqlServer.Management.smo.Server "$($ServerName).$env:USERDNSDOMAIN,$Port"

        if(!$srv.Databases[$DatabaseName]){
            throw "Could not find a database $DatabaseName on primary replica $ServerName"
        }

        if(!$srv.AvailabilityGroups[$AvailabilityGroup].AvailabilityDatabases.Contains($DatabaseName)){
            [void]$srv.ConnectionContext.ExecuteNonQuery("RESTORE DATABASE [$DatabaseName] WITH RECOVERY;");
            $ADB = New-Object Microsoft.SqlServer.Management.smo.AvailabilityDatabase($srv.AvailabilityGroups[$AvailabilityGroup], $DatabaseName);
            $ADB.Create();
        }
        else{
            Write-Warning "Database $DatabaseName is already an availbility database on server $ServerName."
        }
        
        
        $srv.AvailabilityGroups[$AvailabilityGroup].AvailabilityReplicas | ?{$_.Name -ne $ServerName} | select -expandproperty Name | %{
            $name = $_ 
            try{
                $sec = new-object Microsoft.SqlServer.Management.smo.Server "$($name).$env:USERDNSDOMAIN,$Port"
                $ag = $sec.AvailabilityGroups[$AvailabilityGroup]
                if(!$ag.AvailabilityDatabases[$DatabaseName].IsJoined){
                    $ag.AvailabilityDatabases[$DatabaseName].JoinAvailablityGroup();
                }
                else{
                    Write-Warning "Database $DatabaseName is already an availbility database on server $name."
                }
            }
            catch{
                Write-Error $_ | fl -Force
            }
            finally{
                $sec.ConnectionContext.Disconnect();
            }
        }


        $srv.ConnectionContext.Disconnect()

    }
    end{

    }
}

April 18, 2018 Scott Newman Powershell, SQL Server, Uncategorized

Capture Sql Server Information Messages in Powershell

Sometimes you have a need to capture the informational messages generated in sql server, for example sp_help_revlogin only prints the user information to the messages tab.
Import-Module SqlServer -DisableNameChecking cls try {


    $users = @('user1', 'user2')
    $users | % {

        $user = $_ 
        $sqlConn = New-Object System.Data.SqlClient.SqlConnection "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=servername"

        $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {

            param(

                $sender,

                $event

            ) 
            Write-host $event.Message 
        };

        $sqlConn.add_InfoMessage($handler);

        $sqlConn.FireInfoMessageEventOnUserErrors = $true;

        $sqlConn.Open();

        $sqlCmd = New-Object System.Data.SqlClient.SqlCommand("EXEC sp_help_revlogin '$user', NULL", $sqlConn);

        $sqlcmd.CommandType = [System.Data.CommandType]::Text

        [void]$sqlCmd.ExecuteNonQuery();

    }

} catch { $_ | fl -Force } finally { $sqlConn.Close(); }

November 6, 2017 Scott Newman Powershell, SQL Server, Uncategorized

Stop/Disable SqlAgent/MSSQLServer service via Powershell

One-liner to stop and disable the sqlagent and msssqlserver service.

Get-Service MSSQLSERVER, SQLSERVERAGENT | Stop-Service -PassThru -Force | Set-Service -StartupType disabled