Take the 2-minute tour ×
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. It's 100% free, no registration required.

I need to run some ad-hoc queries on several hundred servers that are not part of a domain. Each server has a low-privelage 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 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')         
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
add comment

1 Answer

up vote 2 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)

SELECT
    * FROM
OPENDATASOURCE (
   'SQLNCLI', -- or SQLNCLI
   'Data Source=OtherServer\InstanceName;Catalog=RemoteDB;User ID=SQLLogin;Password=Secret;').RemoteDB.dbo.SomeTable
share|improve this answer
add comment

Your Answer

 
discard

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.