Search code examples
sqlsql-serversql-server-2012sql-server-2016linked-server

How to create linked server on remote server to local PC

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

Solution

  • 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