asked on

Openrowset with trusted connection

Can I perfome a query on a remote server using openrowset with trusted connection like:

openrowset('SQLOLEDB', 'server';'login';'password', 'select * from DB.dbo.Table1')

Guy Hengel [angelIII / a3]

listening & learning...

i don't know it (and i don't have SQL box here), but i would try
openrowset('SQLOLEDB', 'server';'';'', 'select * from DB.dbo.Table1')

vterekh

ASKER

unfortunately, it doesn't work :(

Server: Msg 18456, Level 14, State 1, Line 34
Login failed for user '(null)'.

danblake

Why do it in this method.

Create the OLEDB as a linked server.
then you can run the command like so:
select * from servername.database.owner.tablename

Adding a linked server use:
sp_addlinkedserver (T-SQL)

see below:
A. Use the Microsoft OLE DB Provider for SQL Server
This example creates a linked server named SEATTLESales that uses the Microsoft OLE DB Provider for SQL Server.
USE master
GO
EXEC sp_addlinkedserver
'SEATTLESales',
N'SQL Server'
GO

or use Enterprise Manger.
Go to linked servers in security.
Select add linked server.
Select SQL Server.
Put in the SQL Server name.
Click Security.

Map any necessary logins.
(You can force all linked server commands to be requested under 1x specific login for example).

Much more usefull.

Saves having to specify connection info all the time,

dan.

ASKER CERTIFIED SOLUTION

chigrik

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial