SQL Server - set up Linked server in sql server 2005

Hi friends,

I am trying to create Linked server in SQL Server 2005, i want to create the linked server to my production server.

I gave following information to create a Linked Server:

Linked Server name : AR

and i select the option Server Type  as  SQL Server

In security option, Local login : local, make tick mark in impersonate, 
      Remote user as 'sa',
       remote login as 'xxx12'

enable the radio button, be made using this security context and enter 'sa' in remote login, and password 'xxx12' in with password text box.

In server options,

collation compatible    False
Data Access        True
Rpc            False
Rpc Out          True
Use Remote collation      True
Collation name
Connection timeout      0
QUERY TIMEOUT      0

Remote Server name is :  ProdServer, username is 'sa', and password is ' abc123'

when i execute the command, sp_helpserver  it  displays the information

i think i set up a server correctly, but still it gives following erroer,

OLE DB provider "SQLNCLI" for linked server "ar" returned message "Login timeout expired".

OLE DB provider "SQLNCLI" for linked server "ar" 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 53, Level 16, State 1, Line 0

Named Pipes Provider: Could not open a connection to SQL Server [53]. 

i dont know where i made mistake,

can any one help on this. it is very very required one. it will solve so many problems because i want to retrieve so many information from the production server.

Your production server is not set to enable remote connections.  You have to turn this on before attempting to create a linked server entry.

Open SQL Server Management Studio

Connect to your production server

Right click on the top node in the object explorer.  It will be the one with the green arrow indicating that sql server is running for that instance.

Select Properties to open the Properties Dialog.

In the properties dialog, you'll see several sections listed on the left.  Select Connections.

In the Connections area, check the "Allow remote connections to this server" option and click Ok.

You are all set. 

Not that SQL will respond to connection requests, you'll want to make sure the server is running behind firewalls so that you can't be attacked over the internet.

dear sir

thank you very much for your help. but  still i have the same problem. i already enable the check box of 'Allow remote connections to the server"

Is there any way to overcome this problem.


Thanks

It is most likely being blocked by a firewall "somewhere" in your network.  You'll need to work that out with your network admins.

Thank you, i will try