Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Cannot Create and Connect to a linked server

Posted on 2015-02-24
3
Medium Priority
?
188 Views
Last Modified: 2015-03-15
Hello Experts,

I have successfully created a linked server in SOL Server 2005 that connects to a remote server in SOL Server 2008 r2. It just stopped working for some reason. After spending a lot of time trying to get it to work I decided to start over from scratch  but I am getting the same thing.

Here are my steps:

A. Allow Remote connections on Remote SOL Server that I will be creating a linked Server for:

In Configuration Manager:

1.      Made Sure both Browser and SQLExpress Database engine services are running and that Start mode is Automatic.
2.      Enabled TCP/IP Protocol and restarted SQLExpress Database engine service

In SOL Server Management Studio:

1.      Verified “SQL Server and Windows Authentication Mode” mode is selected in Security properties  SQLExpress Instance.
2.      Restarted service

B.  Create a Linked Server (to Remote server) in SSMS on local server

1.      Click New Linked Server
2.      On “New Linked Server” General tab:
-      Linked Server: TESTLINKEDSERVER
-      Provider: Microsoft OLE DB Provider for SOL Server (this always resolves to Native Client)
-      Product Name: SOL SERVER
-      Data Source: 192.168.4.241\SQLEXPRESS (I can PING 192.168.4.241 from machine where I am setting up the Linked Server)
3.      On “New Linked Server” Security tab:
-      Under “Local Server to remote server login mappings”: Local Login: sa, Impersonate checked
-      Under “For a login not defined in the list above. Connections will”: Be made using this security context: Remote Login: sa, With Password:(verified correct sa password on remote server)

C. Test Connection to new Linked Server (TESTLINKEDSERVER) with following query:

SELECT
Cast(a.CustomerID as varchar(30)) as CustomerID
FROM
OPENQUERY([TESTLINKEDSERVER],'
SELECT
Cast(CustomerID as varchar(30)) as CustomerID
FROM
[Northwind].dbo.Customers'
) A


note. Query times out with following message:

OLE DB provider "SQLNCLI" for linked server "TESTLINKEDSERVER" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "TESTLINKEDSERVER" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
Msg 65535, Level 16, State 1, Line 0
SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].


I have checked the sa password and that I can reach the remote server over the network several times...

Can anyone see what I am doing wrong here?

Thanks!
0
Comment
Question by:Saxitalis
[X]
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 Comments
 
LVL 35

Accepted Solution

by:
David Todd earned 2000 total points
ID: 40631784
Hi,

You've said that you have checked that the SQL Express instance allows remote connections?

Can you from this server, in SSMS connect to the remote server and run that query?

Is there a firewall between these two servers? In that case you'll need to in configuration manager fix the port that SQL express is on and put a hole in the firewall corresponding to this port.

HTH
  David
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40639283
Saxitalis, you still have the issue or it's already solved?
0
 

Author Closing Comment

by:Saxitalis
ID: 40666960
Thanks
0

Featured Post

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

715 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