Solved

Linked Server - MS SQL 2005 to MS SQL 2005

Posted on 2009-04-07
12
1,283 Views
Last Modified: 2012-05-06
Greetings,

I am setting up a linked server across the internet.  Both servers are MS SQL 2005 Standard Servers.  I have sysadmin proveledges on both servers.

Using SQL Server Managemtn Studio I right clicked on "Linked Servers" and added the following:

GENERAL TAB
Linked Server: sqlserver.mydomain.com
Radio Button "SQL Server" is checked

SECURITY TAB
Local Login: my_local_account
Remote User: my_remote User_account which is a sysadmin
Remote Password: _remote_accounts_password

SERVER OPTIONS
RPC: True
RPC Out: True

Other SQL Configs:
Local & Remote connections are allowed for TCp and Named Pipes
SQL Server Browser is running

PORTS:
My firewwall has ports 1433 TCP and 1434 UDP open.

My web research shows I cannot use "sqlserver.mydomain.com" to reference the server, and leads me to setup an alias or to change the configs above to reflect the following:

GENERAL TAB
Linked Server: mylinkedserver
Radio Button "Other Datasource" is checked
  - Provider is set as "SQL Native Client"
  - Product name is set to "sql_server"
  - datasource is set to "sqlserver.mydomain.com"

SECURITY TAB
Local Login: my_local_account
Remote User: my_remote User_account which is a sysadmin
Remote Password: _remote_accounts_password

SERVER OPTIONS
RPC: True
RPC Out: True
Remote Collaboration: True


Both of these setups produce the following error (I replaced the linked server with the value xxx below):

OLE DB provider "SQLNCLI" for linked server "xxxx" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "xxxx" 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 1214, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [1214].

Any ideas?
0
Comment
Question by:styleteks
[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
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24093324
can you try creating by following code?

--add link server
 
exec sp_addlinkedserver
@server='LinkToOffice',
@srvproduct='OfficeDB',
@provider='SQLNCLI',
@provstr = 'DRIVER={SQL Server};SERVER=server name or ip;initial catalog=adventureworks;UID=sa;PWD=sa;'
GO
 
--authentication
EXEC sp_addlinkedsrvlogin 'LinkToOffice', 'false', NULL, 'sa', 'sa' --last two 'sa' is id/pass of remote sql server
 
--list all tables available in adventureworks of remote db.
 
exec sp_tables_ex 'LinkToOffice'

Open in new window

0
 

Author Comment

by:styleteks
ID: 24093916
Shah...

I created the linked server using the code below and received the following error:

OLE DB provider "SQLNCLI" for linked server "LinkToOffice" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "LinkToOffice" 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 1214, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [1214].
OLE DB provider "SQLNCLI" for linked server "LinkToOffice" returned message "Invalid connection string attribute".
exec sp_addlinkedserver
@server='LinkToOffice',
@srvproduct='OfficeDB',
@provider='SQLNCLI',
@provstr = 'DRIVER={SQL Server};SERVER=mysqlserver.mydomian.com;catalog=my_db_name;UID=My_username;PWD=my_password;'
GO
 
EXEC sp_addlinkedsrvlogin 'LinkToOffice', 'false', NULL, 'my_username, 'my_password'

Open in new window

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24093950
error you specified is caused due to several reason but most common, either your "mysqlserver.mydomian.com" is not contactable from the server you are trying to connect, or DB doesn't exist or credentials are wrong.

Can you try to connecting your mysqlserver.mydomian.com from SSMS with the credentials you provided?
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24094150
what about this:
exec sp_addlinkedserver @server='LinkToOffice' , @srvproduct='OfficeDB', @provider='SQLNCLI'
                      , @datasrc = 'mysqlserver.mydomian.com'
GO
 
EXEC sp_addlinkedsrvlogin 'LinkToOffice', 'false', NULL, 'my_username, 'my_password'

Open in new window

0
 

Author Comment

by:styleteks
ID: 24094236
SHAH...

I am connecting using SSMS without any issues to the server using the same credentials.

ANGELLLL...

Your suggestion produced the following error:
OLE DB provider "SQLNCLI" for linked server "LinkToOffice2" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "LinkToOffice2" 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 1214, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [1214].
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24094621
please try to deactivate the names pipes... just to test (requires restart of sql server)
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 24096105
Can you try with the server's IP address to connect..

Also, do a ping to the server.. from command prompt.
0
 

Author Comment

by:styleteks
ID: 24098801
ANGELLLL
Deactivating named pipes produces the same indentical error.

VALLI
IP Address produces the same error.  Ping results are successful with both qualified domain name and ip.

IS OPENING 1434 UDP REQUIRED FOR THIS OPERATION?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24099802
to confirm: did you restart sql server?
0
 

Author Comment

by:styleteks
ID: 24099832
ANGELLL
Yes, I did restart the server. Thanks!
0
 

Author Comment

by:styleteks
ID: 24102326
RiteshShah...

Any other recommendations based on what you see as being common resolutions for this problem?

Are there maybe special requirements/settings on the login account that i should consider checking?

Also, is UDP 1434 a requirement here?

Thanks in advance...
Chris
0
 

Accepted Solution

by:
styleteks earned 0 total points
ID: 24133867
The issue appears to have been caused by the ports for File and Printer sharing not being open.  After detailed research the best approach is not to open any of these ports and instead create a VPN through the firewall.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

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