Failed connecting to Database from BiDS cube

moosetracker
moosetracker used Ask the Experts™
on
I am just learning. From one textbook I have one database that connects.. Now a new book different Database, different cube. This deploy does not work. I have tried to copy the other connection. the .ds says the connection is successful when I hit the test.
Yet when I deploy I get this error.

"Error      6      OLE DB error: OLE DB or ODBC error: Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.; 28000; Cannot open database "AdventureWorksDW" requested by the login. The login failed.; 42000"

I have migrated to the database and have added full permissions for "NETWORK SERVICE"

I am at a loss as what to try next..
Connection1.png
Connection2.png
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
I found that part of the cube installed, but you couldn't open it to do an MDX something about the metadata of the cube not being there.. (So basically the deploy just can't pull the data out of the AdventureWorksDW database.

Then following advice from a google search I in configuration manager, I changed from network service to local Service, and the deploy works with this, and although at home practicing, this will work, I would prefer staying with Network service because this is what my companies SQL server is set to, and I would like to practice as close as possible to what I will hit when I start trying to work it at my office.

Author

Commented:
I lied, I thought the local Service worked.. The Deploy was successful after that (and I set it for a full deployment).. but then only a quarter of the fields showed up, and when I attempted to drag & drop into rows & columns in the browser the program froze.
I removed the cube and rebuilt it. Then tried to deploy and it errored out just like the "network service" did.
Consultant, Trainer
Commented:
Hi,

did you still have problem?
go to BIDS
double click on adventure works.ds
go to Impersonation Information
select : use specific windows user name and password
enter username and password of administrator
then OK
then deploy your project again.

Author

Commented:
Thanks,
I have so many (works, and don't work) sinerios.. I got this to work on one go round, then it didn't, then I got then windows authentication to work.. Right now I am working with Network / Windows auth.. But.. give it time, and that may not work again.

I don't have this problem with Bids for SSIS at work, so I am hoping it will be a none issue with the server at work.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial