I don't believe it's possible to get the execution plan via OPENQUERY() remotely, and the only way would be to connect to the server directly and run the query there, unfortunately.
That being said, you should definitely see an instant performance improvement for all of your linked server queries, if you migrated the remote server data to the local server (e....
What is Allow Inprocess : as the name says by selecting Allow Inprocess, SQL Server allows the provider to be instantiated or allows the provider to run as an In Process server. When the option is not set, the default behavior is to allow the provider to run outside the SQL Server process.
The question that arises is what is the benefit of running the ...
Even if you manage to accomplish EXEC master.dbo.sp_addlinkedserver and EXEC master.dbo.sp_addlinkedsrvlogin, you will face another problem: you won't be able to use OPENROWSET or OPENDATASOURCE unless you're a database administrator.
You can create a registry setting on the database server that enables this AdHoc functionality.
I found a support.oracle.com Doc ID 1519958.1 that describes the issue.
For solution it says: Use the relatively simple work around of linking to a view that is based upon the table, instead of directly to the table.
Apparently this issue has been around since 2012.
As a DBA I've experienced more SPN issues than I'd care to imagine. For us, the SQL account we are allowed to use to install SQL Server do not have permissions to write the SPNs to Active Directory. So, SQL uses the NTLM authentication instead. And from there some SPN issues do rise to the surface.
Having said all that, your question seems to arise ...
You have to verify these requirements:
AD Trust between source domain and target domain
Proper AD SPN registration at least for the linked server source instance
You have to verify proper DNS resolution in both domains for TGT requests (Are they in the same AD forest?
Sorry but there is not an easy answer and to set all these things you must have Domain ...
From your client (e.g. PC), when you log into ServerB, and run the following, what do you get?
SELECT net_transport, auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@SPID
Ideally, you will see something like TCP and Kerberos. If you see Shared memory, you need to run this again from a remote client (as that only shows up with connections made ...
You have already stated, respectively listed what the issue could be:
OLE DB provider "SQLNCLI11" for linked server "LinkedServerName" returned message
"Cannot generate SSPI context".
Msg -2146893044, Level 16, State 1, Line 0 SQL Server Network Interfaces:
The logon attempt failed
So what is SSPI? Microsoft describes it as:
This error usually comes when the Windows user account is expired and he is already logged in with old password. Just ask the user to restart his machine and check if the password is expired or he has changed the password. Hope this helps!!!!!