sp_addlinkedserver - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sp_addlinkedserver

Author

Topic

chapm4
Yak Posting Veteran

58 Posts

Posted - 2011-08-31 : 11:43:21

Trying to link from SQL 2008 running on Server 2008 R2 to SQL 2005 running on Server 2003 R2. I run sp_addlinkedserver using the following:

EXEC sp_addlinkedserver
@server = 'Link',
@srvproduct = '',
@provider = 'SQLNCLI',
@provstr = 'DRIVER={SQL Server};SERVER=ipofserver;UID=sa;PWD=passforsa;',
@catalog = 'Lab'

I create the ‘link’ fine. And also the ‘user login’… but when I try to view some data… I get:

OLE DB provider "SQLNCLI" for linked server "REMOTE_SERVER" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "REMOTE_SERVER" 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 87, Level 16, State 1, Line 0
SQL Network Interfaces: Connection string is not valid [87].

Please help

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-31 : 11:46:07

See if this one works:

EXEC sp_addlinkedserver
@server = 'Link',
@srvproduct = '',
@provider = 'SQLNCLI',
@provstr = 'Provider=SQLNCLI;Data Source=ipofserver;User ID=sa;Password=passforsa;Initial Catalog=Lab;',
@catalog = 'Lab'

Kristen
Test

22859 Posts

Posted - 2011-08-31 : 11:48:17

I have some suggestions on creating a linked server here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=164892

chapm4
Yak Posting Veteran

58 Posts

Posted - 2011-08-31 : 13:37:20

I tried robvolk's suggestion and once again command completes successfull, but when I try "SELECT * FROM Link.Lab.dbo.tblAnalysts" I get

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'CCMA-DATASRVR\Administrator'.
Msg 4060, Level 11, State 1, Line 1
Cannot open database "Lab" requested by the login. The login failed.
OLE DB provider "SQLNCLI" for linked server "Link" returned message "Invalid connection string attribute"

Thanks Kristen for the work that went into your file, I tried statements from there but get the same results.

Please help

Kristen
Test

22859 Posts

Posted - 2011-08-31 : 13:50:15

Try using a UserID that is the same on both servers, and has the same password

Assuming that both SQL's are set up to use SQL Logins ?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-31 : 13:55:00

Ahhh, try this:

EXEC sp_addlinkedserver
@server = 'Link',
@srvproduct = '',
@provider = 'SQLNCLI',
@provstr = 'Provider=SQLNCLI;Data Source=ipofserver;User ID=sa;Password=passforsa;Initial Catalog=Lab;',
@catalog = 'Lab',
@rmtuser=N'sa',
@rmtpassword='passforsa'

chapm4
Yak Posting Veteran

58 Posts

Posted - 2011-08-31 : 14:05:28

Kristen,
I am using a user 'link' on both servers that has the same password on both.

What do you mean "Assuming that both SQL's are set up to use SQL Logins "?

robvolk, doesn't like the @rmtuser
from that I get "Msg 8145, Level 16, State 1, Procedure sp_addlinkedserver, Line 0
@rmtuser is not a parameter for procedure sp_addlinkedserver."

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-08-31 : 14:50:03

I just dealt with this irritating linkedserver issue

This worked for me
1. I would not use sa for linked server, you just opened your door wide open if anyone is using the linked server

2. Create a "linkUser" login on server to be linked to. Make it an understandable user name unique


		IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'LinkLoginRemote')
		DROP LOGIN LinkLoginRemote

		CREATE LOGIN LinkLoginRemote WITH PASSWORD=N'LinkUserPwd', DEFAULT_DATABASE=[master], 
DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

3. Create a local LOGIN on the server where you will create the linked server


		IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'LinkLoginLocal')
		DROP LOGIN LinkLoginLocal

		CREATE LOGIN LinkLoginLocal WITH PASSWORD=N'LinkUserPwd', DEFAULT_DATABASE=[master], 
DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

4. Create link USER on the database that will use the remote linked server


	IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'LinkUserLocal')
	DROP USER LinkUserLocal
	GO

	CREATE USER LinkUserLocal FOR LOGIN LinkUserLocal WITH DEFAULT_SCHEMA=[dbo]
	GO

	EXEC [sp_addrolemember] @rolename = 'db_datareader', @membername = 'LinkUserLocal'

5. tie in local with remote


		--with server alias you do not have to expose the real name of your linked server
		--and when you deploy this to different environs the name stays generic
		EXEC master.dbo.sp_addlinkedserver @server = N'ServerAlias', @srvproduct=N'ServerAlias', 
@provider=N'SQLNCLI10', @datasrc=N'ipofserver'		
		

		 /* For security reasons the linked server remote logins password is changed with ######## */
		EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ServerAlias',@useself=N'False',
@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
		EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ServerAlias',@useself=N'False',
@locallogin=N'LinkUserLocal',@rmtuser=N'LinkUserRemote',@rmtpassword='LinkUserPwd'

		EXEC master.dbo.sp_serveroption @server=N'ServerAlias', @optname=N'collation compatible', @optvalue=N'false'
		GO

		EXEC master.dbo.sp_serveroption @server=N'ServerAlias', @optname=N'data access', @optvalue=N'true'
		GO

		EXEC master.dbo.sp_serveroption @server=N'ServerAlias', @optname=N'dist', @optvalue=N'false'
		GO

		EXEC master.dbo.sp_serveroption @server=N'ServerAlias', @optname=N'pub', @optvalue=N'false'
		GO

		EXEC master.dbo.sp_serveroption @server=N'ServerAlias', @optname=N'rpc', @optvalue=N'true'
		GO

		EXEC master.dbo.sp_serveroption @server=N'ServerAlias', @optname=N'rpc out', @optvalue=N'true'
		GO

		EXEC master.dbo.sp_serveroption @server=N'ServerAlias', @optname=N'sub', @optvalue=N'false'
		GO

		EXEC master.dbo.sp_serveroption @server=N'ServerAlias', @optname=N'connect timeout', @optvalue=N'0'
		GO

		EXEC master.dbo.sp_serveroption @server=N'ServerAlias', @optname=N'collation name', @optvalue=null
		GO

		EXEC master.dbo.sp_serveroption @server=N'ServerAlias', @optname=N'lazy schema validation', @optvalue=N'false'
		GO

		EXEC master.dbo.sp_serveroption @server=N'ServerAlias', @optname=N'query timeout', @optvalue=N'0'
		GO

		EXEC master.dbo.sp_serveroption @server=N'ServerAlias', @optname=N'use remote collation', @optvalue=N'true'
		GO

		EXEC master.dbo.sp_serveroption @server=N'ServerAlias', @optname=N'remote proc transaction promotion', @optvalue=N'true'
		GO

hope it helps rather than make is as clear as mud

If you don't have the passion to help people, you have no passion

chapm4
Yak Posting Veteran

58 Posts

Posted - 2011-08-31 : 17:38:50

Wow thanks! Sure would be nice if everything just worked as documented, but I guess that's job security. Thanks for all the help.

Will let you know if I run into further issues.

Kristen
Test

22859 Posts

Posted - 2011-09-01 : 02:40:14

So what have you done different to what we suggested?

yosiasz's script, unless I've missed something, is just creating a user on both remote and local servers with the same password, and then creating a linked server.

Unless, for some reason?, you have some weird options have been set as the defaults for the Server Alias.

chapm4
Yak Posting Veteran

58 Posts

Posted - 2011-09-02 : 11:31:18

Kristen, that is a good question. I did try both suggestions but they didn't work, so whats different? I read something in the beginning of my problems to set a different port number on the server that I am trying to link to....that could be an issue. Also I read about security and firewall issues. Don't think that is it, because tha addlinked server seems to work on the setup but I get the error when trying to query the data.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-09-02 : 12:08:50

what error do you get? which user are you using to run the query. what happens when you right click on linked server and choose Test Connection?

If you don't have the passion to help people, you have no passion

chapm4
Yak Posting Veteran

58 Posts

Posted - 2011-09-02 : 12:13:43

I created a user 'linkUser' on both boxes. When I run the query 'SELECT * FROM Link.Lab.dbo.tblAnalysts' I get:
OLE DB provider "SQLNCLI" for linked server "REMOTE_SERVER" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "REMOTE_SERVER" 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 87, Level 16, State 1, Line 0
SQL Network Interfaces: Connection string is not valid [87].

chapm4
Yak Posting Veteran

58 Posts

Posted - 2011-09-02 : 12:14:51

I know it looks like I was using sa user, but I actually created linkUser

Kristen
Test

22859 Posts

Posted - 2011-09-02 : 12:32:16

"I read something in the beginning of my problems to set a different port number on the server that I am trying to link to"

If you are not using the standard SQL port (on the remote server) there is an example in my script of how to "describe" that in the commands that you have to give.

"When I run the query 'SELECT * FROM Link.Lab.dbo.tblAnalysts' I get:
OLE DB provider "SQLNCLI" for linked server "REMOTE_SERVER" returned message "Login timeout expired"."

I think I would expect "Link" and "REMOTE_SERVER" to be the same (text). Might just be you have changed them in posting here though?

Kristen
Test

22859 Posts

Posted - 2011-09-02 : 12:34:02

You might want to try this, simple, example first. Hopefully your user has enough permissions to run this:

select top 10 [name] from [MyRemoteServer].master.dbo.sysdatabases

Substitute MyRemoteServer for the name of your linked server

chapm4
Yak Posting Veteran

58 Posts

Posted - 2011-09-07 : 13:11:27

OK....still issues. Kristen, I think I followed your script to a tee (thanks for all the help) but I still get the following error when doing the following:

select top 10 name from [CCMASHPSQL10].master.dbo.sysdatabases

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

I am new to this and don't know what to do. I set up user on both the remote and the server I am linking from (user is same on both and using same pass). Users are SQL users. Port on link side is 1433. I have set up firewall to allow remote 1433.

I would like to get this working but maybe my issue requires a different approach, see what you think. There is a Lab database on Sql Server 2008 machine, data is updated every 5 minutes. This is on regular 192 network. Another server in maintenance is on that network as well as a furnace network (22). For reporting purposes, the plant wants the Lab data to be pushed to a PLC on the 22 network. The server that is on both networks is running SQL Server 2005. I thought that the link server might be the best route to go....but maybe not. PLEASE HELP ME?!?!?!

chapm4
Yak Posting Veteran

58 Posts

Posted - 2011-09-07 : 13:24:36

One added bit of info....I can ping both the IP and Name of the remote server in SQL

Kristen
Test

22859 Posts

Posted - 2011-09-08 : 02:08:29

"Named Pipes Provider: Could not open a connection to SQL Server [1326]. "

I only ever use TCP/IP for my linked server connections. Set up as follows:

Start : Programs : SQL Server : Configuration tools : SQL Server Configuration Manager

Under SQL Native Client Configuration : Aliases

(there may be a 32 bit and a non-32bit - i.e. 64-bit - menu choice, pick the 64-bit one if you are using 64 bit machines)

Add a new alias as follows:

Alias name = the name of your remote server - e.g. CCMASHPSQL10
Port No = 1433
Protocol = TCP/IP (i.e. not Named Pipes)
Server = either a name that will resolve via DNS or the TCP/IP address. As you can PING both the name would be better (it can then be changed on DNS server if necessary, rather than having to reconfigure SQL). If the name doens;t work it would be worth trying the IP address.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources