My PowerShell script is unable to create a PS-Session when executed through SQL Server Agent Job, On the other hand when I tried to execute the script from powershell editor it is running fine.

I tried to catch output into a text file which shows script is executing fine until-------

echo "Credentials received" >> C:\Users\username\Desktop\ABC.txt

#Set Execution policy for the first run
#Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope CurrentUser

#This is better for scheduled jobs
$User = "[email protected]"
$pass = "0100skdhfsdhlfsj7eb0100000044afdcf1458d41449af94347f8d9d962000012464664603660000c000000010000000cb11e38eb73dd5d612111f4461d953e90000000004800000a00000001000000085512ba758402a4c5f4504af6f407ba3180000003bf98f386b5341815358f54166f931802814a6154f86daf8140000009a3c5af0588baa420ed836dc0ac1d1f4d0fb4649"

$PWord = ConvertTo-SecureString -string $pass
$UserCredential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $User, $PWord

echo "Credentials received"  >> C:\Users\username\Desktop\ABC.txt

$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $UserCredential -Authentication Basic -AllowRedirection

echo "Session"  >> C:\Users\username\Desktop\ABC.txt

Import-PSSession $Session -AllowClobber

echo "Session set up"  >> C:\Users\username\Desktop\ABC.txt

Remove-PSSession -Id $Session.Id
exit

When I run the same piece of code from powershell editor, it is getting executed correctly.
I am the expecting same behavior when it is scheduled from SQL Agent.

1 Answers

0
Umashankar On

As encryption is user specific we should create a proxy account at sql agent jobs for a user with whom password is encrypted. And then run the job with particular proxy. It works.

Encryption Technique used:

$password = read-host -prompt "Enter Password" 
write-host "$password is: " 
$secure = ConvertTo-SecureString $password -force -asPlainText 
$bytes = ConvertFrom-SecureString $secure 
#Encrypted Password
echo $bytes

Note:- enter code here Above mentioned technique is user and system specific. Please encrypt the password on the same machine from where you will be running the script in which you will be using the password.