Here is a funny issue with our OLAP Linked Server to Analysis Services. When I run “EXEC sp_testlinkedserver N’OLAP_SERVER'” or just right click on the linked server and hit “Test Connection”, the request takes *ALWAYS* 60 seconds and then it returns a Successful message. NO, it did not timeout, it SUCCEEDED. After 60 seconds, again, always.
- We have two domains: DomainA and DomainB;
- My laptop (the client running Management Studio) is in DomainB, Servers are in DomainA;
- I can connect to the server (RDP or SQL Server) using my DomainB account (again, even tho the servers are in DomainA);
- The linked server is set to “Be made using this security context:”, any other works fine;
- Same issue regardless of the domain of the user being passed above.
Under Event Viewer/Security, this is the message when connection is established:
A logon was attempted using explicit credentials.
Subject:
Security ID: DOMAINB\Rafael.Goncalez
Account Name: Rafael.Goncalez
Account Domain: DomainB
Logon ID: 0xCCCCCCC
Logon GUID: {00000000-0000-0000-0000-000000000000}
Account Whose Credentials Were Used:
Account Name: CubeLinkedServerUser
Account Domain: DomainA
Logon GUID: {xxxxxxxx-fe9c-xxxx-e7de-1d6ac10c4777}
Target Server:
Target Server Name: “SQL and AS Server”
Additional Information: MSOLAPSvc.3/”SQL and AS Server”
Process Information:
Process ID: 0x19e4
Process Name: C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\sqlservr.exe
Network Information:
Network Address: –
Port: –
- SQL Server and Analysis Services are in the same Server;
- We can connect to both, SQL Server and AS straightway immediate via Management Studio or RDP;
- The issue is gone for a few a minute after RDP;
- In one of the “Test Connection” executions, I signed out a RDP connection (likely during one execution) and I managed to get the following message:
OLE DB provider “MSOLAP” for linked server “Linked_Server_name” returned message “The following system error occurred: Key not valid for use in specified state. “.
Msg 7303, Level 16, State 1, Procedure sp_testlinkedserver, Line 1 [Batch Start Line 0]
Cannot initialize the data source object of OLE DB provider “MSOLAP” for linked server “Linked_Server_name”.
So far, the conclusion is that apparently SQL Server Linked server requires some sort of key to use explicit credentials within it and therefore SQL\Kerberos\AD searches for that key in the AD (likely?) for 60 seconds, when the search gets a timeout and SQL\Kerberos\AD decide to just create this key, and then it succeeds. If you RDP, this key is easy to find, once you sign out your RDP connection, the key is dropped.
Any feedback on this is welcome.
Thanks.
Rafael