OPENQUERY: Very tough question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Analysis Services (2000)

OPENQUERY: Very tough question

Author

Topic

Max Pumpe
Starting Member

2 Posts

Posted - 2004-09-21 : 12:01:20

Hi all,

This is a pretty tough one:

I have the following environment:
Servermachine 1:
Windows 2000 Server in Domain
SQL Server 2000 SP3a
Analysis Services 2000 SP3a
FoodMart 2000 Catalog
both services run unter the same local windows user account as administrator

Servermachine 2:
Windows 2000 Server in Domain
SQL Server 2000 SP3a
Analysis Services 2000 SP3a (not used in this topic)
service runs unter the same local windows user account as administrator as Machine 1

Client Machine:
Windows 2000 Pro
MSDE 2000 SP3a
MSDE runs under LocalSystem windows account

I am logged on to the user machine.
my login is administrator on the client machine and on both server machines

Now when I open query analyzer onto my local msde and execute the command:

select * from
openrowset('MSOLAP.2','DATASOURCE=Server1;CATALOG=FoodMart 2000','
select
{[Measures].[Unit Sales]} on columns,
order(except([Promotion Media].[Media Type].members,{[Promotion Media].[Media Type].[No Media]}),[Measures].[Unit Sales],DESC) on rows
from Sales
')

the command executes and gives back the table, fine.

Now when I connect query analyzer from my client machine to Servermachine 2 and execute the exact same command, the query fails:

Server: Msg 7302, Level 16, State 1, Line 1
Could not create an instance of OLE DB provider 'MSOLAP.2'.
OLE DB error trace [Non-interface error: CoCreate of DSO for MSOLAP.2 returned 0x80040154].

Ok, now I take Terminal Services and log on to the Server 2 using the same windows account, and start query analyzer on the terminal and execute the same query. Result:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSOLAP.2' reported an error. Access denied.
OLE DB error trace [OLE/DB Provider 'MSOLAP.2' IUnknown::QueryInterface returned 0x80070005: Access denied.].

Now my question: why does the openquery run on my local machine's sql server, but not on any other sql server ?

Why do I get different error messages when I execute the query from my local client machine and from the terminal, although I use the exact same windows login ?

Please help
Max

chajdu
Starting Member

1 Post

Posted - 2006-08-31 : 11:53:06

quote:
Originally posted by Max Pumpe

Hi all,

This is a pretty tough one:

I have the following environment:
Servermachine 1:
Windows 2000 Server in Domain
SQL Server 2000 SP3a
Analysis Services 2000 SP3a
FoodMart 2000 Catalog
both services run unter the same local windows user account as administrator

Servermachine 2:
Windows 2000 Server in Domain
SQL Server 2000 SP3a
Analysis Services 2000 SP3a (not used in this topic)
service runs unter the same local windows user account as administrator as Machine 1

Client Machine:
Windows 2000 Pro
MSDE 2000 SP3a
MSDE runs under LocalSystem windows account

I am logged on to the user machine.
my login is administrator on the client machine and on both server machines

Now when I open query analyzer onto my local msde and execute the command:

select * from
openrowset('MSOLAP.2','DATASOURCE=Server1;CATALOG=FoodMart 2000','
select
{[Measures].[Unit Sales]} on columns,
order(except([Promotion Media].[Media Type].members,{[Promotion Media].[Media Type].[No Media]}),[Measures].[Unit Sales],DESC) on rows
from Sales
')

the command executes and gives back the table, fine.

Now when I connect query analyzer from my client machine to Servermachine 2 and execute the exact same command, the query fails:

Server: Msg 7302, Level 16, State 1, Line 1
Could not create an instance of OLE DB provider 'MSOLAP.2'.
OLE DB error trace [Non-interface error: CoCreate of DSO for MSOLAP.2 returned 0x80040154].

Ok, now I take Terminal Services and log on to the Server 2 using the same windows account, and start query analyzer on the terminal and execute the same query. Result:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSOLAP.2' reported an error. Access denied.
OLE DB error trace [OLE/DB Provider 'MSOLAP.2' IUnknown::QueryInterface returned 0x80070005: Access denied.].

Now my question: why does the openquery run on my local machine's sql server, but not on any other sql server ?

Why do I get different error messages when I execute the query from my local client machine and from the terminal, although I use the exact same windows login ?

Please help
Max

phoenix522
Starting Member

1 Post

Posted - 2006-11-01 : 10:46:09

I had this problem at work and posted a possible solution on my blog.

Without rewriting the entire post, I’ll just sum it up. It is a permissions issue. In your post, you say that you are using the local Administrator account on all of your boxes. The local administrator account on one box does not have permissions on the other boxes so that is a major part of the problem.

Where I work, security is crucial. We are ITAR regulated as well as many other regulations so we take security very seriously. That being said, we run our SQL Server services under a service account defined on the domain. When a user gets a shared database, they are granted permissions to the development database via their domain NT ID. This means they have dbo on the database but they do not have permissions to the server at any level. Not even read/execute. Because the Oracle linked server creates an instance of the Oracle client every time a user connects to the linked server, you must have read/execute to the Oracle client. Without that level of permissions you will see this error. You can assign this by right clicking the Oracle client folder on the SQL Server and setting the permissions for your DOMAIN NT ID and using that domain account to run your openquery statements.

Now here is a neat caveat to the problem. If you have a SQL Server authenticated account instead of an NT ID, you will be able to access the linked server because the connection runs under the service account of the SQL Server which in most cases has administrator privileges on the box. If this does not appear to be the case, you might be running the SQL Server service as “SYSTEM”. If this is the case, make sure SYSTEM has read/execute to the Oracle client folder as well.

Since you are running as local administrator, you will not, in any case, have access to the remote SQL Server without an account on the SQL Server. If you are using SQL Server authentication with a domain account, that domain account will require read/execute to your Oracle client on the SQL Server.

As far as the “Access denied” error you get, when a linked server is created, you have the option to map domain accounts to the Oracle account. If this mapping is there and the radio button that states, “ignore users not in the list” then you will get this error unless your NT ID is in the mapping list. Since you are again using a local account, this is probably the case in your situation. If security permits, you can use the map everyone to this Oracle account option or you can add your NT ID to the list. This is much harder to do when you use a local machine account on a remote host though.

Here is a line of TSQL that will add a user to the linked server correctly.

EXEC sp_addlinkedsrvlogin ‘My_Linked_Server’, 'false', 'domain\user_id', 'Oracle_account', 'Oracle_Password'

Good luck!

Ron Hagerman

The most powerful person in any company is the DBA for the finance system

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources