Re: ODBC Linked Server & DSN

On Fri, 12 Jan 2007 14:11:04 -0800,
labsRcoolcommunitynospan@xxxxxxxxxxxxxxxxxxxxxxxxx wrote:

You will need to setup the driver and DSN on the SQL Server machine.

When you issue a query to a linked server, the client machine only knows
that it is talking to a SQL Server. The SQL Server then makes the request
to the remote data source. So the SQL Server has to have all the required
drivers, DSNs and privileges required to access the remote data source.

--
Darren Gosbell
SQL Server MVP

Hi,

Short version of question:
For linked servers to ODBC DSN data sources, does the DSN have to be
setup
on the same windows server as the sql server 2005 instance? Or is only
requirment that the DSN is setup from the workstaing that is luanching
SSMS?


Longer Background for question:
I'm attempting to setup a linked server in SQL Server 2005 to an ODBC
datasource which is Centura SQLbase database.

On my workstation, I have a DSN setup with the odbc drivers to connect to
the SQLbase db. The driver was "Centura SQLBase 3.60 32-bit Driver -NT &
Win95"
I can successfully import that tables from SQLBase using Access.

I'm using the SQL Server Managment Studio from my desktop to connect to
the
SQL 2005 Server on a Windows 2003 Server.

When testing the Linked server by using a query, I use the following
syntax
in a query in SSMS:
SELECT LastName, FirstName FROM LINK2SQLBASE.COMPANYDB.dbo.tblEmployee

I receive an Error:
*OLE DB provider "MSDASQL" for linked server... returned message
"[Microsoft][ODBC Driver Manager] Data source name not found and no
default
driver specified".
*Msg 7303, Level 16, State 1, Line 1
*Cannot initialize the data source object of OLE DB provider "MSDASQL"
for
linked server...

Again, i'm using the SSMS from my workstation that has the DSN setup and
works fine when importing from access. I do NOT have the DSN or drivers
on
the actual windwows 2003 Server where the SQL Sever 2005 instance is on.

Is that my promblem? Do I need the DSN on the actual windows 2003 server,
or
should I be albe to use the DSN from my workstation?

Thanks in advanced!
.

Relevant Pages

  • cannot access website
    ... Nerve.com--this started a few weeks ago; I do not get the error message on ... Microsoft OLE DB Provider for ODBC Drivers Error '80040e4d' ... Not defined as a valid user of a trusted SQL Server connection. ... The DSN used to connect to the Commerce database is misconfigured to use ...
    (microsoft.public.windows.inetexplorer.ie6.browser)
  • RE: Invalid authorization specification
    ... Does your application use a DSN to connect to SQL Server? ... DSN is setup correctly on the client machine. ... How to troubleshoot connectivity issues in SQL Server 2000 - ...
    (microsoft.public.sqlserver.connect)
  • Re: Programmatically create DSN usind SQL Server Authentication
    ... Not when you create the DSN programmatically. ... >Server using SQL Server Authentication. ... >is off-topic for my purpose because it only shows Windows authentication. ...
    (microsoft.public.sqlserver.odbc)
  • Re: Understanding SQL Connection
    ... production SQL server being hosted by a 3rd party on an external domain. ... "Rick Brandt" wrote: ... >> the linked table wizard and created a DSN. ... > credentials are automatically passed when the DSN is actually used later on. ...
    (microsoft.public.access.forms)
  • Re: Word 2003 MailMerge with SQL Server 2000 problem
    ... Okay, I switched the way I was connecting, and started using a DSN ... If I could either create a DSN-less connection string for Mail Merge, ... Personally I wouldn't use the 1=1 workaround on the grounds that the query ... SQL Server does but I once did this with Oracle to get around some problem ...
    (microsoft.public.word.mailmerge.fields)
Quantcast