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


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!

Group of computer programmers working in the office. Focus is on blond woman showing something to her colleague on PC.
Updating your Azure SQL server OAuth2 credentials in Power BI via PowerShell for automation purposes
The better way to update OAuth2 credentials in Power BI is by automating the process of updating Azure SQL Server...
Under (memory) pressure
A few weeks ago, a client asked me if they were experiencing memory pressure and how they could monitor it...
Managing files from other devices in a Fabric Lakehouse using the Python Azure SDK
In this blogpost, you’ll see how to manage files in OneLake programmatically using the Python Azure SDK. Very little coding...
Database specific security in SQL Server
There are many different ways to secure your database. In this blog post we will give most of them a...
SQL Server security made easy on the server level
In this blog, we’re going to look at the options we have for server level security. In SQL Server we...
Microsoft SQL Server history
Since its inception in 1989, Microsoft SQL Server is a critical component of many organizations' data infrastructure. As data has...