Executing a stored proc with openrowset query in a SQL Server job across the CMS

Today I came across an issue with my Central Management Server. I have a database on my CMS which has a few tables with details about the server state (memory, disk space, etc.). I wrote a few stored procs to gather this data using openrowset in combination with the server list obtained from the msdb on the CMS.

When I executed this stored proc under my Windows account in SSMS it worked and I was able to get the data across my CMS. But when I tried to automate it, I came across an interesting issue that I’d like to share with you guys.

The stored procs I wrote contain server state information which is added to the tables of a database on the CMS, which are then added to some of the reports on a report server. Since I want to run these stored procs periodically, I decided to execute them using an SQL Server agent job.

So I created the job and executed it. So far so good, no errors. But when I looked at the data in the tables I noticed that the only data being collected were the data from the CMS and there were no data from the other servers. It turns out that the SQL Server agent account couldn’t connect to all the servers in the CMS.

How to solve this problem? I decided to change my SQL statement (execute of my stored proc) to a PowerShell statement that would execute my jobs. With PowerShell I can use a proxy to execute these statements and use an account with sufficient rights on all my servers (view server state & rights to the databases you want to read from).

I’ll explain how I did this in a few screenshots.

First I had to configure a proxy account. Go to SQL Server agent and right-click Proxies and select New Proxy. Enter your information and click OK to create the proxy.

After creating the proxy go to job step, which needs to execute across your CMS. Select your proxy under ‘Run as’ and this job step will then run under the account you defined under the proxy.

This will solve the problem and execute the stored proc across the whole CMS or the subset of servers you selected from the msdb on your CMS.

Note: Remember to adjust your SqlCmd.CommandTimeout to the amount of time needed to complete your query. If you don’t do this, the job will be reported as successful even if it didn’t gather the data across all the servers because of insufficient time specified in the query timeout.

Thanks for reading! Stay tuned!