asked
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
You have some answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
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() ;
Nope, I get the following: Msg 18456, Level 14, State 1, Line 1 Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
I had previously added a comment after yours, but I'm not sure what happened to it. Maybe I forgot to submit. Regardless, your error message leads me to believe the problem could be related to multi-hop delegation with Kerberos authentication.
Uh oh. It sounds like you may have been bitten by Kerberos. I have had to handle that several times myself. It's really not that complicated to fix ... as long as you have the necessary permissions. There are several articles you will find if you search for "kerberos authentication sql server anonymous". Here's one that I scanned that looks pretty good.
http://mssqlwiki.com/2013/12/09/sql-server-connectivity-kerberos-authentication-and-sql-server-spn-service-principal-name-for-sql-server/ I wrote my own guide about a year ago based on internet research and my own experiences. If I find it, I'll post it here.
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][1] 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. [1]: https://technet.microsoft.com/en-us/library/gg723715.aspx
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][1] 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. [1]: https://technet.microsoft.com/en-us/library/gg723715.aspx
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.
19 People are following this question.
