RE: Linked Server -> Distributed query

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

From: John Bell (JohnBell_at_discussions.microsoft.com)
Date: 09/17/04 

Date: Fri, 17 Sep 2004 00:19:03 -0700

Hi

I am not a sybase user, but using a DSN would mean that you have ODBC on top
of OLEDB connection. I would therefore looks to use OLEDB directly. The
following site http://www.connectionstrings.com/ talks about possibly using a
.IDS file for ASE and other things that are needed.

John

"Boknaai" wrote:

> 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: Need to send a directive to linked database
    ... >I need to insert records into a Sybase database via a linked table. ... tested DSN ODBC connection to the Sybase database ... Without sending that authentication string, ...
    (comp.databases.ms-access)
  • Re: Need to send a directive to linked database
    ... Passed the authentication string with no problem. ... >>I need to insert records into a Sybase database via a linked table. ... >database server handle it" ...
    (comp.databases.ms-access)
  • RE: Linked Server -> Distributed query
    ... Thanks John, I tried using an IDS file, but users get the same error still ... > I am not a sybase user, but using a DSN would mean that you have ODBC on top ... >> username and password for the Sybase database. ...
    (microsoft.public.sqlserver.programming)
  • Need to send a directive to linked database
    ... I need to insert records into a Sybase database via a linked table. ... tested DSN ODBC connection to the Sybase database ... Without sending that authentication string, ...
    (comp.databases.ms-access)
  • Re: Access cant link more than one Sybase table, but Excel can ???
    ... I assume you're using the Linked Table Manager with a DSN? ... writing DAO code with an ODBC connection string. ... I'm trying to link tables in a Sybase DB to an Access DB. ... I can import the tables content without any single problem. ...
    (microsoft.public.access.externaldata)