Wednesday, December 23, 2015

SQL Agent job to run a task on a remote system that has SQL Server instance

Problem

The last step of a SQL Server Agent job has to run a windows task on a remote system that has no SQL Server instance installed.

Description

A SQL Server has to execute a number of database tasks by means of a SQL Server Agent job. This job is scheduled to run daily at a given time. The last step should execute a task on a remote Windows server. This remote server does not have SQL Server instance installed. A simple solution would be to schedule a Windows task on the remote server, but we will never be certain that the SQL Agent job finishes before the remote server starts the task, unless we schedule the windows task a lot later than the estimated end of the SQL job. We prefer the whole process to finish in as little time as necessary.
Therefore, the last step of the job should trigger the execution of the task on the remote system. The maintenance on the remote system is kept simpler  because there is no windows schedule to maintain that has to be aligned with the SQL Agent schedule. Also the timespan to finalize the complete job is kept as short as possible, compared to keeping a large enough timespan between the SQL schedule and the Windows task schedule.

Solution

The executable on the remote system can be run with a Windows user account that is already used as a Windows service account on the remote system. This account has a password that does not expire and it has sufficient rights to run the executable.
Powershell is available on both servers and can be used to invoke remote commands. Powershell can be scripted as a job step in SQL Server Agent.
There is a powershell script on the remote computer to run the executable.
What I need to do is to add a job step that invokes the powershell script on the remote computer with the credentials of the Windows service account.

Remote script setup

The script that contains the command to run the executable on the remote system is saved as C:\Scripts\Powershell\RunSomeExecutable.ps.
As proof of concept the script contains some lines to create a file in another directory of the remote server.
$text = 'Hello World'
$text | Out-File 'D:\Data\Test\file.txt'

Password encryption

The powershell command executed by the job step could contain the password hardcoded like this:
$pw = convertto-securestring -AsPlainText -Force -String tH1s1sAPa$$w0rd
$user = "domainname\username"
$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $user,$pw
If you want to encrypt the password, you have to encrypt it on the remote server and then enter the encryption string in the Powershell command in the Job step.
# Enter the Credentials details 
# DO THIS ON EVERY SQL SYSTEM locally logged in!!! Encryption will be different for every server

$password = read-host -prompt "Enter your Password"  
write-host "$password is password"  
$secure = ConvertTo-SecureString $password -force -asPlainText  
ConvertFrom-SecureString $secure |Out-File C:\Users\username\Documents\Powershell\Encryptpw.txt 
You can use the Windows Powershell ISE to create the code and run it.

Open Encryptpw.txt, it will contain a single line like this
01000000d08c8ddf0115d1118c7a00c04fc297eb01000000f4dc057c2362784b850a195b175f2e520000000002000000000003660000c000000010000000c5b288e86f6e31e4c8245f1ebbf12f070000000004800000a00000001000000084793ddf17bc3c116f3d991e469f188718000000405af26d131debb66922c4381a81edef6b996f030ac5165b14000000fa6a327ea9da87e868b491c18b8393e89002d713
Copy the encrypted contents to the clipboard, then paste into the first statement of the Job step command line, to assign it to the $encryptpw variable (see next setup step)

Job step setup with encrypted password

Step Name:
Type: Powershell
Run As: SQL Server Agent Service Account
Command:
$encryptpw = "01000000d08c8ddf0115d1118c7a00c04fc297eb01000000f4dc057c2362784b850a195b175f2e520000000002000000000003660000c000000010000000c5b288e86f6e31e4c8245f1ebbf12f070000000004800000a00000001000000084793ddf17bc3c116f3d991e469f188718000000405af26d131debb66922c4381a81edef6b996f030ac5165b14000000fa6a327ea9da87e868b491c18b8393e89002d713"  
$pw = ConvertTo-SecureString -string $encryptpw
$user = "domainname\username"
$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $user,$pw
$computer = "remotecomputername"
$session = new-pssession -computername $computer -credential $cred
Invoke-Command -Session $session -ScriptBlock {Invoke-Expression "C:\Scripts\Powershell\RunSomeExecutable.ps1" }
Save the job-step and the job and then run it
 Check on the remote server if the task was executed. In my example: if the file has been created.

SQLAgent proxy

You should test the job in a scheduled run as well, because the job will run with the operator 'SQL Server Agent Service Account'. If this account does not have sufficient rights to run the powershell script remotely, you will need to create a SQL Agent proxy with rights to run the powershell script.

12 comments:

Dhiya L said...

Great post! I am actually getting ready to across this information, is very helpful my friend. Also great blog here with all of the valuable information you have. Keep up the good work you are doing here.Well, got a good knowledge.

Hadoop Training in Chennai

Sowmiya said...

this technological concepts are really well being and wonderful thus it is very much interesting and very well good too, really i got more information from your knowledge. thanks for postings these valuable information.


Digital Marketing Company in Chennai

Shalini said...

Very nice post here thanks for it .I always like and such a super contents of these
post.Excellent and very cool idea and great content of different kinds of the valuable
information's. seo company in chennai

Shaakshi said...

Superb i really enjoyed very much with this article here. Really its a amazing article i had ever read. I hope it will help a lot for all. Thank you so much for this amazing posts and please keep update like this excellent article.thank you for sharing such a great blog with us. expecting for your updation.
Car Wash Services in Mumbai

Jeffy said...

Thank you for sharing such a nice and interesting blog with us. I have seen that all will say the same thing repeatedly. But in your blog, I had a chance to get some useful and unique information. I would like to suggest your blog in my dude circle. please keep on updates. hope it might be much useful for us. keep on updating...
seo company in chennai
Digital Marketing company in chennai

Padhma said...

Really a pretty thing you had said here. I think this will be useful at many people. SO please keep update like this.
Back to original

Priya said...

Great Article… I love to read your articles because your writing style is too good, its is very very helpful for all of us and I never get bored while reading your article because, they are becomes a more and more interesting from the starting lines until the end.

gmat training in chennai
gmat classes in chennai
gmat coaching institutes in chennai
gmat coaching chennai
best gmat coaching classes in chennai

Shalini said...

Superb. I really enjoyed very much with this article here. Really it is an amazing article I had ever read. I hope it will help a lot for all. Thank you so much for this amazing posts and please keep update like this excellent article.thank you for sharing such a great blog with us. expecting for your.
Digital Marketing Company in India
seo Company in India

Abiya Carol said...

I just see the post i am so happy to the communication science post of information's.So I have really enjoyed and reading your blogs for these posts.Any way I’ll be replay for your great thinks and I hope you post again soon.

digital marketing course in chennai
software testing training in chennai

Vignesh PV said...

your information is really awesome as well as it is very excellent and i got more interesting information from your blog.Android Training in Chennai

Mahalyasree said...

Everything is fine, am happy about your blog. Thanks admin for sharing the unique content, you have done a great job I appreciate your effort and I hope you will get more positive comments from the web users.
Interior Decorators in Chennai
Home Interior Designers in Chennai
Home Interiors in Chennai

sandhosh said...



great article which conveyed a good information.awaiting for more updaates like this.
SEO Company in India
SEO Services in India
SEO Companies in India
SEO Company India
SEO Services India