• Home
  • About
  • Blog
  • Speaking
  • Contact

Openrowset across the CMS: The Proxy Way of Life

20 March 2015SQL StijnGeen categorieNo Comments

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

1

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

2
Pick your proxy under run as and then this job step will run under the account you defined under the proxy.

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!

Stay tuned!

Tags: CMS, MICROSOFT, Openrowset, Proxy, SQL SERVER, TSQL

Related Articles

Load your data into your PDW (part 1 the DWLOADER)

7 January 2014SQL Stijn

Clustered Columnstore Index TupleMove Magic

6 November 2014SQL Stijn

Migration Gone Wrong! The Fallback Scenario

23 February 2016SQL Stijn

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

Recent Posts

  • Building a Modern Lakehouse Data Warehouse with Azure Synapse Analytics: Moving your Database to the lake
  • Azure Synapse Dedicated SQL Pools: Workload Management
  • Synapse Spark – Playing with Data Frames with Python
  • Synapse Spark – Reading CSV files from Azure Data Lake Storage Gen 2 with Synapse Spark using Python
  • Azure Synapse Analytics – Serverless SQL Pools: Data Elimination

Recent Comments

    Archives

    • June 2022
    • December 2021
    • May 2021
    • April 2021
    • March 2021
    • February 2021
    • January 2021
    • December 2020
    • August 2016
    • July 2016
    • March 2016
    • February 2016
    • December 2015
    • October 2015
    • August 2015
    • March 2015
    • December 2014
    • November 2014
    • July 2014
    • February 2014
    • January 2014
    • December 2013

    Categories

    • Azure Synapse Analytics
    • Building a Modern Lakehouse Data Warehouse
    • Geen categorie
    • MS PDW
    • SQL 2016
    • SQL Maintenance
    • SQL Server Migration
    • SQL Server Misconfiguration Chronicles
    • SQL2014
    • Troubleshooting

    Contact Stijn now


    © SQL Stijn 2017 - Privacy Policy - Cookie Policy
    Manage Cookie Consent
    To provide the best experiences, we use technologies like cookies to store and/or access device information.
    Functional Always active
    The technical storage or access is strictly necessary for the legitimate purpose of enabling the use of a specific service explicitly requested by the subscriber or user, or for the sole purpose of carrying out the transmission of a communication over an electronic communications network.
    Preferences
    The technical storage or access is necessary for the legitimate purpose of storing preferences that are not requested by the subscriber or user.
    Statistics
    The technical storage or access that is used exclusively for statistical purposes. The technical storage or access that is used exclusively for anonymous statistical purposes. Without a subpoena, voluntary compliance on the part of your Internet Service Provider, or additional records from a third party, information stored or retrieved for this purpose alone cannot usually be used to identify you.
    Marketing
    The technical storage or access is required to create user profiles to send advertising, or to track the user on a website or across several websites for similar marketing purposes.
    Manage options Manage services Manage vendors Read more about these purposes
    View preferences
    {title} {title} {title}