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!

Parameter sniffing solved with new Parameter Sensitive Plan Optimization feature

If you’re someone that works a lot with Microsoft SQL Server, there’s no doubt that you’ve had issues with an issue called “Parameter sniffing” before. Up until now, you had...

Creating maps with R and Power BI

The possibilities are infinite when it comes to creating custom visuals in Power BI. As long as you have creativity and knowledge about the right programming language, you can let...

Sending monitoring alerts through Telegram

What if you could get the ease of phone notifications for whatever monitoring alerts you need? Then we have a solution for you with the app Telegram. Some of you...

Send mails with Azure Elastic Database Jobs

The DatabaseMail feature in SQL Server and Managed Instance is widely used by many professionals. But what if you want a similar functionality in Azure SQL Database? There are options,...

Sorting matrices in Power BI

Recently I worked on a Power BI project for a client. They had a SharePoint site where they regularly published articles and wanted to pour view data into a report...

The world of data is evolving

The data landscape has changed dramatically over recent years. In the past, we mainly heard that we needed to do as much as possible “cloud-only”—but this trend has become more...