CSS SQL Server Engineers

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

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

  • 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

 

  1. 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:

<Security>

<DataProtection>

<RequiredProtectionLevel>1</RequiredProtectionLevel>

</DataProtection>

<AdministrativeDataProtection>

<RequiredProtectionLevel>1</RequiredProtectionLevel>

</AdministrativeDataProtection>

to

<Security>

<DataProtection>

<RequiredProtectionLevel>0</RequiredProtectionLevel>

</DataProtection>

<AdministrativeDataProtection>

<RequiredProtectionLevel>0</RequiredProtectionLevel>

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

  1. 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)
  2. 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)
  3. 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.
  4. 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).
  5. 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)

 

John Desch

Microsoft SQL Server Escalation Engineer

 

Leave a Comment
  • Please add 1 and 5 and type the answer here:
  • Post
  • SSAS 2008 Deployment: The connection either timed out or was lost

  • Are there any updates on the timeline for a fix? Also, is this going to be a Windows hotfix or multiple hotfixes for individual products (SQL Server, Reporting Services, Excel, etc.)?

  • Is option (1) working for anyone?

    It is not an option for me to avoid a Kerberos situation and option (1) did not resolve the problem.

    I would like to know the timeline for this fix as well.

  • Any word on the fix for this?  August?  

  • hey,

    i found a patch here : http://denglishbi.spaces.live.com/blog/cns!CD3E77E793DF6178!1214.entry

    It seems to be working !

  • When I wanna download the fix, I only get the versions for Windows Vista, can I still install it on Windows 2008 64bit ?

  • Here are this and some other articles on Analysis Services and Kerberos:

    <a href="ssas-wiki.com/.../Articles

    ssas-wiki.com/.../Articles

Page 1 of 1 (7 items)