Why We Can’t Connect Anymore?
New OS (Windows 7 in this case) and new issues when trying to connect to Analysis Services on a remote server.
I installed Windows 7 and added it to a corporate domain. I can use SSMS to connect to some SSAS servers but there was one server (the most important of course) which refused the connections with the following dreadful message which I am sure you’ve seen somewhere along the line:
TITLE: Connect to Server
——————————
Cannot connect to <servername>.
——————————
ADDITIONAL INFORMATION:
The connection either timed out or was lost. (Microsoft.AnalysisServices.AdomdClient)
——————————
Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. (System)
——————————
An existing connection was forcibly closed by the remote host (System)
I was able to connect to that server from other non-Windows 7 machines so I narrowed the issue to Windows 7. Interestingly, running the SQL Server Profiler on the server showed that the right Windows identity was passed so the issue wasn’t with the server itself either. Even more interesting, I had no problems connecting to SQL Server Database Engine on that server using Windows Authentication. Attempting to connect to the server using the SQL Server Profiler on the Windows 7 machine or attempting to browse the cube in BIDS would shown another enlightening message:
“The security database on the server does not have a computer account for this workstation trust relationship”
Binging the internet showed that Kerberos doesn’t agree with Windows Server 2008 on the server and Windows Vista (or Windows 7) on the client. But, in my case, the server was running Windows Server 2003 with SP2. More binging and an Eureka moment after reading KB 976419 which luckily has just been released.
Windows Live ID Sign-in Assistant 6.5 installs a new Security Support Provider (SSP) package. The SSP package causes the Security Support Provider Interface (SSPI) that is used by the Analysis Services OLE DB provider not to switch to NTLM authentication.
You gotta be kidding me! So, the unassuming Live ID Assistant, which every Live application out there recommends, nukes NTLM connectivity to Analysis Services? In case you are interested, since a hotfix wasn’t available yet, I went with method 2, which unregisters the livessp provider, and the problem when away.
I got to say that for the past two years I had my fare share of connectivity issues with Analysis Services. It looks to me that the Analysis Services team has to go back to the OLE DB Provider drawing board and solve these issues. If I can connect to the Database Engine, I should be able to connect to Analysis Services.
