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

x
?
Solved

Linked Server - MS SQL 2005 to MS SQL 2005

Posted on 2009-04-07
12
Medium Priority
?
1,298 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

722 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