I have 2 SQL Servers on one machine. Lets call it Server1 and Server2. I need to access Server1 from Server2 through the linked server. And I have managed to configure it but querying the linked server works only when I am logged with windows authentication method to Server2. When I will log with SQL login 'sa' into Server2, I am getting an error when executing a query:
The OLE DB provider "..." for linked server ".." reported an error. Authentication failed.
This is the security setting I am using in linked server configuration:
Please help.
Error Log from the remote server:
06/24/2022 09:23:33,Logon,Unknown,Login failed for user 'NT Service\MSSQL$SQLEXPRESS'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]
06/24/2022 09:23:33,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 5.
Error Log from remote SQL server made me think, so I have added new user NT Service\MSSQL$SQLEXPRESS to remote server. Now I am available to run queries against linked server.
I am puzzled though why linked server is using network service account despite having configured remote sql login sa in security tab. I would appreciate if someone could clarify this to me.