kohera-logo-regular.svg

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!

2319-blog-database-specific-security-featured-image
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...
kohera-2312-blog-sql-server-level-security-featured-image
SQL Server security on server level
In this blog, we’re going to look at the options we have for server level security. In SQL Server we...
blog-security_1
Microsoft SQL Server history
Since its inception in 1989, Microsoft SQL Server is a critical component of many organizations' data infrastructure. As data has...
DSC_1388
Aggregaties
Power BI Desktop is een prachtige tool om snel data-analyses te kunnen uitvoeren. Je connecteert op een databron, importeert en...
dba image
DBA is not that scary
Often when talking to people who are looking for a career path in the data world I feel like there...
blog-2303
How do you link an SCD Type 2 table in Power Query?
This article uses a simple example to demonstrate how to link an SCD Type 2 table in Power Query to...