3

I need to run some ad-hoc queries on several hundred servers that are not part of a domain. Each server has a low-privilege SQL user account that has read-only access to a few tables of interest.

My idea was to have the names of these servers stored in a table, and use that table to drive an OPENDATASOURCE or OPENROWSET command.

The servers are either in different domains, non-trusted domains, work-groups, etc. A big mess.

I cannot for the life of me find an example of how to configure these commands to use a SQL Server account/password combination.

Note: This happens in a sandbox, it is not meant for the real world. It is simply a proof of concept, so security is not a major concern. These servers are cranked out by a process that is simulating a real-world growth of a network.

For the record, I was able to get OPENROWSET to work:

SELECT *
FROM OPENROWSET('SQLNCLI',
   'DRIVER={SQL Server};SERVER=MyServer;UID=MyUserID;PWD=MyCleverPassword',
   'select @@ServerName')         
  • Sorry, a wee typo in there. You can or cannot use Windows because of no domain? You want to use a SQL login? – gbn Nov 29 '11 at 19:55
  • Each of the server is in its own little domain, or workgroup, etc. Big mess, will be fixed over time. Domain trust either does not exist or is not possible. SQL authentication is a band-aid, but it will work for now in the sandbox. – datagod Nov 29 '11 at 20:16
  • thought so, just checking because of a wee typo above – gbn Nov 29 '11 at 20:17
  • I think I clarified things. Thanks for the help, gbn. – datagod Nov 29 '11 at 21:19
5

To run it with a SQL login, just need to specify User ID and Password in the connection string (called "init string" in BOL)

SELECT
    * FROM
OPENDATASOURCE (
   'SQLNCLI', -- or SQLNCLI
   'Data Source=OtherServer\InstanceName;Catalog=RemoteDB;User ID=SQLLogin;Password=Secret;').RemoteDB.dbo.SomeTable
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.