Create linked server - 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

SQL Server 2008 Forums

High Availability (2008)

Create linked server

Author

Topic

younas02
Starting Member

28 Posts

Posted - 2013-07-18 : 03:58:15

i am using sql server 2008 r2. i want to distribute data to other pc using linked server. I googled a lot and did not get any proper idea how to create linked server for remote pc.Can anyone help me. Can anyone tell me step by step procedure to create it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-18 : 04:24:56

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=164892

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-18 : 08:17:04

If you have never done this, it just might be easier to use SQL Server Management Studio. The instructions on this page is very precise and easy to follow. The only trouble you might run into is when setting up security. When you get to that point and if you have questions, post back. http://msdn.microsoft.com/en-us/library/aa560998.aspx

younas02
Starting Member

28 Posts

Posted - 2013-07-19 : 02:53:33

these are the steps in that link

To create a linked server (SQL Server 2008)

1 Click Start, click All Programs, click Microsoft SQL Server 2008 R2, and then click SQL Server Management Studio.

2 In the Connect to Server dialog box, specify the name of the appropriate SQL Server, and then click Connect.

3 In SQL Server Management Studio, double-click Server Objects, right-click Linked Servers, and then click New Linked Server.

4 In the New Linked Server dialog box, on the General page, in the Linked server text box, enter the full network name of the SQL Server you want to link to.

5 Under Server type, click SQL Server.

6 In the left pane of the New Linked Server dialog, under Select a page, choose Security.

7 You will need to map a local server login to a remote server login. On the right side of the Security page, click the Add button.

8 Under Local Login, select a local login account to connect to the remote server. Check Impersonate if the local login also exists on the remote server. Alternatively, if the local login will be mapped to a remote SQL Server login you must supply the Remote User name and Remote Password for the remote server login.

9 In the left pane of the New Linked Server dialog, under Select a page, choose Server Options. Set the RPC and RPC Out parameters to True, and then click OK.

in step 4 i am giving the name of remote sqlserver instance that is KSS-PC\SQLEXPRESS

in step 5 i added a user and selected local server under local login option and under remote user option i write remote server name i.e., KSS-PC\SQLEXPRESS and because remote instance is using windows authentication i did not write any password.
then i followed step 9 and clicked on ok button and it gives error.

the linked server has been created but failed to test.
error locating server . time out error.
server not found or failed to connect to server.check if remote server is set to allow remote connections.

my other pc is on lan .i disabled the firewal of remote pc and set sql server to allow remote connections.
plz check i might be missing something like ip adres of remote sql server or something else?

[url][/url]

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-19 : 07:28:45

See if you are able to connect to the remote server using SSMS. If you are not able to, follow the instructions in this trouble-shooting guide: http://blogs.msdn.com/b/sql_protocols/archive/2006/09/30/sql-server-2005-remote-connectivity-issue-troubleshooting.aspx

I didn't quite follow your description of how you set up the credentials. The login name you specify has to be a valid login on the remote server.

One other thing to keep in mind is the so called multi-hop problem if you are connecting to the local server from a different box. There are some details here: http://blogs.msdn.com/b/sql_protocols/archive/2006/12/02/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections.aspx You can eliminate that possibility if you log on to the local box directly and then see if linked server connection works.

younas02
Starting Member

28 Posts

Posted - 2013-07-19 : 11:40:11

you mean i have to create remote login . and while creating linked server , under remote server option i have to write this remote login?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-19 : 12:27:35

You need SOME way of logging into the remote server. If you are using SSMS GUI to set this up, the security tab of the Linked Server set up dialog shows you the choices.
1. It lets you specify a mapping on the top part of the window. So, if I am user abcd on the local server, then connect as user xyz on the remote server etc.
2. You can simply specify that anyone who tries to connect should use their own security credentials. This works best if you are in the same windows domain and users are logging in using windows authentication.
3. All users will connect to the remote server using a specified SQL login and password.

From what you said it sounds like both computers are in the same domain, and you use Windows authentication. If that is the case, option 2 should work for you. Make sure that the user who is trying to connect to the remote server has a login on the remote server as well.

younas02
Starting Member

28 Posts

Posted - 2013-07-22 : 05:09:49

i created a login for remote server and used that but same error . can not find instance.
i am stuck here. dont know how to resolve this problem

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-22 : 05:18:35

quote:
Originally posted by younas02

i created a login for remote server and used that but same error . can not find instance.
i am stuck here. dont know how to resolve this problem

are the servers in the same domain?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

younas02
Starting Member

28 Posts

Posted - 2013-07-22 : 05:19:16

ccan you provide exapmle with screen shorts?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-22 : 05:53:28

quote:
Originally posted by younas02

ccan you provide exapmle with screen shorts?

example of what?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

younas02
Starting Member

28 Posts

Posted - 2013-07-22 : 06:35:38

i got the solution.
i was giving the linked server name server1 or something like this. but when i give it the name of remote sql server . linked server created successfully and working now.
thanks for the cooperation.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-22 : 10:42:11

quote:
Originally posted by younas02

i got the solution.
i was giving the linked server name server1 or something like this. but when i give it the name of remote sql server . linked server created successfully and working now.
thanks for the cooperation.

Of course! Using the wrong remote server name can do that to you EVERY time

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources