Change Sql Service Account Password with no Restart

On SINGLE INSTANCE (apparently this does not work correctly with clusters…will test and update…) Sql Servers use the following code to update the password for the service account. This will not require a restart of the service. Curiously…the call to $service.ChangePassword is supposed to take the old password as the first parameter and the new password as the second parameter…but in my haste I didn’t notice this at first and was just passing in the new password for both parameters, yet everything seems to work just fine this way. I bingled around to try to see if anyone else had noticed this with no luck at all…

  $password = 'P@55s0rd!' 

 icm -ComputerName ComputerName -ScriptBlock{
    
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null
    try{
        $srv = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $env:COMPUTERNAME
     
        $service = $srv.Services | Where-Object{$_.name -eq 'sqlserveragent'}
        $service.ChangePassword($using:password, $using:password)
        $service.Alter()
 
        $service = $srv.Services | Where-Object{$_.name -eq 'mssqlserver'}
        $service.ChangePassword($using:password, $using:password)
        $service.Alter()
    }
    catch{
        Write-Warning ($_ | fl -Force | Out-String)
        throw $_ 
    }


 }

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 


}

Create PSCredential Object

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

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"

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()

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();
}

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()
    }

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
    }

}

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{

    }
}

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(); }