4

How to add linked server establishing connection from remote SQL Server linking to local instance of SQL Server Express? By local I mean the PC I use with SQL Server Express instance and by remote I mean server I connect to with SSMS. Mostly on the web the opposite situation is described - from remote to local. I start with that documentation: https://learn.microsoft.com/en-us/sql/relational-databases/linked-servers/create-linked-servers-sql-server-database-engine

  1. Do I have to treat local server just as if it was remote? I mean do I have to find the IP address of local instance of linked server? If yes, then the question is how do I find necessary parameters of local server to make it remote? This query was a promising start for me https://stackoverflow.com/a/14695530/1903793 however I get null values for local_net_address, local_tcp_port, and client_net_address. So I am stuck.
  2. Please see image below. What to type in Linked server field? .\SQLExpress or some other phrase.
  3. What to choose SQL Server or Other data source?
  4. If Other data source, then what about provider?

enter image description here

Przemyslaw Remin
  • 6,276
  • 25
  • 113
  • 191
  • 1
    You write nothing about your network setup. Is the remote server in your your LAN or on the Internet? I think your question is more related to network/firewall/nat/routing than sql-server. – MatSnow Sep 25 '17 at 07:55
  • @MatSnow Remote is in the internet. Let's assume that we may adjust network setup of remote server whatever way which is needed. However I think that the focus should be on the setup of local PC how to make it visible to net, not on the remote server. – Przemyslaw Remin Sep 25 '17 at 08:02
  • `how to make it visible to net` - what do you mean with **net**? LAN, Internet? – MatSnow Sep 25 '17 at 08:04
  • @MatSnow internet. – Przemyslaw Remin Sep 25 '17 at 08:09
  • If your local PC already is [configured](https://technet.microsoft.com/en-us/library/hh231672%28v=sql.110%29.aspx) to be accessed via TCP/IP, then the question is just how to configure your firewall/router to make your server visible to the internet. This depends on your setup and especially your network-hardware. This can't be answered without any further information and is off topic for SO in my opinion. – MatSnow Sep 25 '17 at 08:16

4 Answers4

4

  1. Yes. It is local to you not to the other SQL Server where you want to create linked server. To the other server your local server is remote. Make sure you local server can accept network connections by using SQL Server Configuration Manager.

  2. If you go with SQL Server, you have to provide network name of your local server - the name you use in SSMS. Unless you use localhost or "." then you need to find it out, you can use ipconfig /all and look for Host Name. This will work assuming both your local server and the other server are on the same network. With Other server you have more options. In linked server you provide whatever you like and then specify connection information bellow. Here you can find more information about values

  3. You choose what your scenario allows. Choose Sql Server if you can

  4. Look at link in pt.2

EDIT:

DISCLAIMER I am not network engineer so I might be missing some crucial steps to actually make it work and this is just general direction.

If you are on other network than your other server you would have to open firewall on 1433 TCP port not only on your machine but also on the router and map router's port 1433 to your machine's port 1433. Then having your router public IP you should be able to link both servers. It kind of goes beyond scope of SO like @MatSnow mention in his comment.

4

If you have found "from remote to local" which is the opposite of what you want just do imagine your PC is the server and the server is the local PC and go one. Imagine you have 2 machines : machine1 and machine2. to add a link server on machine1 to machine2 : Just open the new link server page as above on the machine1. Linked server : (Machine2 IP address) (Selected) SQL server

enter image description here Go to the security page and select : Be made using this security context . and enter the username and password you have from SQL server instance on machine2. enter image description here

Hadi Ardebili
  • 99
  • 1
  • 7
1

I will not talk about your IP, because it is clear that you must have a public/reachable IP it is a pre-requisite

  1. You have to enable TCP/IP protocol in your SQL Server, this way your server accepts requests that does not come from your localhost. you can do it in the Sql Server Configuration Manager as you can see in the image below

enter image description here

  1. And once it is done, you have to open the port 1433 in your firewall so connections are not blocked as you can see here

  2. At this point you just have to add the linked server as usual, by IP/Instance

Victor Hugo Terceros
  • 2,969
  • 3
  • 18
  • 31
1

As others have pointed out. A lot of the answer and success of it depends on the ability for the 2 machines to communicate. so is DNS configured? Firewalls? Can you ping? Can you resolve machine names? Can you otherwise communicate from one server to the other? The difference of creating a linked server from your local machine and a remote server and vice versa is actually just reversing the information. your Local machine still needs to be a server with the appropriate configuration and permissions granted, but if it is then it is just another SQL server. A linked server only relys on having 2 SQL servers that are able to communicate.

Because I often work remote (VPN) and servers cannot always resolve my machines DNS name I use the following script to create a linked server on a remote server to connect to my local machine via IP address I am connected to the server via SSMS with:

DECLARE @AutoExecute BIT = 1
DECLARE @LinkedServerName VARCHAR(50) = 'LinkedServername' --if null or blank the linked server will just get the instance name
DECLARE @InstanceAppend VARCHAR(45) = '\InstanceName' --make emptry string if none
DECLARE @user VARCHAR(20) = 'username'
DECLARE @password VARCHAR(20) = 'password'

DECLARE @SQL VARCHAR(MAX)
DECLARE @IPAddress VARCHAR(255)
DECLARE @Instance VARCHAR(300)
DECLARE @SrvName VARCHAR(300)

SELECT @IPAddress = client_net_address
    ,@Instance = client_net_address + ISNULL(@InstanceAppend,'')
FROM
    sys.dm_exec_connections
WHERE Session_id = @@SPID;

SET @SrvName = CASE WHEN LEN(@LinkedServerName) > 0 THEN @LinkedServerName ELSE @Instance END

IF NOT EXISTS (SELECT * FROM sys.servers WHERE name = @SrvName)
BEGIN
    IF (LEN(@LinkedServerName) > 0)
    BEGIN
        SET @SQL = 'EXECUTE master.dbo.sp_addlinkedserver @server = N''' + @LinkedServerName + ''',@provider=''SQLOLEDB'', @srvproduct=N'''', @datasrc=N''' + @Instance + ''''
    END
    ELSE
    BEGIN
        SET @SQL = 'EXECUTE master.dbo.sp_addlinkedserver @server = N''' + @Instance + ''', @srvproduct=N''SQL Server'''
    END
    SET @SQL = @SQL + CHAR(13) +  'EXECUTE master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N''' + @SrvName + ''',@useself=N''False'',@rmtuser=''' + @user + ''', @rmtpassword=''' + @password + ''''
END
ELSE
BEGIN
    PRINT 'Linked Server Exists'
END

PRINT @SQL

IF (@AutoExecute = 1)
BEGIN
    BEGIN TRY
        EXECUTE (@SQL)
        PRINT 'Executed Successfully'
    END TRY
    BEGIN CATCH
        PRINT 'Failed To Execute'
        ;THROW
    END CATCH
END
Matt
  • 13,833
  • 2
  • 16
  • 28