How to use OPENROWSET with Trusted Connection

[Andel]

I was able to use OPENROWSET before using Trusted Connection but now it's not working because of some SQL patch that we've applied. Is there any other way I can query the information from another server using trusted connection? I can use OPENQUERY but I don't want to setup link server.

Andel
andel@barroga.net

http://andyb.8m.com

 

[ClaireHCM]

This is the query for using trusted connection


SELECT *
FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0'
,'Excel 8.0;Database=c:\Documents and Settings\Administrator\My Documents\Book1.xls'
,'SELECT * FROM [Sheet1$]'
)

 

[Andel]

Actually, i'd like to read another sql database not other kind of data source-- From SQL to SQL.


Andel
andel@barroga.net

http://andyb.8m.com

 

[ArtieChoke]

What does your connection string look like?

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[ClaireHCM]

select * from
OPENROWSET( 'SQLoledb',
'Trusted_Connection=yes;Data Source=clairehsu;Initial Catalog=master',
'select * from dbname.dbo.a' )

 

[ClaireHCM]

Data Source is your Server Name

 

[ArtieChoke]

Looks reasonable. Andel, does your server still support NT Authentication Mode or did the patch change that?

"I think we're all Bozos on this bus!" - Firesign Theatre