Sql-server – Kerberos Authentication Issue

authenticationkerberossql serverssas

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

Best 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.

Related Question