RamsThoughts

December 9, 2010 2:39 am

Computer account trust relationship – Excel to Analysis Server

 

This issue popped up on my computer several weeks ago on my Excel instance when  connecting to the SQL Analysis services. Enquired several people and searched several blogs.

Here is how I had installed the applications on my machine .

Client config
                Windows 7   [7600]
                Office 2010 
                VS 2010 ultimate

Sequence of installation, if that matters:
     Windows 7 –> Office 2010 –>  VS2010 –>  uninstall/reinstall Office 2010.

This is pretty straight forward, right? I don’t see any conflicts here of course, and the add-ins that were required were still in place. And I thought i didn’t have to reinstall VS2010 at this time.

Here is how i would connect the Excel instance to an Analysis services

1. Launch Excel
2. Data –> from other sources –> Analysis services
image 
3. Provide the server name on data connection dialog, and connect if you are connecting with windows authentication
image  
4. takes a while and boom – gives this following error message:

“The following system error occurred: The Security database on the server does not have the computer account for this workstation trust relationship”

clip_image001

5. well, apparently i tried logging in with the same user account but via providing UserName and the Password.
6. it would go through the first level ok and gives the list of cubes and perspectives available
7. select one of those and proceed to create a Pivot –> now again, gives the same error message as above….

It is at times frustrating when the message doesn’t say exactly what is wrong and how it can be fixed. It gets more frustrating if the issue wasn’t resolved for longer time.

Well anyways, finally found the solution – who would have thought the issue was something completely *repeat* completely unrelated in nature with an Excel application connecting to a SQL Analysis service, and where did Live ID assistant come into picture.

Yes, thanks to the following MSDN forum post – http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/b058635f-6d3e-4240-a555-1dbd945ae25a – that said three options
1. to update the kernel on the Server
2. check the windows Live ID assistant on client
3. connect as NTLM from the client machine

…and recommended #1. Well, why should I be looking at the server level configuration when i know another person could connect to  the same server and i was unable to. Well, I was also able to connect to the same server from different client machines and not from this current machine…  So, i chose option #2..

yes, uninstalled the Windows Live Id Sign in Assistant:
1. Launch Control Panel –> Programs and Features
2. look for Windows LiveID sign in Assistant
3. right click and uninstall
4. if cautions close all the applications suggested (Outlook, Visual Studio, etc)
5. Restart the machine
6. go over the Excel –> Analysis service connection process
7. wow! connects through and able to retrieve the data..

ufff! this hard just because the message was not that friendly and the event log didn’t have the right message?

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: