4

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')
Colin 't Hart
  • 9,455
  • 15
  • 36
  • 44
datagod
  • 7,141
  • 4
  • 38
  • 58

1 Answers1

7

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
gbn
  • 70,237
  • 8
  • 167
  • 244