Today I came across an issue I was having with my CMS.
On my CMS server I have a database which has a few tables with some detail about my server state (Memory/Diskspace/…).
I wrote a few stored procs to gather this data with openrowset in combination with the serverlist that I get from the msdb on the Central Management Server.
When I execute this stored procedure under my windows account in SSMS nothing failed and I was able to get the data across my cms. Now when I tried to automate this, I came across an interesting issue which I would love to share with you guys.
The stored procs that I wrote contains server state information which feed tables of a database on my cms which will feed some reports on a report server. Since I want to have these stored procs run periodically, I decided to execute these stored procs using a sql server agent job.
So I created the job, I executed the job, so far so good. No errors, but when I looked into the data in the tables I saw that the only data that was collected was the data of the CMS and no data was collected from other servers. This is because the SQL Server agent account could not connect to all the servers in the CMS.
How to solve this pickle? I decided to alter my sql statement (execute of my stored proc) to a Powershell statement which will execute my jobs. Using powershell I can use a proxy to execute these statements and use an account with sufficient rights (View server state & rights to the databases you want to read from) on all my servers.
I will continue by explaining how I did this in a few screenshots.
Firstly 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.
When you have created the proxy go to your job step which needs to execute across your CMS
This will solve the problem, and execute the stored proc on your whole CMS or the subset of servers you selected from the msdb of you CMS.
NOTE : Don’t forget to put your SqlCmd.CommandTimeout to the amount of time you need for your query to finish. If you do not do this, he will report the job as successful but will only have gotten the data of the servers for which he had time in the current timeout of the query.
Thanks for reading!