SQL Server - Linked Server - Using OPENROWSET with windows integrated security

3 votes

2 answers

22675 views

sql-server sql-server-2012 security linked-server openrowset

                          I have seen many articles regarding OPENROWSET using integrated security (Windows Authentication), but I could not make it work for me.

It is working fine using SQL Server authentication:

     select *
     FROM
     OPENROWSET('SQLOLEDB',
     'myserver';'monitor';'#J4g4nn4th4#',
     'SELECT GETDATE() AS [RADHE]')

But the same query I am trying to run using windows authentication, and it is not working:

    select *
     FROM
     OPENROWSET('SQLOLEDB',
    'myserver';'Integrated Security=SSPI',
     'SELECT GETDATE() AS [RADHE]')


Can someone please post an example that works?


Here is a good article  that describes OPENROWSET usage.




**Examples of working scripts using OPENROWSET - please read comments**

     ----------------------------------------------------------------
     -- this works - linked server REPLON1
     ----------------------------------------------------------------
     
    select *
     FROM
     OPENROWSET('SQLOLEDB',
    'Server=REPLON1;Trusted_Connection=yes;',
     'SELECT GETDATE() AS [RADHE]')
    
     select *
     FROM
     OPENROWSET('SQLOLEDB',
    'Server=REPLON1;Trusted_Connection=yes;',
     'SET FMTONLY OFF select * from sys.dm_exec_requests')
    
    
     SELECT a.*
    FROM OPENROWSET('SQLOLEDB', 'server=replon1;Trusted_Connection=yes;', 'SET
    FMTONLY OFF select * from sys.dm_exec_requests') AS a
    WHERE a.session_id > 50
    ORDER BY a.start_time desc
    
    
    
      ----------------------------------------------------------------
     -- this does not work - when using windows authentication
     -- apparently because windows server 2003 and windows server 2012 have problems connecting - related to SID
     -- it works fine using SQL Server Authentication
     ----------------------------------------------------------------
    
     select *
     FROM
     OPENROWSET('SQLOLEDB',
    'Server=SQLREPLON1\REP;Trusted_Connection=yes;',
     'SELECT GETDATE() AS [RADHE]')
    
    -- Msg 18456, Level 14, State 1, Line 1
    --Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
    
    
    
     ----------------------------------------------------------------
     -- this works - linked server SQLREPLON1\REP
     ----------------------------------------------------------------
    
     select *
     FROM
     OPENROWSET('SQLOLEDB',
     'SQLREPLON1\REP';'monitor';'#J4g4nn4th4#',
     'SELECT * from SAReporting.DBO.tblStockLedgerMovement')


                        

Asked by Marcello Miorelli (17274 rep)

Feb 19, 2015, 11:11 AM
Last activity: May 16, 2022, 08:14 AM

SQL Server - Linked Server - Using OPENROWSET with windows integrated security

Related Questions