Linked Server -> Distributed query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Boknaai (Boknaai_at_discussions.microsoft.com)
Date: 09/17/04 

Date: Thu, 16 Sep 2004 23:29:10 -0700

Hi!

I have written a stored procedure that uses a distributed query that looks
like the following:

SELECT Code, ShortName
FROM OPENQUERY(LINKED1, 'SELECT Code,ShortName FROM dbo.Client') Client

LINKED1 refers to a system DSN on the server that is link to a Sybase
database.

I can run this stored procedure and use it in my code to my hearts' content,
but as soon as anybody else (ie permited users on the database) tries to use
it, they get an error saying "System.Data.SqlClient.SqlException: Could not
create an instance of OLE DB provider 'Sybase.ASEOLEDBProvider'."

The Sybase database requires a username and password to logon to it, which
is different to the windows credentials that users use to get onto my SQL
server database. So in the Linked Server properties -> security tab, I have
checked the "Be Made With This Security Context" button and filled in a valid
username and password for the Sybase database. But this seems to make no
difference.

The fact that I can run this stored procedure means that the Sybase database
accepts distributed queries so it must be a security issue of sorts. I
installed the instance of SQL server so I obviously have full administratice
rights.

Any help/suggestions would be much appreciated

Relevant Pages

  • Re: DBD::Sybase and fork() in Perl 5.8.7
    ... other Sybase database in a child process. ... connection to the server must exist on the connection structure before ... Server MYSERVER, database ... Could someone help me figure out why the child process cannot connect? ...
    (perl.dbi.users)
  • RE: Accessing remote Sybase database directly from Pocket PC ...
    ... Check both parameters (server, port). ... "Arif" wrote: ... >>> Can anyone give me idea how to access remote Sybase database directly from ...
    (microsoft.public.sqlserver.ce)
  • DBD::Sybase and fork() in Perl 5.8.7
    ... other Sybase database in a child process. ... connection to the server must exist on the connection structure before ... Server MYSERVER, database ... Could someone help me figure out why the child process cannot connect? ...
    (perl.dbi.users)
  • Stored Procedures
    ... When running a stored procedure from a Sybase Database, ... It seems like there should be a way to declare an Oracle Transaction ...
    (comp.databases.oracle.server)
  • Re: Java, Sybase and CallableStatement
    ... I've got a problem in calling a stored procedure with a ... CallableStatement in JAVA. ... So I've got a stocked procedure on a Sybase database. ...
    (comp.lang.java.databases)