How to create a linked server on a remote server with a local PC

Question

How to add a linked server establishing a connection from a remote SQL Server associated with a local instance of SQL Server Express? By local, I mean the computer that I use with the instance of SQL Server Express, and by remote, I mean the server with which I connect with SSMS. Basically, the opposite situation is described on the Internet - from remote to local. I start with this documentation: https://docs.microsoft.com/en-us/sql/relational-databases/linked-servers/create-linked-servers-sql-server-database-engine

• Do I need to handle the local server in the same way as if it was deleted? I mean, should I find the IP address of the local instance of the linked server? If yes, then the question arises, how to find the necessary parameters of the local server to make it remote? This request has become a promising start for me at https://stackoverflow.com/a/3129609/9 , however I get null values ​​for local_net_address, local_tcp_port and client_net_address. So I'm stuck.
• Please see the image below. What to enter in the Linked server field? .\SQLExpressor some other phrase.
• What to choose SQL Serveror Other data source?
• If another data source, what about the provider?

Answer 1

• . SQL Server, . . , SQL Server.

• SQL Server, - , SSMS. localhost "." , ipconfig /all . , , . . , , .

• , . Sql Server,

• pt.2

EDIT:

, , , .

, , TCP- 1433 , 1433 1433. , IP- . SO, @MatSnow .

Answer 2

" ", , , , , . , 2 : machine1 machine2. machine1 machine2: , 1. : (IP- Machine2) () SQL-

: . SQL Server 2.

Answer 3

IP-, , IP-/,

• TCP/IP SQL Server, , . Sql Server Configuration Manager,

2. , , 1433 , ,

3. , , IP/Instance

Answer 4

. . DNS? ? ? ? ? - . - , , SQL-. SQL-, .

Since I often work remotely (VPN), and servers cannot always resolve my computer name, I use the following script to create a linked server on a remote server to connect to the local machine by IP address. I am connected to the server via SSMS using:

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