In article you will learn how to solve Kerberos encryption protocol related errors. “An error was encountered in the transport layer” or “The target principal name is incorrect. Cannot generate SSPI context.”
Problem 1
If you create linked server on SQL server targeting SQL Analysis Services (SSAS) using security setting “Be made the login’s current security context” and run “Test Connection” you get following error:
Cannot initialize the data source object of OLE DB provider “MSDASQL” for linked server “TEST” returned message “An error was encountered in the transport layer.”. OLE DB provider “MSOLAP” for linked server “TEST” retuned message “The peer prematurely closed the connection.”. (Microsoft SQL Server, Error: 7303)
Running SQL Profiler on SSAS server show NT AUTHORITY\ANONYMOUS LOGON which means that user can not be personalized.
Problem 2
If you try to connect to SQL server instance using SQL management studio (SSMS) you get following error:
The target principal name is incorrect. Cannot generate SSPI context. (Microsoft SQL Server)
At the same time in you Event Viewer you may see :
The Kerberos client received a KRB_AP_ERR_MODIFIED error from the server
Explanation
This error indicates that the service unable to decrypt the ticket that it was given. Meaning that source service encrypted ticket with one kerberos encryption protocol, but destination was not able to decrypt that ticket because used weaker decryption protocol. This could happen when source and destination server has different KerberosEncryptionType (aka msds-supportedencryptiontypes attribute). If this attribute is not specified for account then it will use weaker RC4 encryption protocol.
Before changing this setting should check what is allowed encryption protocol on source and destination servers.
Solution
Expect that you use service account or group managed service account (gMSA) which run SQL and SSAS.Check that SPN for SQL and SSAS services are in place.
1. Check if SPNs are in place
For SQL server you should have following SPNs:
MSSQLSvc/SERVER.FQDN:1433
MSSQLSvc/SERVER:NAME
For SSAS you should have following SPNs:
MSOLAPSvc.3/SERVER:NAME
MSOLAPSvc.3/SERVER.FQDN:NAME
MSOLAPSvc.3/SERVER
You can use Kerberos Configuration Manager which will guide you what SPNs you should have and how to install them.
2. Compare kerberos encryption protocols
Check source and destination server service account kerberos encryption protocol
Get-ADuser SERVICEACCOUNT -Properties * -server DOMAIN|select Kerb*
This means we have to configure AES128 and AES256 for second service account.
Set-ADUser -Identity SERVICEACCOUNT -KerberosEncryptionType AES128,AES256
3. Check allowed encryption protocols on server
Use Windows Group Policy to check enabled encryption protocols. You should check only those you use.
4. Check delegation (For problem 1)
In case of problem 1 when run “Test Connection” from SQL management studio happens double hop. Client -> SQL -> SSAS. Security for linked server contain “Be made the login’s current security context“. This means delegation should happen. To make things work you should configure destination SSAS to allow receive delegation from SQL server.
SSAS service account attribute PrincipalsAllowedToDelegateToAccount define AD group which members can perform delegation to SSAS.
get-aduser SSASSERVICEACCOUNT -Properties * -server DOMAIN | select PrincipalsAllowedToDelegateToAccount
So now when everything is in place add SQL service account as member of this group.
Add-ADGroupMember GROUPFORDELEGATION SQLSERVICEACCOUNT
You can read about how this work more here.
