Oracle connection from MS AS 2000

[ol...@gmsbv.nl]

Hi,

Any idea why it is possible to work in MSAS2000 and see the data from
an Oracle table but when processing with a Microsoft for Oracle /
Oracle OLE DB ODBC driver then there is always an error.

Regards, Marco

[michael v]

Why not transport using DTS...

Lots of oracle conversion trouble that you need to deal with.

datetime, row size limit

<ol...@gmsbv.nl> wrote in message
news:1129637794....@g47g2000cwa.googlegroups.com...

[Dean Adam]

What errors are you seeing?

Dean.

[Dave Wickert [MSFT]]

This is actually a common conceptional problem which happens even if you are
using a SQL Server data source.

When you are creating objects with Analysis Manager, looking at data, etc.
then you are using the interactive account running under Analysis Manager as
your security environment. However, when you process a dimension or
partition, it isn't your process which is doing the work -- it is the
MSSQLServerOLAPService service itself. This introduces several common
mis-configurations. For example, if you have the service configured to be
running under the Local System account, then it will be denied network
access (this is a characteristic of Local System that it is unable to touch
any network resources), or if you are using a domain account, it might not
have access to the remote machine, or have access to Oracle, etc.

The give-away is that you said this happens only when processing -- as soon
as I heard that, this mis-configuration immediately came to mind.

Hope that helps.
--
Dave Wickert [MSFT]
dwic...@online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

<ol...@gmsbv.nl> wrote in message
news:1129637794....@g47g2000cwa.googlegroups.com...

[ol...@gmsbv.nl]

But what is the solution to solve this.

I found several issues like change the local system account of OLAP
services or change the rights to the Oracle directory Home\Oracle ?

Is this also a problem in MS SQL Server 2005 ?

Regards, Marco

Dave Wickert [MSFT] schreef:

[ol...@gmsbv.nl]

But what is the solution to solve this.

I found several issues like change the local system account of OLAP
services or change the rights to the Oracle directory Home\Oracle ?

Is this also a problem in MS SQL Server 2005 ?

Regards, Marco

Dave Wickert [MSFT] schreef:

> This is actually a common conceptional problem which happens even if you are

[ol...@gmsbv.nl]

But what is the solution to solve this.

I found several issues like change the local system account of OLAP
services or change the rights to the Oracle directory Home\Oracle ?

Is this also a problem in MS SQL Server 2005 ?

Regards, Marco

Dave Wickert [MSFT] schreef:

> This is actually a common conceptional problem which happens even if you are

[roman...@adastra.cz]

Describe the error. We use MS OLEDB fro Oracle for many years without
troubles.

Roman Domin
Senior Consultant
roman...@adastracorp.com http://www.adastra.cz

[Dave Wickert [MSFT]]

Sorry. I thought that I said the solution. It is probably buried in the
explanation.

If this connection is to a remote server with Oracle on it, then the
solution is to use a domain account for your MSSQLServerOLAPService service
account which has the rights to access that server and that Oracle database.

Bring up Administartive Tools \ Services, navigate down to the
MSSQLServerOLAPService entry, double-click it and change the logon account.

By definition, the built-in Local System account is denied network access of
any type. This has been true in NT for years and years.

--
Dave Wickert [MSFT]
dwic...@online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

<ol...@gmsbv.nl> wrote in message
news:1129701509....@g47g2000cwa.googlegroups.com...

[ol...@gmsbv.nl]

We tried 3 connection from MS AS 2000 to Oracle 9. With all connections
we are able to use the wizard, see the Oracle tables and devlop cubes
and slicers. So far so good.

Now the last and most import one. Trying to process any cubes or slicer
is ending in an error with all 3 connections. Any idea why this is
happening, while the Wizards are working! I look forward to any
solution to be able to process cubes.

Errors:

Microsoft ODBC for Oracle driver:
Data source provider error: [Microsoft][ODBC-stuurprogrammabeheer]
SQLSetConnectAttr van het stuurprogramma is
mislukt;IM006;[Microsoft][ODBC driver for Oracle][Oracle]ORA-12154:
TNS:servicenaam kon niet worden herleid.;08001;

Microsoft OLE DB for Oracle:
Data source provider error: ORA-12154: TNS:could not resolve service
name;

Oracle Driver:
Data source provider error: Het opgegeven stuurprogramma kan niet
worden geladen vanwege een systeemfout 5 (Oracle in ORA92).;IM003;

Best Regards,
Marco Goeneveld
www.gmsbv.nl

roman...@adastra.cz schreef:

[roman...@adastra.cz]

It seams that the definition is not correct. Try to connect correctly
via Oracle tools - e.g SQL-Plus. Check also TNSNAMES.ORA.

Roman

[ol...@gmsbv.nl]

SQL-PLUS is ok, TNSNAMES.ORA is OK. The Wizards in MSAS2000 are OK

Only processing not.

I understand that it is something like users and security but what and
where is not clear for me.

I have made an access database as sample with linked tables to Oracle
and some make Table Queries.
These new tables coming from a query are now processed in MSAS2000 in a
fast way.

For a demo it works but for production it is not the way to work !

Any help is welcome to solve this problem. I know it is an old problem
but I tried a lot os settings but nothing works.

Regards, Marco

[Darren Gosbell]

As a test, not as a permenant solution.

Can you change the account that the MSSQLServerOLAPService is running
under to the user account you use when you log on to Analysis Manager.
Then try running a process, with any luck this should work.

If it does we know that the issue is a permissions problem with the
current account accessing the Oracle server. From there you will have to
compare the differences between the 2 accounts to find the issue.

HTH

--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

[roman...@adastra.cz]

I am not sure if Oracle uses Windows accounts (it should be posible
under some circumstances - but mostly it is necessary to use oracle
user and password - this must be specified in datasource definition.
Stupid question: is the allow save password checked ?

Regards

Roman Domin, Adastra

[Dave Wickert [MSFT]]

Sorry. I'm not an expert on the Oracle-side, but I do want to make a comment
about basic NT security that I haven't heard mentioned yet.

If Oracle is on a remote server, then you should make sure that the machine
running the MSSQLServerOLAPService must be under a domain account; not Local
System. One of the characteristics of Local System is that it is denied any
network access. This is regardless of what kind of connection or any other
authentication. Local System is not allowed to touch network resources --
you simply cannot get off the box using Local System. Using a domain account
also ensures that you can get INTO the Oracle server. If it is a Windows
server, then Windows has group policy settings again that may come into
play. Therefore regardless of what kind of connectivity to Oracle you are
doing, I would first ensure that I have a domain account that has access off
the network from the OLAP server; and into the machine on the Oracle side.

--
Dave Wickert [MSFT]
dwic...@online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

<roman...@adastra.cz> wrote in message
news:1129996696.3...@g49g2000cwa.googlegroups.com...

[ol...@gmsbv.nl]

It is still strange that the wizards in MS AS 2000 can show all data,
tables, etc in Oracle and you are able to make cubes, dimensions etc.
But processing no way. Why is the Manager and wizards working and which
security name are used at that moment ?

Regards, Marco

Dave Wickert [MSFT] schreef:

> Sorry. I'm not an expert on the Oracle-side, but I do want to make a comment

[Dave Wickert [MSFT]]

When you are using Analysis Manager to create objects and browse, you are
using your *interactive logon credentials*. However, processing an object
such as a dimension, cube or partition is an operation performed by the
system, i.e. the msmdsrv.exe. It is the Analysis Services' service which is
doing the processing. Thus its service logon credentials are used. This is
the basic way NT operations.

Sitting on-top of that is the way that Oracle accepts its network
connections -- but first you have to get off of the NT box. And NT does not
allow any process running Local System to touch any network resource. This
is done for your protection. It is saying that a machine administrator has
the rights to only work with resources on its local machine and that it
cannot go out on the network.

To go out on the network you have to have security logon credentials which
allow it, i.e. a domain account -- NOT Local System.

Sorry. That is just the way NT works. Nothing at all to do with Analysis
Services.

--
Dave Wickert [MSFT]
dwic...@online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

<ol...@gmsbv.nl> wrote in message
news:1130046810.8...@g43g2000cwa.googlegroups.com...

[Kizi M.]

Ok.

So Im having a rather simple problem. I just installed MSAS and am
trying setup a new DSN in the 'Data Sources' folder. I specify 'Oracle
Provider for OLE DB' and test the connection. It gives me an error "
..initializing provider :ORA-12154: TNS:could not resolve service
name".

It gives me this error regardless of the username/pwd I use.
I also went into services and assigned MSOLAPServices a domain account.

Any suggestions will be appreciated.

Kizi.

*** Sent via Developersdex http://www.developersdex.com ***