Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Linked Server - MS SQL 2005 to MS SQL 2005

Posted on 2009-04-07
12
Medium Priority
?
1,320 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
  • 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

963 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