Working example of OPENDATASOURCE command using SQL Authentication

Question

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') 

Answer 1

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