MSSQL: Create linked server using SSMS (SQL Server Management Studio)

Setting up MSSQL Linked Servers

From MSSQL documents:

Linked servers helps executing T-SQL commands against other sql server instance or oracle.
To execute stored proc, the linked server needs to be defined as an instance of SQL Server.

sp_addlinkedserver (TSQL statement used to adding linked server).

Permissions needed:
when using T-SQL : ALTER ANY LINKED SERVER or setupadmin role
when using SSMS: control server or sysadmin fixed role is needed.

First method: SSMS 

These steps are to be done in the linked server target machine (the destination to which we wish to connect to)

1) Let us create a test db for this excercise in both source and target server (in my case it is windows 2019 server & windows 10 laptop)

Use the method outlined in previous blog - refer here.

https://oracledbaplanner.blogspot.com/2023/05/mssql-create-database-using-sqlcmd.html

2) Ensure to make SQL Server listen on the IP reachable from the other server.

a. Verify from the ERRORLOG (startup message, which port and ip the SQL Server is listening on)
2023-05-07 18:15:12.71 spid25s     Server is listening on [ x.x.x.7 <ipv4> 1434] accept sockets 1.  <<< this is the ip we are going to reach from other server
2023-05-07 18:15:12.72 Server      Server is listening on [ ::1 <ipv6> 1434] accept sockets 1.
2023-05-07 18:15:12.72 Server      Server is listening on [ 127.0.0.1 <ipv4> 1434] accept sockets 1.

Note: To enable your private network ip to be listened to apart from default 127.0.0.1. you have to go to SQL Server configuration manager > SQL Server network configuration > TCP/IP Properties > Ensure you set "Enabled = Yes" and "Listen All = No" > then in IP Addresses, navigate to the x.x.x.x ip you wanted to be listened to, set "Enabled=yes"

This change needs SQL server restart.

See quick screenshots below..

The target protocal page:

The target IP Addresses page:

3) Add port exceptions in windows defender/antivirus based firewall.
4) Ensure you have a user account with necessary permission...

a. Ensure the SQL Server authentication mode is known and set as expected (mixed mode in my case & for this test case).

b. Create a user with the below commands

USE master
GO
CREATE LOGIN tsqltestdb_user WITH PASSWORD='tsqltestdb_user', DEFAULT_DATABASE=[tsqltestdb], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
use tsqltestdb
create user tsqltestdb_user
for login tsqltestdb_user
with default_schema=dbo;
go
ALTER ROLE db_datareader ADD MEMBER tsqltestdb_user;
go
       c. Ensure you verify that your able to connect to the user using sqlcmd as below...

C:\Users\lab>sqlcmd -U tsqltestdb_user -P tsqltestdb_user
1> select * from t1;
2> go
i      j
------ ----------
     1 V
     2 R
(2 rows affected)
1> SELECT @@servername
2> go
--------------------------------------------------------------------------------------------------------------------------------
DESKTOPXXXXXXXXXXXXX
(1 rows affected)
1> SELECT @@servicename
2> go
--------------------------------------------------------------------------------------------------------------------------------
MSSQLSERVER
(1 rows affected)
1>

5) Get the name of the target server and instance you wish to connect to using the below commands...

To get the server name:

SELECT @@servername
go
To get the instance/service name:
SELECT @@servicename
go
Example output:
1> SELECT @@servername
2> go
--------------------------------------------------------------------------------------------------------------------------------
DESKTOPXXXXXXXXXXXXX
(1 rows affected)
1> SELECT @@servicename
2> go
--------------------------------------------------------------------------------------------------------------------------------
MSSQLSERVER
(1 rows affected)
1>

Now on the source machine, where the linked server has to be created:

6) Try connecting to the linked server target using the below sqlcmd command from the linked server source; it should succeed. Ideally this meand we are able to connect fine.Note: SQL Browser isnt needed for this authentication to happen (meaning the SQL browser can stay stopped/disabled in linked server target)

C:\Users\testusr>sqlcmd -U tsqltestdb_user -P tsqltestdb_user -S tcp:192.168.0.7,1434
1> exit
C:\Users\testusr>

7) Let us connect to SSMS using windows authentication on the source machine (linked list creation server)

8) Let us navigate to the below option

Open [Object Explorer] > Expand [Server Objects] > Right-click [Linked Servers] > Select [New Linked Server]

An option like below screen opens up...

Please follow the screenshot to create the linked server and test as well.

To test the linked server:

Openquery method:

select * from openquery("x.x.x.7,1434",'select * from t1');

4 part query method:

select * from "x.x.x.7,1434".tsqltestdb.dbo.t1;

Both worked in our ssms based linked server setup. But the sqlcmd couldnt get past the '.' characters in ip.

Note: Microsoft asks us to enter the name of the server alone in case if its a default sql server instance (if it is named, use named instance).

Remember we arent using default port, hence we have to put a comma followed by the server name and give the nodefault port as well. In our case it was 1434.

8) Since I dont have name resolution, I ended up using IP. The IP isnt working very well when sqlcmd is used to query the linked server.With either openquery or 4 part query.

This closes the excercise.

Thank you!