Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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:

   'DRIVER={SQL Server};SERVER=MyServer;UID=MyUserID;PWD=MyCleverPassword',
   'select @@ServerName')         
share|improve this question
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
up vote 3 down vote accepted

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

    * FROM
   'Data Source=OtherServer\InstanceName;Catalog=RemoteDB;User ID=SQLLogin;Password=Secret;').RemoteDB.dbo.SomeTable
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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