Link to home
Start Free TrialLog in
Avatar of bucball2007
bucball2007

asked on

Establishing a connection between MS Excel Data Mining tools and SQL Server

Hi experts,

We're having an issue getting a connection from end users running Office 2007 Excel and the Data Mining add-ins to SQL Server 2008 Database engine and Analysis Svcs.

I had this working awhile back, and the server crashed, so I had to reconfig everything.  I'm missing something somewhere that it won't work this time around.

The end user is getting this error when he is in Excel working off the data mining tab:
"Unable to query schema rowset on 'Tables.'  Errors in the high-level relational engine.  A connection could not be made to the data source with the DataSourceID of 'Data Warehouse Version0', Name of 'Data Warehouse Version0'.  The following system error occured: Logon failure: unknown user name or bad password.  The editor will switch to edit query mode."

So the user has already built a connection to this database already in the mining tools using the button on the ribbon.  If you hit 'test connection' it comes back succeeded.  But if he clicks anything like cluster, classify, query, etc. in the data mining tools...he can't actually DO anything.  The error stated above pops up.  Before, he'd click 'cluster' for instance, then choose the 'external data source' option, and then he'd see a listing of all the tables/views available for his use and he'd pick what he wanted.  Instead now he gets the error and the area comes up blank, so he can't get anywhere.  

I added him as a user on the database in the SQL server Database engine, and gave him datareader/datawriter.  Also in Analysis Svcs, I created a role, added him to it, and made sure he's got db creator rights, and then I gave read or read/write rights where available for the db to him.

What might I have missed?  Thanks!!
Avatar of Bhavesh Shah
Bhavesh Shah
Flag of India image

Avatar of bucball2007
bucball2007

ASKER

Thanks Brichsoft, good info, and I tried manipulating the data source and toggling the connection options to see if any of them worked, but no luck as of just yet.  I think we're on the right path though. I also went to the server and did the right click and manage to see the users/groups and what the end user was in.   Any other ideas??  Thanks again...
Hi,

Can you share error screen?

It might helps us.
here's the error we're seeing
ScreenHunter-01-Oct.-22-14.01.jpg
Thanks--unfortunately no luck with either of those :(
I opened a Microsoft support case on this one--will update the post if we can figure out what the hangup is...thanks!
ASKER CERTIFIED SOLUTION
Avatar of bucball2007
bucball2007

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
=)

Thats good, you got the solution

enjoy
Microsoft helped to isolate the issues and get us reconnected