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')
Best Answer
To run it with a SQL login, just need to specify User ID and Password in the connection string (called "init string" in BOL)