Cannot setup a linked server

Question

Hi,

I am trying to set up a linked server from a standalone SQL server to a 2 node Clustered SQL instance using a SQL login.

It works fine from the SQL Cluster to the standalone server, but I get a 'login failed for user X' when creating it on the standalone to the clustered instance. Even if I grant the login sysadmin on the clustered instance it still fails.

Any ideas what im doing wrong?

Answer 1

Did you look in the SQL Server error log on the SQL Cluster? There should be more details explaining why the login failed.

Answer 2

Hi @mark hayward ,

Please firstly create same user and password in both servers(Clustered nodes and standalone server),and then use this user and password create linked server.

--on server 1  
  
use dbname  
go  
  
--1.create new login  
CREATE LOGIN test1019 WITH PASSWORD = 'Password01!';  
  
--2.create a new user in database test  
use dbname  
go  
  
create user test1019 for login test1019  
  
GRANT select ON DATABASE::dbname TO test1019;  
GO  
  
--on server 2  
  
use master  
go  
  
--1.create new login  
CREATE LOGIN test1019 WITH PASSWORD = 'Password01!';  
  
--2.create a new user in database test  
create user test1019 for login test1019  

--3.how-to-create-and-configure-a-linked-server-in-sql-server-management-studio

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.