This is for DBAs out there who are using OPENROWSET to collect data from multiple instances. I also found a new method.

Try to execute following before your script

execute as login = 'sa'
GO
select *
 FROM
 OPENROWSET('SQLOLEDB',
'Server=SQLREPLON1\REP;Trusted_Connection=yes;',
 'SELECT GETDATE() AS [RADHE]')

You will need sysadmin privileges for this to work.

This is for DBAs out there who are using OPENROWSET to collect data from multiple instances. I also found a new method.

Try to execute following before your script

execute as login = 'sa'
GO

so your full script would look like

execute as login = 'sa'
GO

select *
 FROM
 OPENROWSET('SQLOLEDB',
'Server=SQLREPLON1\REP;Trusted_Connection=yes;',
 'SELECT GETDATE() AS [RADHE]')

You will need sysadmin or IMPERSONATE ANY LOGIN privileges for this to work.

Disclaimer:

if you are executing from Query Window it will work. If you are running from a job, you need to make sa job owner. This might also need more steps involving SQL Server service account, SPN configuration but I have not tested it yet.