Working example of OPENDATASOURCE command using SQL Authentication 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')
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