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.
CodePudding user response：
Does Server2 accept remote connections at all? (You can test connection using SSMS from Server1)
Also, What error is shown for the "Login Failed" on Server2? Should be written in the SQL Server Logs by default. The error number and State(8 in example) should help diagnose the issue. Example for a login failed over wrong password
CodePudding user response：
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.
Thanks for the suggestions.