Remote Powershell Example with forward authentication to SQL

[Andreas Neufert]

Hi,

just a small example for the PowerShell Code example topic.

Remote Powershell with WSMANCred to forward authentication to a remote DB (works as well with local DB).
- There is an example with encrypted passwords and plain passwords.
- You can find the WSMANCred configuration as well in the code.

With Plaintext password:

Code: Select all

#On the BRE/SQL Server
#enable-wsmancredssp -role server
#set-item wsman:localhost\Shell\MaxMemoryPerShellMB 512

#On the Client
#winrm quickconfig
#enable-wsmancredssp -role client -delegatecomputer backup, backup.demoinfra.an.veeam.de
#set-item wsman:localhost\Shell\MaxMemoryPerShellMB 512
#
#gpedit.msc
#Computer Configuration -> Administrative Templates -> System -> Credentials Delegation -> Allow Fresh Credentials with NTLM-only Server Authentication
#Enable and add SPN and FQDN of the Servers in the list,like this:
#WSMAN/Servername               (without #)
#WSMAN/servername.domain.tld    (without #)
#
#PS with Administrative rights "gpupdate /force"

write-host " "
write-host " "
write-host " "
write-host " "
write-host " "
write-host " "
write-host " "
$actualtime = get-date
$actualtimeformated =$actualtime.ToUniversalTime()
Write-host $actualtime "Information: Loading Input"
$backupserver = "backup"
$username = "demoinfra\Administrator"
$password = convertto-securestring -string "Sumsi1!" -asplaintext -force
$credentials = new-object -typename System.Management.Automation.PSCredential -argumentlist $Username, $Password
$session = New-PSSession $backupserver -authentication CredSSP -Credential $credentials

$actualtime = get-date
$actualtimeformated =$actualtime.ToUniversalTime()
Write-host $actualtime "Information: Connecting to Backup Server and invoke commands..."

invoke-command -session $session -scriptblock{


#YourScript here
write-host "Hello World"
#EndOfYourScript


}
Remove-PSSession $session
$actualtime = get-date
$actualtimeformated =$actualtime.ToUniversalTime()
Write-host $actualtime "Information: Invoke Session Stopped"

Create an encrypted password file:

Code: Select all

read-host -assecurestring | convertfrom-securestring | out-file 'C:\scripts\password.txt'
#You have to type in the password when you run this script
#This saves the password in the password.txt by using Windows DPAPI. You can decrypt that password only on that Windows machine.
#Windows DPAPI uses Tripple-DES https://msdn.microsoft.com/en-us/library/ms995355.aspx
#You can use as well AES256 with the convertfrom-securestring command... See https://technet.microsoft.com/en-us/library/hh849814.aspx

#You can read the password with: $Password = get-content 'C:\scripts\password.txt' | convertto-securestring

Use this encrypted password in the above described code:

Code: Select all

#On the BRE/SQL Server
#enable-wsmancredssp -role server
#set-item wsman:localhost\Shell\MaxMemoryPerShellMB 512

#On the Client
#winrm quickconfig
#enable-wsmancredssp -role client -delegatecomputer backup, backup.demoinfra.an.veeam.de
#set-item wsman:localhost\Shell\MaxMemoryPerShellMB 512
#
#gpedit.msc
#Computer Configuration -> Administrative Templates -> System -> Credentials Delegation -> Allow Fresh Credentials with NTLM-only Server Authentication
#Enable and add SPN and FQDN of the Servers in the list,like this:
#WSMAN/Servername               (without #)
#WSMAN/servername.domain.tld    (without #)
#
#PS with Administrative rights "gpupdate /force"

write-host " "
write-host " "
write-host " "
write-host " "
write-host " "
write-host " "
write-host " "
$actualtime = get-date
$actualtimeformated =$actualtime.ToUniversalTime()
Write-host $actualtime "Information: Loading Input"
$backupserver = "backup"
$username = "demoinfra\Administrator"
$password = get-content 'C:\scripts\password.txt' | convertto-securestring
$credentials = new-object -typename System.Management.Automation.PSCredential -argumentlist $Username, $Password
$session = New-PSSession $backupserver -authentication CredSSP -Credential $credentials

$actualtime = get-date
$actualtimeformated =$actualtime.ToUniversalTime()
Write-host $actualtime "Information: Connecting to Backup Server and invoke commands..."

invoke-command -session $session -scriptblock{


#YourScript here
write-host "Hello World"
#EndOfYourScript


}
Remove-PSSession $session
$actualtime = get-date
$actualtimeformated =$actualtime.ToUniversalTime()
Write-host $actualtime "Information: Invoke Session Stopped"