Open rowset syntax help 1

[twifosp]

Hi, trying to get an open rowset right. I can get it to work if I use a login, but I want to use NT auth.

This works:

select  a.*
(from	openrowset ('SQLOLEDB',server;'login';'password',
	'select * from table') as a

This does not:

select *
from   openrowset('SQLOLEDB', 'Trusted_Connection= yes;Data Source=server;', 'select * from table') as a

I get "Login failed for user 'SYSTEM'.
[OLE/DB provider returned message: Invalid connection string attribute]" when I try trusted connection.

What am I doing wrong?

 

[vongrunt]

Have you tried Integrated Security=SSPI; ?

Also specify default database (Initial Catalog=blah

 

[twifosp]

Thanks for the response. Didn't work. String now looks like:

select *
from   openrowset('SQLOLEDB', 'Trusted_Connection= yes; Integrated Security=SSPI; Data Source=server; Initial Catlog = database;', 'select * from table') as a

Getting same error message.
Login failed for user 'SYSTEM'.
[OLE/DB provider returned message: Invalid connection string attribute]

 

[vongrunt]

Remove extra spaces between properties and values (Trusted_Connection=Yes for example), check for typos (Initial Catalog) then try again...

 

[twifosp]

Bah, owned by typing. Sorry I didn't catch that. Works now, thanks vongrut you da man.

Fyi, didn't need the initial catalog, just the extra integrated security=sspi.

Thanks!