[MSSQL] SQL Server Linked Server(연결된 서버) 추가와 로그인 매핑정보 / 삭제

SQL Server (MSSQL)의 Linked Server(연결된 서버)란?

: 데이터베이스 엔진에서 다른 SQL Server 인스턴스 또는 Oracle과 같은 이기종 데이터베이스 제품의 테이블을 포함하는 Transact-SQL 문을 실행할 수 있도록 설정하는 것이다.

Linked Server 를 Configuration 하는 데에 있어, 서버의 기능 및 필수 Parameter는 MS 공식 문서에서 제공하는 일반적인 예시와는 크게 다를 수 있다고 공식 문서에서도 명시되어 있으니, 다양한 케이스에 따라 많은 학습이 필요할 것으로 보인다.

필자의 경우에는, SSMS GUI로는 원인 파악이 제대로 되지 않는(?) 다양한 에러(대부분 복잡한 권한 문제가 아닐까 싶다…)가 나는 것 같아, 아래와 같이 쿼리로 확실하게 수행하는 법을 기록해 두고자 한다.

 

0. 현재 시스템의 Linked Server 확인 쿼리

/* 현재 시스템에 세팅되어 있는 Linked-Server 확인 쿼리 */
SEELCT * 
FROM master.dbo.sysservers
WHERE srvname = '[연결된 서버별칭]'

/* 현재 시스템에 세팅되어 있는 Linked-Server의 로그인 매핑 정보 확인 쿼리 */
SELECT *
FROM master.sys.linked_logins
WHERE remote_name = '[Remote DB 로그인 계정]'

 

 

1. Linked Server 추가

EXEC sp_addlinkedserver
	@server = N'[연결된 서버 별칭]'
	, @srvproduct = N'[OLE DB Source DB의 제품 이름]'
	, @provider = N'[OLE DB Provider의 PROGID(프로그래밍 식별자)]'
	, @datasrc = N'{IP},{PORT}'          -- PORT 생략 시 OLE DB의 Default 포트로 연결

/* 예시 */
EXEC sp_addlinkedserver
	@server = N'REMOTE_DB_TEST'
	, @srvproduct = N''                    -- 공식문서에 기본값이 Null이라고 되어 있지만, 이렇게 빈 문자열로 명시해주어야 에러 없이 생성이 된다.
	, @provider = N'SQLOLEDB'
	, @datasrc = N'10.123.123.123,55555'

 

2. 생성된 Linked Server에 대한 로그인 계정 매핑 정보 추가

로그인 계정 매핑 정보를 반드시 추가할 필요는 없다.

만약 추가하지 않는다면, ‘NT AUTHORITY\ANONYMOUS LOGON’ 계정으로 연결을 시도하는데, 일반적으로 접근 권한이 제한되어 있다. 따라서 연결이 안될 것이다. 보안상 연결이 안 되는 것이 맞다.

필자의 경우, SSMS의 GUI로 설정하는 건 parameter가 내가 의도하는 대로 설정이 되지 않는지, 아래와 같은 에러가 뜨면서 연결 테스트 진행이 되지 않아, SP(Stored Procedure)로 수행하였다.

Access to the remote server is denied because no login-mapping exists 에러

 

따라서 아래와 같이 Linked-Server에 대한 로그인 계정 매핑 정보도 별도로 추가해 주어야 한다.

EXEC sp_addlinkedsrvlogin
	@rmtsrvname = N'[연결된 서버 별칭]'
	, @useself = N'FALSE'              -- 로컬 로그인 계정으로 원격 로그인 계정에 그대로 사용할지 말지 정하는 옵션
	, @locallogin = N'[Local DB의 올바른 권한을 가진 계정]'
	, @rmtuser = N'[Remote DB의 올바른 권한을 가진 계정]'
	, @rmtpassword = N'[Remote DB 계정의 Password]'

/* 예시 */
EXEC sp_addlinkedsrvlogin
	@rmtsrvname = N'REMOTE_DB_TEST'
	, @useself = N'FALSE'
	, @locallogin = N'LOCALDBADM'
	, @rmtuser = N'REMOTEDBADM'
	, @rmtpassword = N'My_Password123~!'

• useself 의 값을 TRUE로 할 경우, 현재 로컬 서버의 로그인 계정으로 로그인을 하게 되는데,
  만약 원격 DB 서버에 동일한 이름의 계정이 없거나 필요한 권한을 가지고 있지 않으면, 로그인이 되지 않아 Linked-Server를 사용할 수 없으니, 이 점 유의하자!

 

3. 생성된 Linked Server 연결 테스트

SSMS의 DB 인스턴스 - [서버 개체] - [연결된 서버] - 해당 연결된 서버 마우스 우클릭 - '연결 테스트'

• 유의할 점 : Linked-Server를 만든 계정은 System Admin 권한을 가졌을 것이고, 이는 로그인 계정 매핑에 사용된 계정과는 다를 가능성이 높다. (SA 권한을 거기에 박는건 보안상 너무 위험하니까…) 따라서, 위와 같은 SSMS GUI 상의 [연결 테스트]는 로그인 계정으로 들어간 계정으로 다시 DB에 로그인하여 테스트 해야 한다. 이는 Linked-Server를 이용하는 프로시저와 Agent Job을 테스트 할 때에도 마찬가지이다.

---

Linked-Server 삭제 방법

Linked-Server 삭제를 할 때에는 로그인 매핑 정보를 먼저 삭제한 후, Linked-Server 개체를 삭제해야 한다.

SP로 수행하려면 다음 쿼리를 순서대로 수행하면 된다.

/* Linked-Server 로그인 매핑 정보 삭제 SP */
EXEC sp_drolinkedsrvlogin
    @rmtsrvname = N'[연결된 서버 별칭]'
    , @locallogin = N'[로그인 매핑 정보의 Local 로그인 계정]'

/* Linked-Server 개체 삭제 SP */
EXEC sp_dropserver
	@server = N'[연결된 서버 별칭]'

/* 예시 */
EXEC sp_drolinkedsrvlogin
	@rmtsrvname = N'REMOTE_DB_TEST'
	, @locallogin = N'LOCALDBADM'

EXEC sp_dropserver
	@server = N'REMOTE_DB_TEST'

 

---

참고

https://learn.microsoft.com/ko-kr/sql/relational-databases/system-stored-procedures/sp-addlinkedserver-transact-sql?view=sql-server-ver16

sp_addlinkedserver(Transact-SQL) - SQL Server

https://learn.microsoft.com/ko-kr/sql/relational-databases/system-stored-procedures/sp-addlinkedsrvlogin-transact-sql?view=sql-server-ver16

sp_addlinkedsrvlogin(Transact-SQL) - SQL Server

https://blog.naver.com/diceworld/220311810558

MSSQL 연결된서버 사용하기 (linked server)