Solved

Linked Server - MS SQL 2005 to MS SQL 2005

Posted on 2009-04-07
12
1,270 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
 
LVL 142

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 142

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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 142

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now