Linked Server in SQL 2008 on Windows Server 2008

Posted on 2012-03-26
Last Modified: 2012-06-27

I have Local MS SQL Server which I need to link to a remote MySql Server.
I have a Linked server set up on my local PC linking the Local SQL to the remote MySql server.

We have installed a Windows Server running the SQL Server and now I can not get the Linked Server to connect.

The Error I get is "Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "TESTWEB".
OLE DB provider "MSDASQL" for linked server "TESTWEB" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". (Microsoft SQL Server, Error: 7303)"

When I test the System DSN under Data Sources it tests fine.

I have set up these linked servers EXACTLY the same but the one on the local PC will connect and the one on the Windows Server refuses to.

Any Ideas??
Question by:p-plater
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 25

Expert Comment

ID: 37771661
You need TO change the PROPERTY OF the provider i.e. MSDASQL
Expand Providers -> Right Click MSDASQL -> Select Properties
Enable Nested queries
Enable Level zero only
Enable Allow inprocess
Enable Supports 'Like' operator

In SQL Server Surface Area Configuration:
Enable Local and Remote connections via TCP/IP and named pipes.Enable OPENROWSET and OPENDATASOURCE support.
LVL 35

Expert Comment

by:David Todd
ID: 37772632
Hi TempDBA,

How and where do you enable openrowset and opendatasource support?

LVL 25

Accepted Solution

TempDBA earned 250 total points
ID: 37775561
From Surface Area Configuration Manager:
-> Click Surface Area Configuration For Features
-> Check in the checkbox that asks for enabling openrowset and opendatasource support.

From T-sql:
sp_configure “show advanced options”,1

reconfigure with override

sp_configure “Ad Hoc Distributed Queries”,1

reconfigure with override
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

LVL 35

Assisted Solution

by:David Todd
David Todd earned 250 total points
ID: 37775832
Hi TempDBA,

Thanks for that.

But this unless I'm mistaken is a SQL 2008 question, and SQL 2008 doesn't have a Surface Area Configuration Manager.

Instead in SSMS, in Object Explor, right click on server and select Facets.

In Facet drop-down box, select Surface Area Configuration.

In Facet properties set AdHocRemoveQueriesEnaboled to true.

Is this right?


Author Comment

ID: 37895039
Look at tonight
LVL 25

Expert Comment

ID: 37895407
        Thanks, I forgot about the removal of surface area configuration in 2008. Thanks again for correcting me.

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
This tutorial will give a an overview on how to deploy remote agents in Backup Exec 2012 to new servers. Click on the Backup Exec button in the upper left corner. From here, are global settings for the application such as connecting to a remote Back…
This tutorial will walk an individual through the steps necessary to install and configure the Windows Server Backup Utility. Directly connect an external storage device such as a USB drive, or CD\DVD burner: If the device is a USB drive, ensure i…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question