Hi @Li, Treen ,
Anybody met with this similar problem before? Or do you have any idea that can help shoot the problem?
I have not met this similar problem. However, I have some ideas for it.
For SQL Server Authentication, a login with the exact same name and password must exist on the remote server. It means you need to create the same user name and password on your two servers if you use one sql server authentication.
So test on my side : create linked server in different server with SQL Server Authentication. (I restarted all the services on these two servers with same account doamin\Administrator)
--on server CLIENT\SQL2017DB
select * from test_dac.dbo.T_Test
use test_dac
go
--1.create new login
CREATE LOGIN test0924 WITH PASSWORD = 'Password01!';
--2.create a new user in database test
use test_dac
go
create user test0924 for login test0924
GRANT select ON DATABASE::test_dac TO test0924;
GO
--on server VERSIONTEST\SQLLOGIN2017
use master
go
--1.create new login
CREATE LOGIN test0924 WITH PASSWORD = 'Password01!';
--2.create a new user in database test
create user test0924 for login test0924
--create link server by ssms or t_sql
--3.check
select * from TESTLINK.test_dac.dbo.T_Test
More information: create-linked-servers-sql-server-database-engine
BR,
Mia
If the answer is helpful, please click "Accept Answer" and upvote it.