This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

Updated Errors may occur after configuring Analysis Services to use Kerberos authentication on Advanced Encryption Standard Aware Operating Systems

psssql

3 Apr 2009 10:26 AM

Comments 7

The Microsoft SQL Server Analysis Services support team has seen an increasing number of issues involving errors when attempting to execute queries against or deploy databases to instances of Analysis Services 2005 and Analysis Services 2008 that are configured for Kerberos authentication and running on Windows 2008 Server or Windows Vista. This note provides information regarding the errors that have been reported and investigated.

What we've found is that when a client application is running on an operating system that is Advanced Encryption Standard (AES) aware (i.e. Windows Vista and Windows 2008 Server) and connects to an Analysis Services server that is running on an operating system that is AES aware then one of the following error return values or error messages may surface.

RETURN VALUE	RETURN CODE	MESSAGE
0X80090302	SEC_E_NOT_SUPPORTED	The requested Function is not supported
0x8009030f	SEC_E_MESSAGE_ALTERED	The message or signature supplied for verification has been altered

These problems can occur when all four of the following conditions are met:

1. The server operating system is AES Aware

2. The client operating system is AES Aware

3. The Analysis Services server is configured to support Kerberos authentication

4. Encryption/Decryption is performed using Kerberos SSP

Depending on the application that is being used, the error message that is actually returned to the user may vary somewhat. The table below illustrates some of the error messages that may be returned from various applications:

APPLICATION	MESSAGE
Business Intelligence Design Studio (Deployment)	The connection either timed out or was lost. Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. An existing connection was forcibly closed by the remote host
SQL Server Management Studio (Deployment)	The connection either timed out or was lost.
SQL Server Management Studio (Query)	The connection either timed out or was lost. Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. An existing connection was forcibly closed by the remote host
Reporting Services	Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset '<DataSet_Name>'. ---> Microsoft.AnalysisServices.AdomdClient.AdomdConnectionException: The connection either timed out or was lost. ---> System.IO.IOException: Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. ---> System.Net.Sockets.SocketException: An existing connection was forcibly closed by the remote host at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size) --- End of inner exception stack trace --- at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size) at System.IO.BufferedStream.Read(Byte[] array, Int32 offset, Int32 count) at Microsoft.AnalysisServices.AdomdClient.DimeRecord.ForceRead(Stream stream, Byte[] buffer, Int32 length) at Microsoft.AnalysisServices.AdomdClient.DimeRecord.ReadHeader() at Microsoft.AnalysisServices.AdomdClient.DimeReader.ReadRecord() at Microsoft.AnalysisServices.AdomdClient.TcpStream.GetResponseDataType()
Performance Point Server	Microsoft.AnalysisServices.AdomdClient.AdomdConnectionException: The connection either timed out or was lost. ---> System.IO.IOException: Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. ---> System.Net.Sockets.SocketException: An existing connection was forcibly closed by the remote host at System.Net.Sockets.Socket.Receive(Byte[] buffer, Int32 offset, Int32 size, SocketFlags socketFlags) at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size) --- End of inner exception stack trace --- at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size) at System.IO.BufferedStream.Read(Byte[] array, Int32 offset, Int32 count) at Microsoft.AnalysisServices.AdomdClient.DimeRecord.ForceRead(Stream stream, Byte[] buffer, Int32 length) at Microsoft.AnalysisServices.AdomdClient.DimeRecord.ReadHeader() at Microsoft.AnalysisServices.AdomdClient.DimeReader.ReadRecord() at Microsoft.AnalysisServices.AdomdClient.TcpStream.GetResponseDataType()
Excel	The Function requested is not supported
Excel	The message or signature supplied for verification has been altered

There are actually two distinct errors, which are discussed in somewhat greater detail below.

SEC_E_NOT_SUPPORTED:

This issue occurs when a call to Kerberos!SpUnsealMessage fails during an attempt to decrypt data for an input security buffer that is not properly aligned to the block size that is used by AES. In this case, the buffer is considered as fragmented and the decryption fails with the following error:

SEC_E_NOT_SUPPORTED

SEC_E_MESSAGE_ALTERED:

This issue occurs in an AES Kerberos!SpSealMessage/Kerberos!SpUnsealMessage exchange where the encrypted data is aligned on a 16 byte boundary and the message of the data is fragmented into multiple buffers which do not align on 16-byte boundaries. In this case, the call to Kerberos!SpUnsealMessage will return the following error:

SEC_E_MESSAGE_ALTERED.

When either of these errors is returned to the client or the server, the application that is attempting to decrypt the encrypted message will close the connection between the server and the client application.

These issues have been addressed in a Windows hotfix which is described in Knowledge Base Article 969083 (http://support.microsoft.com/kb/969083). Note that the fix for this issue must be applied to both server on which Analysis Services is running as well as any client machines that use the Analysis Server as a data source.

Alternative workarounds

Alter the server configuration settings to allow unencrypted incoming client connections by taking the following actions:

a) Using Notepad or another text editor, open the msmdsrv.ini file (Default path for Analysis Services 2005 is <%SystemDrive%>\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\CONFIG. Default path for Analysis Services 2008 is <%SystemDrive%>\Program Files\Microsoft SQL Server\MSAS10\OLAP\CONFIG)

b) Change from the default values of:

</DataProtection>

</AdministrativeDataProtection>

to

</DataProtection>

</AdministrativeDataProtection>

c) Stop and re-start the Analysis Server Service

d) In the client application add ";Protection Level=Connect" to the connection string

e) Testing these changes in a lab environment resolved the connectivity issues using both Excel and Reporting Services as the client application. These changes did not resolve the connectivity issue when using SQL Server Management Studio as the client.

If it’s possible to run both the client application (i.e. Excel, ProClarity, Microsoft Office SharePoint Server, Performance Point, Reporting Services, Excel Services, Project Server, Commerce Server, Custom Web app, HTTP Connectivity, etc.) and Analysis Services on the same physical server, it is not necessary to configure the Analysis Server to support Kerberos authentication, which will resolve the issue. (NOTE: This option will not work if a double hop is involved)

If it's possible to run both the client application (i.e. Excel, ProClarity, Microsoft Office SharePoint Server, Performance Point, Reporting Services, Excel Services, Project Server, Commerce Server, Custom Web app, HTTP Connectivity, etc.) and Analysis Services on the same physical server and the Analysis Server must be configured for Kerberos authentication, add either “;SSPI=NTLM” or “;Integrated Security=SSPI” to the connection string used by the client application to resolve the issue. (NOTE: This option will not work if a double hop is involved)

If connectivity necessarily involves a double hop where the client application (i.e. Microsoft Office SharePoint Server Performance Point, Reporting Services, Excel Services, Project Server, Commerce Server, Custom Web app, HTTP Connectivity, etc.) resides on one server and the Analysis Server resides on another server, Kerberos authentication is a requirement. Under those conditions, running either the client application or the Analysis Server on an operating system that is not AES aware (i.e. Windows 2003 Server or Windows 2000) will resolve the issue.

In single hop scenarios, avoid configuring the Analysis Server to use Kerberos Authentication, since this will force use of NTLM and avoid use of AES for encryption/decryption. (NOTE: This option will not work if a double hop is involved).

In single hop scenarios, add ";SSPI=NTLM" or ";Integrated Security=SSPI" to the connection string, which will force use of NTLM and avoid use of AES for encryption/decryption. (NOTE: This option will not work if a double hop is involved)