Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm getting these errors when testing a linked server from SQL Server 2012 to SSAS 2012 (both servers are Windows 2012 and clustered)

"An error was encountered in the transport layer" and "The peer prematurely closed the connection"

I've spent 2 days on this now and has been swung towards Kerberos. I've enabled Kerberos in AD for all of the computer names involved which are host names, SQl cluster names and windows cluster name and have SETSPN for the SQL Server cluster name and SQL Server cluster name FQDN against the service account used.

The same service account is running SQL and SSAS and is a SysAdmin on each in security. The linked server works fine if I RDP onto Server1 and test the linked server to Server2 but it won't work from anywhere else - there are no firewalls in between these servers and windows firewall is disabled on both.

To create the linked server on Server1 (SQL Server) I have followed all links on other msdn forum posts and this is what I used in order to connect to Server2 (SSAS)

EXEC master.dbo.sp_addlinkedserver 
@server = N'LinkedServerTest', 
@srvproduct='', 
@provider=N'MSOLAP', 
@datasrc=N'Server2', 
@catalog=N'TestCUBE'

EXEC master.dbo.sp_addlinkedsrvlogin 
@rmtsrvname=N'Server2',
@useself=N'True',
@locallogin=N'Domain\ServiceAccount',
@rmtuser=NULL,
@rmtpassword=NULL

Really hit a brick wall with this now any help is appreciated. Just to add SQL browser is running on both and both servers are using default instances, Server1 is SQL Server etc and Server2 is literally SSAS installed, nothing else.

Just to add to this I've installed the MSOLAP.5 provider as it's part of the SQL Server 2012 SP1 feature pack and it still doesn't change my situation and we use the same service account for Server1 (SQL Server Service) and Server2 (SSAS Service) here

share|improve this question
add comment

1 Answer

Definitely sounds like a double hop (Kerberos) issue. When you're rdp'd into the box, then the cube is only 1 hop away so I'd guess that's why it works.

Start a profiler trace on the analysis services server, then query the linked server. Check the NTUsername field in the trace. If Kerberos Delegation is failing, the NTUsername will be something like "ANONYMOUS LOGON".

From Server1, what does the following return?

select auth_scheme from sys.dm_exec_connections where session_id=@@spid

What Delegation permissions did you set up in AD for the machines?

Does the spn match the server name you are trying to connect to? If you have alias's, cnames, etc, you'll need an spn for each that you wish to connect to via kerberos.

Also, try out the Kerberos Configuration Manager to verify your settings. This can be particularly effective for reviewing what SPNs actually exist.

share|improve this answer
    
Kerberos is returned from auth_scheme. Delegation has been enabled for the SQL cluster (network name) for each server and set as ‘Trust this computer for delegation to any service (Kerberos only) in AD. SPNs have been set for MSOLPSvc.3/machinename domain\serviceaccount and MSOLPSvc.3/machine.FQDN domain\serviceaccount We have tried the Keberos config manager and it doesn’t really help. From what I can gather it just tells you if a SPN exists or not and not what to actually troubleshoot/do? –  Tom Oct 28 '13 at 11:48
    
Ok. Sounds like you're pretty close then. What about the profiler trace. Did you run that? If so, is the NTUsername showing up as correct or anonymous? –  brian Oct 28 '13 at 13:35
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.