SQL Server DB를 사용할 때 JTA 설정 트러블 슈팅

wontaekoh·2024년 6월 22일
db트러블 슈팅
0

Kakfa 도입기

목록 보기
4/6
post-thumbnail

SpringBoot 프로젝트에서 MultiDataSource를 설정하면서 JTA를 함께 사용하여 여러 DB에 대한 API 요청을 하나의 트랜잭션으로 처리하는 기능을 개발했습니다. 개발 과정에서 H2 데이터베이스로 예제 코드를 작성했지만, 실제 서비스에서는 MSSQL을 사용해야 했습니다. 이 과정에서 발생한 에러와 해결 방법을 정리하여 공유합니다.

📌 발생한 에러1

javax.transaction.xa.XAException: com.microsoft.sqlserver.jdbc.SQLServerException: XA 컨트롤 연결을 만들지 못했습니다. 오류: "저장 프로시저 'master..xp_sqljdbc_xa_init_ex'을(를) 찾을 수 없습니다."

microsoft 공식문서를 확인해보니 JDBC Driver for SQL Server (zip)을 다운 받아서 안에 있는 xa_install.sql 스크립트를 실행해야 했습니다.

  • xa_install.sql 스크립트 내용
-- This script installs the extended stored procedures that implement distributed transaction and XA support
-- for the Microsoft JDBC Driver for SQL Server.
-- Works only with SQL 2008 and above.
--
-- The JDBC XA distributed transaction components are included in the SQL Server engine in SQL Server 2017
-- starting with cumulative update 16 and in SQL Server 2019, and can be enabled or disabled with a system
-- stored procedure. The sqjdbc_xa.dll from the driver is not required and it is recommended to enable the
-- server components instead for these server versions.

-- Notes for SQL Administrators:

-- #1. Prior to running this script you must copy the extended stored procedure dll SQLJDBC_XA.dll 
--     to the target SQL Server's Binn folder.

-- #2. Permissions to the distributed transaction support procedures for the Microsoft JDBC Driver 8.4
--     for SQL Server are granted through the SQL Server role [SqlJDBCXAUser].  To maintain a secure default 
--     configuration, no user is granted access to this role by default.

-- Drop and re-create the extended stored procedure definitions in master.

use master
go

-- Drop any existing procedure definitions.
if exists (select * from sys.objects where object_id = object_id('xp_sqljdbc_xa_init') and OBJECTPROPERTY(object_id, N'IsExtendedProc') = 1) exec sp_dropextendedproc 'xp_sqljdbc_xa_init' 
if exists (select * from sys.objects where object_id = object_id('xp_sqljdbc_xa_start') and OBJECTPROPERTY(object_id, N'IsExtendedProc') = 1) exec sp_dropextendedproc 'xp_sqljdbc_xa_start'
if exists (select * from sys.objects where object_id = object_id('xp_sqljdbc_xa_end') and OBJECTPROPERTY(object_id, N'IsExtendedProc') = 1) exec sp_dropextendedproc 'xp_sqljdbc_xa_end'
if exists (select * from sys.objects where object_id = object_id('xp_sqljdbc_xa_prepare') and OBJECTPROPERTY(object_id, N'IsExtendedProc') = 1) exec sp_dropextendedproc 'xp_sqljdbc_xa_prepare'
if exists (select * from sys.objects where object_id = object_id('xp_sqljdbc_xa_commit') and OBJECTPROPERTY(object_id, N'IsExtendedProc') = 1) exec sp_dropextendedproc 'xp_sqljdbc_xa_commit'
if exists (select * from sys.objects where object_id = object_id('xp_sqljdbc_xa_rollback') and OBJECTPROPERTY(object_id, N'IsExtendedProc') = 1) exec sp_dropextendedproc 'xp_sqljdbc_xa_rollback'
if exists (select * from sys.objects where object_id = object_id('xp_sqljdbc_xa_forget') and OBJECTPROPERTY(object_id, N'IsExtendedProc') = 1) exec sp_dropextendedproc 'xp_sqljdbc_xa_forget'
if exists (select * from sys.objects where object_id = object_id('xp_sqljdbc_xa_recover') and OBJECTPROPERTY(object_id, N'IsExtendedProc') = 1) exec sp_dropextendedproc 'xp_sqljdbc_xa_recover'
if exists (select * from sys.objects where object_id = object_id('xp_sqljdbc_xa_rollback_ex') and OBJECTPROPERTY(object_id, N'IsExtendedProc') = 1) exec sp_dropextendedproc 'xp_sqljdbc_xa_rollback_ex'
if exists (select * from sys.objects where object_id = object_id('xp_sqljdbc_xa_forget_ex') and OBJECTPROPERTY(object_id, N'IsExtendedProc') = 1) exec sp_dropextendedproc 'xp_sqljdbc_xa_forget_ex'
if exists (select * from sys.objects where object_id = object_id('xp_sqljdbc_xa_prepare_ex') and OBJECTPROPERTY(object_id, N'IsExtendedProc') = 1) exec sp_dropextendedproc 'xp_sqljdbc_xa_prepare_ex'
if exists (select * from sys.objects where object_id = object_id('xp_sqljdbc_xa_init_ex') and OBJECTPROPERTY(object_id, N'IsExtendedProc') = 1) exec sp_dropextendedproc 'xp_sqljdbc_xa_init_ex'
go

-- Install the procedures.
exec sp_addextendedproc 'xp_sqljdbc_xa_init', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_start', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_end', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_prepare', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_commit', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_rollback', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_forget', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_recover', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_rollback_ex', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_forget_ex', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_prepare_ex', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_init_ex', 'SQLJDBC_XA.dll'
go

-- Create the [SqlJDBCXAUser] role in master database.
-- The SQL administrator can later add users to this role to allow users to participate 
-- in Microsoft JDBC Driver 8.4 for SQL Server distributed transactions.
if exists (select * from sys.schemas where name = 'SqlJDBCXAUser' ) 
drop schema [SqlJDBCXAUser];

if exists (select * from sys.database_principals where name = 'SqlJDBCXAUser' and type='R') 
drop role [SqlJDBCXAUser];

create role [SqlJDBCXAUser]
go


-- Grant privileges to [SqlJDBCXAUser] role to the extended stored procedures.
grant execute on xp_sqljdbc_xa_init to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_start to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_end to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_prepare to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_commit to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_rollback to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_recover to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_forget to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_rollback_ex to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_forget_ex to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_prepare_ex to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_init_ex to [SqlJDBCXAUser]
go

-- Add users to the [SqlJDBCXAUser] role as needed.

-- Example for adding a SQL authentication user to the SqlJDBCXAUser role.
-- exec sp_addrolemember [SqlJDBCXAUser], 'MySQLUser'

-- Example for adding a windows domain user to the SqlJDBCXAUser role.
-- exec sp_addrolemember [SqlJDBCXAUser], 'MyDomain\MyWindowsUser'

print ''
print 'SQLJDBC XA DLL installation script complete.'
print 'Check for any error messages generated above.'

📌 발생한 에러2

javax.transaction.xa.XAException: com.microsoft.sqlserver.jdbc.SQLServerException: XA 컨트롤 연결을 만들지 못했습니다. 오류: "DLL SQLJDBC_XA.dll 또는 이 DLL이 참조하는 DLL 중 하나를 로드할 수 없습니다. 원인: 126(지정된 모듈을 찾을 수 없습니다.)."

확인해보니 다행이 똑같은 에러를 정리한 블로그를 찾을 수 있었습니다.

블로그 내용을 정리하면 크게 아래 3가지를 해야했습니다. 2번작업은 위에서 해결한 과정으로 1,3번 과정만 설명드리겠습니다.

  1. MS DTC을 사용으로 설정
  2. JDBC 분산 트랜잭션 컴포넌트를 구성(xa_install.sql)
  3. sqljdbc_auth.dll 파일 복사

1. MS DTC을 사용으로 설정

먼저 제어판 > 시스템 및 보안 > Windows Tools > 구성요소 서비스 경로로 들어갑니다.

그리고 아래 이미지에 보이는 것과 같인 경로로 들어가서 로컬 DTC 속성에서 XA 트랜잭션 사용을 선택 후 확인을 클릭하면 됩니다. 이 때 MS DTC를 재시작 할껀지 확인 팝업이 뜨는데 확인을 눌러주면 설정은 완료됩니다.

2. sqljdbc_auth.ddl 파일 복사

발생한 에러1에서 받은 폴더에 아래 이미지의 경로로 들어가면 mssql-jdbc_auth-버전.ddl 파일이 있는데 이를 SQL Server 컴퓨터의 Binn 디렉토리에 복사하면 됩니다.

기본 경로에 설치를 했으면 경로를 아래와 같을 것 입니다.

C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn

만약 해당 경로를 찾을 수 없다면 SQL Manager에서 원하는 DB의 속성을 열어서 경로를 확인할 수 있습니다.

📌 발생한 에러3

위 과정까지 완료하고 실행을하면 실행!될줄 알았으나 어림도 없었습니다... 아래와 같은 에러가 또 발생하였습니다.

com.microsoft.sqlserver.jdbc.SQLServerException: "encrypt" 속성이 "true"(으)로 설정되고 "trustServerCertificate" 속성이 "false"(으)로 설정되었지만 드라이버가 SSL(Secure Sockets Layer) 암호화를 사용하여 SQL Server에 대한 보안 연결을 설정할 수 없습니다. 오류: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target. ClientConnectionId:e7f6bf8b-9ebc-49af-a895-19cdc2a8c7c1

application.yml은 아래와같이 설정하였습니다.

# 생략...
      xa-properties:
        URL: jdbc:sqlserver://localhost:1433;databaseName=test_wms;trustServerCertificate=true;characterEncoding=UTF-8;
        user: sa
        password: 안알랴줌
# 생략...

보시면 trustServerCertificate=true 라고 설정을 했는데 왜안될까?했습니다. 분명 MultiDatasource 설정이 아니라 그냥 단일 DB에서 연결한 url 정보를 그래로 사용한건데....생각하는 순간 혹시 jta를 사용한 설정에서는 trustServerCertificate 속성을 따로 명시해줘야하는건가?? 싶어서 아래와 같이 명시해주니 성공적으로 프로젝트가 실행되었습니다!

수정된 applicaiton.yml은 아래와같습니다.

# 생략...
      xa-properties:
        URL: jdbc:sqlserver://localhost:1433;databaseName=test_wms;trustServerCertificate=true;characterEncoding=UTF-8;
        user: sa
        password: 안알랴줌
        trustServerCertificate: true # 추가
# 생략...

✨ 마무리

JTA를 사용할 때 SQL Server 에 연결하기 위한 설정 방법에 대해 알 수 있었고, 에러가 발생했을 때 여러 공식문서와 블로그를 통해 필요한 부분을 찾아서 해결하는 스킬을 기를 수 있었습니다.
디버깅을 하면서 이를 해결했을 때 기분은 정말...너무 좋은 것 같습니다!ㅎ

profile
wontaekoh
주니어 백엔드 개발자, 오원택입니다!
이전 포스트

Multi Datasource 설정과 JTA를 활용한 분산 트랜잭션 관리

다음 포스트

SQL Server Agent running status query must return exactly one value(Debezium Connect 생성시 발생한 에러)

0개의 댓글

관련 채용 정보