Improve company productivity with a Business Account.Sign Up

x
?
Solved

Linked Server - MS SQL 2005 to MS SQL 2005

Posted on 2009-04-07
12
Medium Priority
?
1,350 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
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

595 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