servers to UDB via IBMDADB2, access denied

A linked server definition in MSSQL 2000, can succcessfully authenticates
with a UDB database v8.1.0.61 on a SUN box, but then gets access denied,
eventhough the account being used has DBADM authority. However, I can
successfully authenticate and query the same UDB databases from a DOS DB2
command prompt on the same windows 2003 server.
See example below and results:
On the windoes 2003 server...
set nocount on
EXEC sp_addlinkedserver
@.server = 'srvr1833',
@.srvproduct = 'aeplm148',
@.provider = 'IBMDADB2',
@.datasrc = 'aeplm148',
@.location = 'srvr1833.dbms.gdomain.com:18330',
@.catalog = 'aeplm148'
go
EXEC sp_addlinkedsrvlogin
@.rmtsrvname = 'srvr1833',
@.useself = false,
@.rmtuser = 'User1',
@.rmtpassword = 'Password1'
go
EXEC sp_serveroption
@.server = 'srvr1833',
@.optname = 'data access',
@.optvalue = 'true'
go
SELECT *
FROM srvr1833.aepcclm148.epl.mline
Results:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'IBMDADB2' reported an error. Access denied.
OLE DB error trace [OLE/DB Provider 'IBMDADB2' IUnknown::QueryInterface
returned 0x80070005: Access denied.].
Yet on a DOS command line using db2...
db2 => connect to aeplm148 user User1 using Password1
Database Connection Information
Database server = DB2/SUN 8.1.6
SQL authorization ID = DGIEPL
Local database alias = AEPLM148
db2 => list database directory
System Database Directory
Number of entries in the directory = 1
Database 1 entry:
Database alias = AEPLM148
Database name = AEPLM148
Node name = XX
Database release level = a.00
Comment =
Directory entry type = Remote
Catalog database partition number = -1
Alternate server hostname =
Alternate server port number =
db2 => list node directory show detail
Node Directory
Number of entries in the directory = 1
Node 1 entry:
Node name = XX
Comment =
Directory entry type = LOCAL
Protocol = TCPIP
Hostname = srvr1833.dbms.gdomain.com
Service name = 18330
Remote instance name =
System =
Operating system type = None
db2 => select * from aeplm148.epl.mline fetch first 2 rows only
I_PLANT_PL I_STDY I_PRES_LINE I_PRES_TYP N_LINE_NAME
L_PLAN I_LOAD_TYP I_AUTOM_TYP I_PRES_LINE_SIZE I_PRES_MECHNM_TYP
L_
TCH_PL_LINE_TO_PLT_LINE Q_SHUT_HGT Q_NMBR_OF_STATNS I_UPD_TID D_UPD_LAST
X_COMN
More results followed but not pasted...
Can anybody offer some suggestions?
I've run out of ideas.
I know the linked server is authenticating properly because when I put in a
bad password, the error message returned says, bad password or userid.
One would assume that the problem is on the UDB server only. Yet I can
access the data from the Windows 2003 server using the UDB client in a DOS
prompt.
Both techniques authenticate properly. Only the linked server gets access
denied.
The driver version on the Windows 2003 Server is DB2 v8.1.12.99A clue was found.
This will work when you remote desktop to the server and log in as either
the administrators account to the account in which the database engine is
running under. But it will not work with any other account, even if that
account is part of the administrators group.
What is different about the "Administrator" account from an a account that
is simply a member of the Administor"s" group?
The account used by the sqlserver engine is also part of the administrators
group.
"DBA449" wrote:

> A linked server definition in MSSQL 2000, can succcessfully authenticates
> with a UDB database v8.1.0.61 on a SUN box, but then gets access denied,
> eventhough the account being used has DBADM authority. However, I can
> successfully authenticate and query the same UDB databases from a DOS DB2
> command prompt on the same windows 2003 server.
> See example below and results:
> On the windoes 2003 server...
> set nocount on
> EXEC sp_addlinkedserver
> @.server = 'srvr1833',
> @.srvproduct = 'aeplm148',
> @.provider = 'IBMDADB2',
> @.datasrc = 'aeplm148',
> @.location = 'srvr1833.dbms.gdomain.com:18330',
> @.catalog = 'aeplm148'
> go
> EXEC sp_addlinkedsrvlogin
> @.rmtsrvname = 'srvr1833',
> @.useself = false,
> @.rmtuser = 'User1',
> @.rmtpassword = 'Password1'
> go
> EXEC sp_serveroption
> @.server = 'srvr1833',
> @.optname = 'data access',
> @.optvalue = 'true'
> go
> SELECT *
> FROM srvr1833.aepcclm148.epl.mline
> Results:
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'IBMDADB2' reported an error. Access denied.
> OLE DB error trace [OLE/DB Provider 'IBMDADB2' IUnknown::QueryInterfac
e
> returned 0x80070005: Access denied.].
>
> Yet on a DOS command line using db2...
>
> DB2 => connect to aeplm148 user User1 using Password1
> Database Connection Information
> Database server = DB2/SUN 8.1.6
> SQL authorization ID = DGIEPL
> Local database alias = AEPLM148
> DB2 => list database directory
> System Database Directory
> Number of entries in the directory = 1
> Database 1 entry:
> Database alias = AEPLM148
> Database name = AEPLM148
> Node name = XX
> Database release level = a.00
> Comment =
> Directory entry type = Remote
> Catalog database partition number = -1
> Alternate server hostname =
> Alternate server port number =
> DB2 => list node directory show detail
> Node Directory
> Number of entries in the directory = 1
> Node 1 entry:
> Node name = XX
> Comment =
> Directory entry type = LOCAL
> Protocol = TCPIP
> Hostname = srvr1833.dbms.gdomain.com
> Service name = 18330
> Remote instance name =
> System =
> Operating system type = None
> DB2 => select * from aeplm148.epl.mline fetch first 2 rows only
> I_PLANT_PL I_STDY I_PRES_LINE I_PRES_TYP N_LINE_NAME
> L_PLAN I_LOAD_TYP I_AUTOM_TYP I_PRES_LINE_SIZE I_PRES_MECHNM_T
YP
> L_
> TCH_PL_LINE_TO_PLT_LINE Q_SHUT_HGT Q_NMBR_OF_STATNS I_UPD_TID D_UPD_LAST
> X_COMN
> More results followed but not pasted...
> Can anybody offer some suggestions?
> I've run out of ideas.
> I know the linked server is authenticating properly because when I put in
a
> bad password, the error message returned says, bad password or userid.
> One would assume that the problem is on the UDB server only. Yet I can
> access the data from the Windows 2003 server using the UDB client in a DOS
> prompt.
> Both techniques authenticate properly. Only the linked server gets access
> denied.
> The driver version on the Windows 2003 Server is DB2 v8.1.12.99
>sql

server: Error 17: SQL Server does not exist or access denied.

I have two sql server 2000 with mixed mode authetication. I stand at one server and setup a linked server to the other using the same user id and password.

However when I click to the Tables icon of linked server in Enterprise manager, there is an error message:

Error 17 SQL Server does not exist or access denied.

And It does not show any table.

I register remote sql server in Enterprise manager fine.

Could any one help me ?

Thanks.

Hi,

What remote login did u used for linked servers.

The remote login should exists on both the server.

Regards

Mohd Sufian

|||

S1 - local server
S2 - remote server

1. Ensure that the user account used has permissions to login to S2 directly and access desired table on S2.

2. Create linked server login mapping using sp_addlinkedsrvlogin on S1.
Try a simple select query on remote table using linked server name.

|||

both S1 and S2 have the same user id and password.

I' ve created linked server login on S1. However when I run a select query on remote table using Query Analyzer, I get the same error message ERROR 17

Do you have any more suggesstion ?

|||

Can you post the query that you executed on S1? Also, when you executed the query on S1, were you connected as the user that you set for the linked server connection?

Thanks
Laurentiu

|||

Both servers 10.0.10.237 and 10.0.10.111 run Sql SERVER 2000 with mixed mode authentication user id: sa, password: vbsp

Linked server QLNSParent from 10.0.10.237 links to 10.0.10.111 with login mapping: local login: sa, remote user: sa, remote password: vbsp

At 10.0.10.237, I login Query Analyzer using user id sa, password: vbsp. Then I run the query:

select * from QLNSParent.qlns.dbo.hoso

It alway returns the error message:

Server: Msg 17, Level 16, State 1, Line 1
SQL Server does not exist or access denied.

At 10.0.10.237, I run Enterprise Manager and I can register 10.0.10.111 fine. This means thats I can connnect to 10.0.10.111 OK but not using linked server.

Do you have any suggestion ?

Thanks.

|||

Can you post the output of the following commands on the 237 server?

exec sp_helpserver 'QLNSParent'
exec sp_helplinkedsrvlogin 'QLNSParent'

Thanks
Laurentiu

|||

the result of the first command:

name: QLNSParent, network_name: NULL, status: data access, use remote collation, ID: 1, connection_timeout: 0, query_timeout: 0

and the result of the second command:

Linked server: QLNSParent, local login: sa, Self mapping: 0, Remote login: sa

I think those results are ok but there is one problem of OS level security and I can't find exactly the cause.

can you help me ?

|||

Dear ,

Do u have a named instance on sqlserver2005.

from

sufian

|||

Both server run windows server 2003 enterprise edition.

In each server, we have a named instance of Sql server 2000.

|||

Dear,

Connect by give the servername with instance.

ex: svt_test(server name\sql_2000(instance name)

from

sufian

|||

I think the way you set up your linked server was incorrect. What was the sp_addlinkedserver command that you used?

Thanks
Laurentiu

|||

sp_addlinkedserver @.server = N'QLNSParent',
@.srvproduct = N' ',
@.provider = N'SQLOLEDB',
@.datasrc = N'10.0.10.111',
@.catalog = N'qlns',
@.provstr = N''
GO
sp_addlinkedsrvlogin @.rmtsrvname = 'QLNSParent',
@.useself = false,
@.locallogin = 'sa',
@.rmtuser = 'sa',
@.rmtpassword = 'vbsp'

GO

do you have any more suggesstion ?

|||

Is your SQLServer installation named QLNSParent? Can you connect to it using QA and the QLNSParent name?

If the name is ok, have you tried simply specifying:

sp_addlinkedserver N'QLNSParent', N'SQL Server'

Thanks
Laurentiu

server: Error 17: SQL Server does not exist or access denied.

I have two sql server 2000 with mixed mode authetication. I stand at one server and setup a linked server to the other using the same user id and password. However when I click to the Tables icon of linked server in Enterprise manager, there is an error message: Error 17 SQL Server does not exist or access denied. And It does not show any table. I register remote sql server in Enterprise manager fine. Could any one help me ? Thanks.

Hi,

What remote login did u used for linked servers.

The remote login should exists on both the server.

Regards

Mohd Sufian

|||

S1 - local server
S2 - remote server

1. Ensure that the user account used has permissions to login to S2 directly and access desired table on S2.

2. Create linked server login mapping using sp_addlinkedsrvlogin on S1.
Try a simple select query on remote table using linked server name.

|||

both S1 and S2 have the same user id and password.

I' ve created linked server login on S1. However when I run a select query on remote table using Query Analyzer, I get the same error message ERROR 17

Do you have any more suggesstion ?

|||

Can you post the query that you executed on S1? Also, when you executed the query on S1, were you connected as the user that you set for the linked server connection?

Thanks
Laurentiu

|||

Both servers 10.0.10.237 and 10.0.10.111 run Sql SERVER 2000 with mixed mode authentication user id: sa, password: vbsp

Linked server QLNSParent from 10.0.10.237 links to 10.0.10.111 with login mapping: local login: sa, remote user: sa, remote password: vbsp

At 10.0.10.237, I login Query Analyzer using user id sa, password: vbsp. Then I run the query:

select * from QLNSParent.qlns.dbo.hoso

It alway returns the error message:

Server: Msg 17, Level 16, State 1, Line 1
SQL Server does not exist or access denied.

At 10.0.10.237, I run Enterprise Manager and I can register 10.0.10.111 fine. This means thats I can connnect to 10.0.10.111 OK but not using linked server.

Do you have any suggestion ?

Thanks.

|||

Can you post the output of the following commands on the 237 server?

exec sp_helpserver 'QLNSParent'
exec sp_helplinkedsrvlogin 'QLNSParent'

Thanks
Laurentiu

|||

the result of the first command:

name: QLNSParent, network_name: NULL, status: data access, use remote collation, ID: 1, connection_timeout: 0, query_timeout: 0

and the result of the second command:

Linked server: QLNSParent, local login: sa, Self mapping: 0, Remote login: sa

I think those results are ok but there is one problem of OS level security and I can't find exactly the cause.

can you help me ?

|||

Dear ,

Do u have a named instance on sqlserver2005.

from

sufian

|||

Both server run windows server 2003 enterprise edition.

In each server, we have a named instance of Sql server 2000.

|||

Dear,

Connect by give the servername with instance.

ex: svt_test(server name\sql_2000(instance name)

from

sufian

|||

I think the way you set up your linked server was incorrect. What was the sp_addlinkedserver command that you used?

Thanks
Laurentiu

|||

sp_addlinkedserver @.server = N'QLNSParent',
@.srvproduct = N' ',
@.provider = N'SQLOLEDB',
@.datasrc = N'10.0.10.111',
@.catalog = N'qlns',
@.provstr = N''
GO
sp_addlinkedsrvlogin @.rmtsrvname = 'QLNSParent',
@.useself = false,
@.locallogin = 'sa',
@.rmtuser = 'sa',
@.rmtpassword = 'vbsp'

GO

do you have any more suggesstion ?

|||

Is your SQLServer installation named QLNSParent? Can you connect to it using QA and the QLNSParent name?

If the name is ok, have you tried simply specifying:

sp_addlinkedserver N'QLNSParent', N'SQL Server'

Thanks
Laurentiu

|||Hi, vhamu

I have the same problem from linking servers, did you find solution of this problem ?

Thanks

server VFPOLEDB gives Access Denied error

When I login to the windows server as administrator, the query runs. When I
login as anyone else (incl a user with as many admin rights as I can give
him), I get the following error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "VFPOLEDB" for linked server "A" reported an error.
Access denied.
Msg 7301, Level 16, State 2, Line 1
Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB
provider "VFPOLEDB" for linked server "A".
Any help would be appreciated."Ashley Brewerton" <AshleyBrewerton@.discussions.microsoft.com> wrote in
message news:800A97D6-2A65-4A2C-A746-56A4D4931C2A@.microsoft.com...
Hi Ashley,
Be sure you have the latest FoxPro and Visual FoxPro OLE DB data provider,
downloadable from msdn.microsoft.com/vfoxpro/downloads/updates .
Here's what works for me to set up the Linked Server in SQL 2005 using the
Linked Server dialog:
Providers list: VFPOLEDB shows as an entry
Linked Servers > New Linked Server ...
Linked Server: MyLinkedServer
Provider: Choose "Microsoft OLE DB Provider for Visual FoxPro
Product name: Visual FoxPro (I think this is optional)
Data source: "C:\Program Files\Microsoft Visual
FoxPro9\Samples\Northwind\Northwind.dbc" (Include quotes since there's
spaces in the string.)
Provider string: VFPOLEDB.1
Location: (blank)
Catalog: (blank)
Security page and Server Options page take defaults.
> When I login to the windows server as administrator, the query runs. When
> I
> login as anyone else (incl a user with as many admin rights as I can give
> him), I get the following error:
What permissions does the SQL Server local system account have?
I'm ok on my laptop with the SQL Server started with "local
system account" and my Windows login being MachineName\Cindy. When I tried
against my SQL Express instance which was started using the "NT
AUTHORITY\NetworkService" I had the same errors you report.
> Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB
> provider "VFPOLEDB" for linked server "A".
How are you executing your query? With the four-part naming or using
OpenQuery()?
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com|||Hi Cindy
Yes, I saw your previous post and have already tried the setup you
indicated. I run the query using the standard select syntax::
select * from AURORA...CMaster
"Cindy Winegarden" wrote:
> "Ashley Brewerton" <AshleyBrewerton@.discussions.microsoft.com> wrote in
> message news:800A97D6-2A65-4A2C-A746-56A4D4931C2A@.microsoft.com...
> Hi Ashley,
> Be sure you have the latest FoxPro and Visual FoxPro OLE DB data provider,
> downloadable from msdn.microsoft.com/vfoxpro/downloads/updates .
> Here's what works for me to set up the Linked Server in SQL 2005 using the
> Linked Server dialog:
> Providers list: VFPOLEDB shows as an entry
> Linked Servers > New Linked Server ...
> Linked Server: MyLinkedServer
> Provider: Choose "Microsoft OLE DB Provider for Visual FoxPro
> Product name: Visual FoxPro (I think this is optional)
> Data source: "C:\Program Files\Microsoft Visual
> FoxPro9\Samples\Northwind\Northwind.dbc" (Include quotes since there's
> spaces in the string.)
> Provider string: VFPOLEDB.1
> Location: (blank)
> Catalog: (blank)
> Security page and Server Options page take defaults.
> > When I login to the windows server as administrator, the query runs. When
> > I
> > login as anyone else (incl a user with as many admin rights as I can give
> > him), I get the following error:
> What permissions does the SQL Server local system account have?
> I'm ok on my laptop with the SQL Server started with "local
> system account" and my Windows login being MachineName\Cindy. When I tried
> against my SQL Express instance which was started using the "NT
> AUTHORITY\NetworkService" I had the same errors you report.
> > Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB
> > provider "VFPOLEDB" for linked server "A".
> How are you executing your query? With the four-part naming or using
> OpenQuery()?
>
> --
> Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
> cindy_winegarden@.msn.com www.cindywinegarden.com
>
>