Wednesday, December 23, 2015

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


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.


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.


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\
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
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
$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.


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

Aashi siva 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

Vmware Training in Chennai

Web Designing Training in Chennai

AWS Training in Chennai

Linux Training in Chennai

Microsoft Azure Training in Chennai

Amelia Lily said...

I do trust all of the concepts you’ve presented on your post. They’re really convincing and will definitely work. Still, the posts are too brief for newbies. May you please extend them a little from subsequent time?Also, I’ve shared your website in my social networks.
Industrial Architecture
Warehouse Architect
Civil Engineering Consultants
Office Interiors in Chennai
Rainwater Harvesting chennai

Nicole Bolton said...

I think it's awesome someone is finally taking notice of our vet's and doing something to help them. I hope all goes well with these articles. More new information i will get after refer that post.
Manufacturing ERP
Oil and gas ERP
ERP software companies
Best ERP software
ERP for the manufacturing industry

Atom Steels said...

The Mobile Accessories is a largest mobile retail Chain dealing in leading international and Indian Brands of mobile phones and accessories headquartered with using special offers and low cost of the latest branded mobile phones. This is amazing offers with some of days.

Mobile Showrooms in OMR

Sophia sage said...

I must thank you for the efforts you have put in penning this site. I am hoping to check out the same high-grade content by you later on as well. In truth, your creative writing abilities has inspired me to get my own, personal blog now..
Software Testing Training in Chennai
SEO Training in Chennai
Informatica Training in Chennai
Digital Marketing Training in Chennai

helen shapiro said...

It is a great job that you are working and offer our necessary need to your posts. We like your job and wish you all the best.
digital marketing company in india

Kalai chellam said...

Its really an Excellent post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog. Thanks for sharing....

Restaurant in OMR
Apartments in OMR
Villas in OMR
Resorts in OMR

Nutra Trials said...

Nutra Trials defines personal characteristics of different health products including skincare, weight loss, muscle and male enhancement. The study presented here is briefly described for reader convenience and to deliver them assurance with health standards. The best possible answers are given here regarding the selection of an ideal supplement or cream or serum that possibly remains to be safe for health and do not cause any side effects.

Arushi Chaya said...

I have really happy to these reading your post. This product control and maintenance of our health.The daily routine can assist you weight lose quickly and safely.My life is completely reworked once I followed this diet.I feeling nice concerning myself.

Herbalife in Chennai
Herbalife Nutrition Products
Nutrition centers in Chennai
Weight Loss in Chennai
Weight Gain in Chennai

Mediahub news said...

Fantastic article to go through, I would appreciate the writer's mind and the skills he has presented this great article to get its look in better style. It really brushed up my mind and I am now feeling very much relaxed after getting complete summary regarding every singl aspect of the article, once again I would like to thank you for such great creativity. Fmovies

NutraT line said...

It is a great job, I like your posts and wish you all the best. and I hope you continue this job well.
NutraT line

sujitha s said...

BIG DATA Technologies provides you with a state of the art software which combines modern GPU technology (Graphic Processing Units) with the best practices in today’s Big Data platforms, providing up to 100x faster insights from data.
Bigdata Training in Chennai OMR

Georgia Jasmine said...

Wow! amazing post. Thanks for the such good type of article. Supplement Doctor suggest most advanced and latest Health & Wellness Products Daily Basis. Everyone Collects more info about their favourite Supplements.

john jersy said...

This looks absolutely perfect. All these tiny details are made with lot of background knowledge. I like it a lot. 

python training in chennai
python course in chennai
python training in bangalore

thomus jons said...

Hello, I am thomus jons thank you for this informative post. That is a great job. Wish you more success.Thank you so much and for you all the best. Takes Down

hansika hans said...

All the points you described so beautiful. Every time i read your i blog and i am so surprised that how you can write so well.

Java interview questions and answers

Core Java interview questions and answers

Java training in Chennai | Java training in Tambaram

Java training in Chennai | Java training in Velachery

john brito said...

Good Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge as updated one, keep blogging.
rpa training in Chennai | rpa training in bangalore | best rpa training in bangalore | rpa course in bangalore | rpa training institute in bangalore | rpa training in bangalore | rpa online training

gowsalya said...

Really you have done great job,There are may person searching about that now they will find enough resources by your post
Best Devops training in sholinganallur
Devops training in velachery
Devops training in annanagar
Devops training in tambaram

geetha sridhar said...

Thank you for taking the time and sharing this information with us. It was indeed very helpful and insightful while being straight forward and to the point.
Data Science course in Chennai | Best Data Science course in Chennai
Data science course in bangalore | Best Data Science course in Bangalore
Data science course in pune | Data Science Course institute in Pune
Data science online course | Online Data Science certification course-Gangboard
Data Science Interview questions and answers

cathryn said...

Thank you for benefiting from time to focus on this kind of, I feel firmly about it and also really like comprehending far more with this particular subject matter. In case doable, when you get know-how, is it possible to thoughts modernizing your site together with far more details? It’s extremely useful to me.
angularjs Training in chennai

angularjs Training in chennai

angularjs-Training in tambaram

angularjs-Training in sholinganallur

angularjs-Training in velachery

angularjs-Training in pune

Shakila Ali said...

Healrun is a health news blog we provide the latest news about health, Drugs and latest Diseases and conditions. We update our users with health tips and health products reviews. If you want to know any information about health or health product (Side Effects & Benefits) Feel Free To ask HealRun Support Team.

Unknown said...

Supplements For Fitness saturated fats, increase vegetables and proteins and reduce calories. Remember that the key to losing weight is to burn more calories than your body consumes. Be sure to pay attention to food labels and consume fewer calories than before, so that your body loses kilos.Also, reduce carbohydrates. You want

Unknown said...

Pilpedia is supplying 100 percent original and accurate information at each moment of time around our site and merchandise, and the intent is to improve the usage of good and pure health supplement. For More Info please visit Pilpedia online store.

Steve Coleman said...

We are here to give you a complete review on the Parallel Profit project by Steve Clayton and Aidan Booth. If you are someone from the field you would already be familiar with these two names, for those of who are new. Parallel Profits Review

Olivia Jakeman said...

Best softwares for Internet Marketers and legitimate make money online opportunities cxyrc