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!!
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bucball2007Author Commented:
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...
Bhavesh ShahLead AnalystCommented:

Can you share error screen?

It might helps us.
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

bucball2007Author Commented:
here's the error we're seeing
bucball2007Author Commented:
Thanks--unfortunately no luck with either of those :(
bucball2007Author Commented:
I opened a Microsoft support case on this one--will update the post if we can figure out what the hangup is...thanks!
bucball2007Author Commented:
couple of things were going on here--there was a schema in there that was invalid.  It was trying to assign ownership to a schema that technically didn't exist in this instance--which was reallly wierd.  Also, we changed around the security, enforced some group policy and backed out of the SQL roles--which ultimately got us a connection.  How this got so convoluted I don't know, but we finally got to the bottom of the issues!  Thanks for the suggestions!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bhavesh ShahLead AnalystCommented:

Thats good, you got the solution

bucball2007Author Commented:
Microsoft helped to isolate the issues and get us reconnected
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.