Having problem connecting to MS SQL in Planning Datasource Creation

Having problem connecting to MS SQL in Planning Datasource Creation

Bee
Bee

October 30th, 2011, 4:53 am #1

Hi,

I'm using Planning 11.1.2.1 with MS SQL 2008. I am attempting to create a datasource for Planning, but I get "Connection Test Failed" when I enter all relevant information. I've created a new DB using SQL Management Studio, and I'm using the SQL admin userID/Password as the owner of the DB for now.

Since I can log into the SQL Management Studio, my credentials are correct. I also used the same SQL server for the installation of the EPM. I've verified the connection by creating a User DSN on Windows ODBC, and its test connection is successful.

Can anyone provide an insight to this issue?

Thank you

Bee
Quote
Share

Cameron Lackpour
Cameron Lackpour

October 30th, 2011, 7:51 pm #2

I'm pretty sure it needs to be SQL Server authentication to work -- I know I've shot myself in the foot on that one at least once.

I think you also need to have dbowner rights so Planning can write the tables.

Regards,

Cameron Lackpour
Quote
Share

Bee
Bee

October 31st, 2011, 4:36 am #3

Thank you for the suggestion, Cameron.

So the user is SQL local authenticated user, not MSAD.
The user is dbowner of the DB too.

I'm still very confused. Need to check the log file to see if it has anything notable.



Quote
Share

Joined: November 26th, 2001, 10:15 pm

October 31st, 2011, 6:11 pm #4

Sorry, I don't have 2008 but it shouldn't be any different.

For the SS dbs that my id (this is a dev environment) has access to, I see:
Role name: db_owner with Role Members myusername and dbo

When I look at Security and Logins, and I look at my myusername Login, I see dbname, myusername, and a Default Schema of dbo. The database role membershipt for myusername is db_owner and public.

I suspect you have all of the above.

Perhaps your issue isn't truly in the security/access of the username.

I once (I hate to admit this but it's true) spent *hours* on this only to find I was mistyping the server name. <Deep blush>.

If you're lucky, maybe it's something as dumb as that.

Regards,

Cameron Lackpour

Quote
Like
Share

Bee
Bee

November 1st, 2011, 4:48 am #5

Tried many times, verified the SQL side as you wrote, and also checked for any typos.. I can create ODBC connection using exactly the same information. Created another SQL ID and tried, but the result is the same. I wished that I could see some kind of log files to see what's exactly failing about it...

Thank you.
Quote
Share

Bee
Bee

November 1st, 2011, 5:06 am #6

I was just able to create the datasource. However, I'm still not sure exactly why.
I have been trying this and failed:

AppLibrary -> File -> New -> DataSource
*This is where I was stuck by getting "Database Connection Failed".


I've also tried to login to the classic AppWizard, and all I saw was the app creation page with "No Datasource Found". That was my initial page and only thing I could do was to logout.

I logged out and tried the AppWizard again immediately after above, and I saw a different page. It had the "Create DataSource", "Edit DataSource", "Manage Datasource" buttons. When I clicked on "Create Data Source" button, it went back to the same loop of "No Datasource Found" and had to log out.

I tried it again, and luckily the buttons were there. I decided to try "Manage Datasource" button, and it was doing something for a while (~1 min?), then it got to the datasource creation page with SQL and Essbase entries. I was traumatized with the SQL side, so I tried Essbase part first. Validated without any problems.

Now the SQL side.... same ol' information I typed in dozens of times. Tada~, it validates. So from there, it's been working like nothing ever happened.


Very confused, but it's working....for now.

Thanks for the input, Cameron.

Bee
Quote
Share

Bee
Bee

November 1st, 2011, 6:18 am #7

So now that I got the Datasource nightmare out of the way, I thought that things will go smoothly. Of course NOT!

I created an Application in EPMA with simple dimensions that validates, and I tried to deploy. When I get to the "DataSource" section, it's blank. What can I do? I can only create the datasource that brings me back to the problem that I was just suffering. I get the "Database Connection Failed".

I also noticed something peculiar...I didn't see "Planning" in the Shared Services project. So I went into the Shared Services, Application Groups, and attempted to add one. I was able to add Planning with my "Plan1" that I created. However, I get an error

"EPMLCM - 30053: Failed to connect to ..../servlet/HspLCMServlet" while performancing explore/listing for application - "Plan1". Received status code - "404" with error message - "Not Found". Possible cause of error Server down or not reachable."

I can access Plan1 with the classic app, but I cannto see the Application in the Workspace.

Any advice is appreciated.... Thx,


Bee
Quote
Share


Confirmation of reply: