Openrowset

[shaunk]

I'm using the following statement to access tables in a remote SQL Server.

select IC.* from
openrowset('SQLOLEDB','CONNELLY7';'Hahs_IMRU_CAxF';'wallyworld',
'SELECT *
FROM Client') IC

The message returned is:
Login failed for user 'Hahs_IMRU_CAxF'. Reason: Not associated with a trusted SQL Server connection.

I have the user setup on CONNELLY7 with dbo.owner role permissions.
So is this related to the server I am making the call from OR have I duffed the user setup on CONNELLY7.

Thanks

 

[JamesLean]

It sounds like the remote server (CONNELLY7) is set up for Windows only authentication. You need to change it to mixed mode (Windows & SQL) to use a username and password like that.

--James

 

[shaunk]

Thanks. Of course sixty pages of reading on Security and Distributed transactions failed to mention that. Before I implement though, is there any sort of objection the purists may mount to Openrowset. I don't need to go to a linked server model.

 

[JamesLean]

Generally speaking, OPENROWSET should be used for one-off ad-hoc access to a server. If you're going to be accessing this server regularly then it is probably easier to add it as a linked server and just use four-part qualified names in your queries.

--James

 

[shaunk]

Are you aware of any limitation around image data and openrowset. I am using a stored procedure containing an openrowset statement as a data source in a Crystal Report. It works for all the data types I am extracting except image data. The "not supported" message is posted.
However, running the procedure standalone in Query Analyser returns the image data as expected ?
I am going to post this in the Crystal forum as well.
Thanks

 

[Catadmin]

I believe this is a problem with specific versions of Crystal, shaunk. Do a search on SeaGate's business objects website to see what issues they may be aware of.

http://support.businessobjects.com/search/default.asp?ref=default.asp_wwwjump

This link should take you directly to the Crystal Reports Support website.

http://www.businessobjects.com/support/default.asp

This one takes you to the page right before the above page if you have trouble reaching the first link.

Hope it helps.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[shaunk]

FYI...it turned out to be a Crystal version issue. I was using V 8.0 where it was failing, but succeded on V 8.5.
I ended up implementing a linked server / Openquery model which was quite easy in the end.
Thanks.