Running query on server 'A'; need to get data from server 'B'
I have a script that I need to run from any SQL instance (we'll call it server 'A') in our environment. One of the datum I need to retrieve is on our CMS server (server 'B'), the 'Registered server name', which has descriptive information about server 'A'. My problem is: how to go about retrieving the server description from server 'B' from any of the possible servers 'A'? I was able to create a login / user on the CMS database and use OPENDATASOURCE in my query from server 'A', but that leaves the password in clear text in the script. Creating linked servers from all of the different 'A' servers is not reasonable. I can't figure out how to dynamically provide the name of server 'A' in a SQLCMD query, or how to return the information back from CMS to 'A', if that is even possible. I think the answer might be in Powershell somewhere, but haven't hit on the way to pass the CMS information back to 'A', unless I write the server description to a text file, then read from that file later on. Differences in drives, permissions, etc. on the different servers would make this very difficult. And my PoSH skills are pretty weak ;) Any ideas or suggestions would be most welcome! Thanks, Mike
Are you able to use integration authentication? If so, you can use OPENDATASOURCE that way and avoid the need for a user ID and password. Have you tried using @@SERVERNAME to pass the server name? I just tested the following using integrated authentication: SELECT * FROM OPENDATASOURCE('SQLNCLI', 'Data Source=myserver\myinstance; Integrated Security=SSPI').master.sys.databases WHERE name = DB_NAME() ;
This is a followup to my comment about Kerberos authentication.
I found an old email with a link to the article I used at the time and some notes I wrote. How to Implement Kerberos Constrained Delegation with SQL Server 2008
https://technet.microsoft.com/en-us/library/gg723715.aspx] My quick summary of what I considered to be the most important part of the document: 1. Active Directory Users and Computers. 2. Right-click delegation computer. (This is the computer hosting the SQL Server instance that will have a linked server to the 2nd SQL Server instance.) 3. Click Delegation tab. 4. Choose "Trust this computer for delegation to specified services only". 5. Choose "Use Kerberos only". 6. Add "MSSQLSvc" services. (These are for the Network Service account. For SQL Server instances running with a domain account, you must first create the SPNs for the accounts. This is explained in Scenario 2 in the document.) 7. Restart the SQL Server service. :
Tom, thanks for the tips. I've marked the OPENDATASOURCE answer since I was able to successfully get the data that I needed; it's just that this solution won't work in my environment due to security constraints. Because we're using different agent accounts across the instances, the 'double-hop' Kerberos issue is rearing it's head, as you indicated earlier. I'm going to try and approach the problem from using Powershell to query the SQL Server objects instead of Query Analyzer and see if that works better.