Windows 7 connection to Analysis Services

Question

Hi, 

I recently updated to Windows 7 and trying to connect to my cube located at: https:/servername/olap/msmdpump.dll.

I receive an error: Unable to connect to a data source. Reason: Access denied. You either made mistake typing in your UserID and/or Password, or you do not have permission to access the database servers.

The connection works on Vista. To connecto to Analysis services I use Excel. I have already tried following:

a) removed & installed Vista ODBC driver

b) troubleshoot firewalls

c) tried different version of Excell

d) tried different laptops with different OS's

It only works on XP, Vista but for some reason I can't connect using Windows 7.

Any advise would be much appreciated.

Thanks

Laco

Answer 1

how do you connect to your cube? excel? SSMS?
have you tried to use "Run As Administrator"?
otherwise your credntials will not be passed to SSAS (at least thats how it was in Vista)

greets,
gerhard

---

- www.pmOne.com -

Answer 2

thanks for the reply,

I am using Excel to connect to it. The same way I used it in Vista. It did not require to use "Run as Administrator".
If I take two laptops (Vista & Win7) I can connect with Vista but no Win7.

If I test connection to internal cubes, it works on both systems. It seems it does not work only while connection over the internet.
It might sounds ridiculous but is there any way that SQL server would not support Win7 drivers? (eg. SQL 2000)

Regards
Laco

Answer 3

do you use SQL 2000?
otherwise you could use profiler to get more information about the error
(which user is connecting?, which error(if any)?, ...)

it could be that win7 does not support the SQL 2000 driver - havent tested that yet

greets,
gerhard

---

- www.pmOne.com -

Answer 4

Laco,

I am seeing *exactly* the same issue.  I have debugged a little further, in that I have proven that SSMS (on Win7) *can* connect to the SSAS server (i have 2005 and 2008 SSAS, both running on an 08 EE server), but nothing I do will let my Excel 07 running on WIn7 get to either SSAS 05 or 08.

Anyone with ideas of what this could be, and of course, how to fix it, please post!

steve.

Answer 5

Fixed for me - the server was missing SPN's.  Followed Joey's lead as per this blog post -> http://blogs.technet.com/proclarity/archive/2009/08/07/warning-the-security-database-on-the-server-does-not-have-a-computer-account-for-this-workstation-trust-relationship.aspx

Answer 6

Steve

Thanks for the post . I have not tried it yet but I feel it might do it for me.

However a roundabout way of sorting out connectivity with EXCEL is to use the the Microsoft SQL Server 2008 Analysis Services 10.0  OLE DB Provider in your connection and use specific user credentials rather than Integrated security.

This will work for you.

The following is an example of the connection that worked for me.

Provider=MSOLAP.3;Password=biadmin;Persist Security Info=True;User ID=YOURDOMAIN\BIAdmin;Initial Catalog=DATABASE;Data Source=SQLSERVER;Location=SQLSERVER;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error

Panos

Answer 7

I think although this is in the correct direction I think it is half the story.

Please follow the link to an excellent article which also shows the way to set up Kerberos delegation - important if you need this to work

http://www.mosha.com/msolap/articles/kerberos_delegation.htm

Panos

Answer 8

There are two separate issues for Windows 7:

1. Connecting using Excel over HTTP. This should work, but you must be on Windows 7 RTM -- there was a bug in WinHttp which was fixed in RTM. If you're on the RC, then you will have a problem.

2. Connecting using Excel over TCP. There is a known issue with this, and the workaround here until a patch is available is to use Kerberos. This issue shows up an error about "workstation trust relationship" -- it is caused by a custom security module that is installed by the Windows Live Sign In Assistant (SIA), so another alternative is to uninstall that temporarily but it can cause the Office Live Addin to not work as expected.

However, the original error message about access denied would appear to be independent of these issues. You might want to re-install the MSOLAP90/MSOLAP100 OLE DB provider and try again -- also you should try using SSMS on the box to see if you can connect over HTTP to see if it is a connectivity issue or a provider issue.

HTH,
Akshai

---

-- This posting is provided "AS IS" with no warranties, and confers no rights

Answer 9

plevantis:

Wouldn't MSOLAP.4 be the provider to access SSAS 2008? MSOLAP.3 tries to find SSAS 2005.

Regardz,

Methody

Answer 10

Both MSOLAP.3 and MSOLAP.4 are able to connect to both AS 2005 and AS 2008. Of course it's always better to use MSOLAP.4 if it is installed, because it has more bug fixes and support for some new functionality in AS 2008.

---

-- This posting is provided "AS IS" with no warranties, and confers no rights

Answer 11

I don't know where is the problem, but I have the same problem on my Windows 7. I tried to enabled kerberos, disable firewall, add a new hotfix, but I have the same error.

Any thing else? Any ideas?

---

Alberto

Answer 12

FYI, there is now a KB article available that describes the "workstation trust relationship" error I mentioned in my post. We anticipate that a hotfix will be available in the near future.

http://support.microsoft.com/kb/975651

Thanks,
Akshai

---

-- This posting is provided "AS IS" with no warranties, and confers no rights

Answer 13

Yes, I found a solution uninstalling Windows Live Sign-In Assistant. http://blogs.gsc.es/adm/archive/2009/09/21/error-al-conectarnos-a-un-analysis-services-desde-windows-7.aspx

Thanks for your post, I don't know that there was a KB for it.

---

Alberto

Answer 14

Yes, I found a solution uninstalling Windows Live Sign-In Assistant. http://blogs.gsc.es/adm/archive/2009/09/21/error-al-conectarnos-a-un-analysis-services-desde-windows-7.aspx

Thanks for your post, I don't know that there was a KB for it.

---

Alberto

Alberto (and Akshai) you saved me a lot of time!!!! Thanks a lot.. I had tried everything but nothing seemed to work (I am using SSMS on Windows 7 RTM and was trying to connect to Analysis Services on a Windows Server 2003 box). Not a Keberos delegation problem, not a network driver problem... it was the Windows Live Sign-In Assistant all along!!!

Thanks a lot and I am proposing this as the solution here and Akshai's post too since it is the same solution.

---

alas

Answer 15

Uninstalling Windows Live Id sign in assistant also resolved this issue for me. Thank you!! 

Answer 16

Thanks a lot Alberto! I removed  Windows Live Sign-In Assistant and it really helped. (I was not able to see Data Analysis cube on Project server 2007 on Windows 7)

Answer 17

FYI, the KB was moved and a Hotfix is available. I expect it will get pushed out by a Windows Update at some point in the future.

http://support.microsoft.com/kb/976419

Thanks,
Akshai

---

-- This posting is provided "AS IS" with no warranties, and confers no rights

Answer 18

Hello, I just installed this hotfix and restar the machine, and I have the same problem. I cannot connect to my Analysis Services from my Windows 7.

---

Alberto

Answer 19

And is it the same "workstation trust relationship" error that was mentioned by the hotfix? My guess is that you just have some other connectivity issues -- perhaps you need to re-install OLE DB, or check the firewall, etc...

---

-- This posting is provided "AS IS" with no warranties, and confers no rights

Answer 20

Hey Alberto -

I just gave the hotfix a shot and it worked. I hope there is something that comes up in the future that fixes your issue.

mb

Answer 21

I don't have much time to find the problem, but I installed the hotfix and I have the same problem with the authentication.

---

Alberto

Answer 22

Had the same problem already for 2 times (same computer, different install).

http://support.microsoft.com/default.aspx/kb/976419?p=1 worked for me.