OPENROWSET with Windows Authentication

Question

OPENROWSET with Windows Authentication

pintail

SSC-Addicted

Points: 483
More actions
July 25, 2003 at 12:02 pm

#159822

Is it possible to use Windows Authentication with OPENROWSET in a stored procedure?
BOL has syntax to use SQL Authentication and is working fine.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Rajesh Patavardhan SSCrazy Points: 2801 More actions · Answer 1

To use windows authentication you need to provide Trusted_connection and Integrated Security attributes in connection string

Try the following

SELECT a.*

FROM OPENROWSET('SQLOLEDB',

'Data Source=vypar;Trusted_Connection=Yes;Initial Catalog=pubs;Integrated Security=SSPI;',

pubs.dbo.authors) AS a

ORDER BY a.au_lname, a.au_fname

pintail SSC-Addicted Points: 483 More actions · Answer 2

This statement does not make connection to remote server even when correct value is supplied for Data Source. It connects to local server. As required database does not exists on local server, error is reported.

Server: Msg 7314, Level 16, State 1, Line 1

OLE DB provider 'SQLOLEDB' does not contain table '"TEST_DB"."dbo"."TABLE_1"'. The table either does not exist or the current user does not have permissions on that table.

OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='SQLOLEDB', TableName='"TEST_DB"."dbo"."TABLE_1"'].

Following command will list databases from local server and not from remote server. Also database specified in Initial Catalog does not exists on local server and no error is reported.

SELECT *

FROM OPENROWSET('SQLOLEDB',

'Data Source=REMOTE_SERVER_1;Trusted_Connection=Yes;Initial Catalog=TEST_DB;Integrated Security=SSPI;',

master.dbo.sysdatabases)

Rajesh Patavardhan SSCrazy Points: 2801 More actions · Answer 3

Sorry for mis-guiding you.

It looks like the syntax ignores Data Source, Initial Catalog etc attributes. It does not even raise error even if there is a error in attribute name.

I tried (SERVER & DATABASE attributes instead of Data Source & Initial Catalog ) the following and it gave correct result.

SELECT a.*

FROM OPENROWSET('SQLOLEDB',

'SERVER=REMOTE_SERVER;DATABASE=pubs;Trusted_Connection=Yes;Integrated Security=SSPI',

'SELECT @@SERVERNAME') AS a

The result cameout as REMOTE_SERVER and not local server.

Try this syntax and let me know whether it works.

pintail SSC-Addicted Points: 483 More actions · Answer 4

Thank you very much. This syntax is working great.

First suggestion did created connection to local server with windows authentication. Consider it a step closer to final solution, as previously that was also not working for me.