FreeTDS 로 MSSQL 2008 (구버전 SQL) 연결시 Error 0002 (severity 9): Adaptive Server connection failed

* 2024/11/08 일 기준으로 작성 되었습니다.

* 환경은  CentOS 7, MSSQL 2008 입니다.

 

FreeTDS가 OpenSSL을 사용하는 경우도 있고 GnuTLS를 사용하는 경우도 있어서 오류 확인 및 설정 확인 내용을 포함하여 작성 되었습니다.

 

tsql 명령으로 테스트시 Error 20002 (severity 9):  Adaptive Server connection failed 오류 발생

locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Error 20002 (severity 9):
        Adaptive Server connection failed
There was a problem connecting to the server

 

* CentOS 7 기준

1. 로그 확인을 위해 freetds.conf 에서 debug 활성화  ( freetds 설정 기본 경로 /etc/freetds.conf )

        dump file = /tmp/freetds.log
        debug flags = 0xffff

 

2. tsql 연결 테스트 

 

3. 로그 확인 ( 1번에 설정된  /tmp/freetds.log )

11:04:01.366482 24159 (tls.c:202):GNUTLS: level 10:
  READ: Got 5 bytes from 0x7ac590
11:04:01.366487 24159 (tls.c:202):GNUTLS: level 10:
  READ: read 5 bytes from 0x7ac590
11:04:01.366491 24159 (tls.c:202):GNUTLS: level 10:
  RB: Have 0 bytes into buffer. Adding 5 bytes.
11:04:01.366495 24159 (tls.c:202):GNUTLS: level 10:
  RB: Requested 5 bytes
11:04:01.366502 24159 (tls.c:202):GNUTLS: level 5:
  REC[0x7c4080]: SSL 3.1 Handshake packet received. Epoch 0, length: 606
11:04:01.366507 24159 (tls.c:202):GNUTLS: level 5:
  REC[0x7c4080]: Expected Packet Handshake(22)
11:04:01.366511 24159 (tls.c:202):GNUTLS: level 5:
  REC[0x7c4080]: Received Packet Handshake(22) with length: 606
11:04:01.366521 24159 (tls.c:117):in tds_pull_func_login
11:04:01.366526 24159 (tls.c:202):GNUTLS: level 10:
  READ: Got 606 bytes from 0x7ac590
11:04:01.366530 24159 (tls.c:202):GNUTLS: level 10:
  READ: read 606 bytes from 0x7ac590
11:04:01.366534 24159 (tls.c:202):GNUTLS: level 10:
  RB: Have 5 bytes into buffer. Adding 606 bytes.
11:04:01.366538 24159 (tls.c:202):GNUTLS: level 10:
  RB: Requested 611 bytes
11:04:01.366549 24159 (tls.c:202):GNUTLS: level 5:
  REC[0x7c4080]: Decrypted Packet[0] Handshake(22) with length: 606
11:04:01.366558 24159 (tls.c:202):GNUTLS: level 4:
  HSK[0x7c4080]: SERVER HELLO (2) was received. Length 77[602], frag offset 0, frag length: 77, sequence: 0
11:04:01.366565 24159 (tls.c:202):GNUTLS: level 4:
  HSK[0x7c4080]: Server's version: 3.1
11:04:01.366570 24159 (tls.c:202):GNUTLS: level 3:
  ASSERT: gnutls_handshake.c:1755
11:04:01.366574 24159 (tls.c:202):GNUTLS: level 3:
  ASSERT: gnutls_handshake.c:2255
11:04:01.366578 24159 (tls.c:202):GNUTLS: level 3:
  ASSERT: gnutls_handshake.c:1475
11:04:01.366582 24159 (tls.c:202):GNUTLS: level 3:
  ASSERT: gnutls_handshake.c:2739
11:04:01.366589 24159 (tls.c:202):GNUTLS: level 5:
  REC[0x7c4080]: Start of epoch cleanup
11:04:01.366593 24159 (tls.c:202):GNUTLS: level 5:
  REC[0x7c4080]: End of epoch cleanup
11:04:01.366600 24159 (tls.c:202):GNUTLS: level 5:
  REC[0x7c4080]: Epoch #0 freed
11:04:01.366604 24159 (tls.c:202):GNUTLS: level 5:
  REC[0x7c4080]: Epoch #1 freed
11:04:01.366637 24159 (tls.c:567):handshake failed: A packet with illegal or unsupported version was received.
11:04:01.366642 24159 (login.c:670):login packet rejected
11:04:01.366646 24159 (query.c:3804):tds_disconnect()
11:04:01.366662 24159 (util.c:179):Changed query state from IDLE to DEAD
11:04:01.366671 24159 (util.c:333):tdserror(0x7ac180, 0x7ac590, 20002, 0)
11:04:01.366698 24159 (util.c:363):tdserror: client library returned TDS_INT_CANCEL(2)
11:04:01.366703 24159 (util.c:386):tdserror: returning TDS_INT_CANCEL(2)
11:04:01.366709 24159 (mem.c:665):tds_free_all_results()

 

4. tsql 로 설정 정보 확인  ( tsql -C )

GnuTLS 사용

 

5. freetds.conf 에서 특정 db 쪽 설정 변경 ( freetds 설정 기본 경로 /etc/freetds.conf )

[global]
        # TDS protocol version
        tds version = 7.4
#       tds version = auto

        # Whether to write a TDSDUMP file for diagnostic purposes
        # (setting this to /tmp is insecure on a multi-user system)
;       dump file = /tmp/freetds.log
;       debug flags = 0xffff

        # Command and connection timeouts
;       timeout = 10
;       connect timeout = 10

        # To reduce data sent from server for BLOBs (like TEXT or
        # IMAGE) try setting 'text size' to a reasonable limit
;       text size = 64512

        # If you experience TLS handshake errors and are using openssl,
        # try adjusting the cipher list (don't surround in double or single quotes)
        # openssl ciphers = HIGH:!SSLv2:!aNULL:-DH


[php등에서 사용될 서버명]
        host = 아이피 혹은 도메인
        tds version = 7.0
#        tds version = 8.0
        tls protocol = TLSv1.1
#        client charset = UTF-8
        client charset = EUC-KR

 

 

일부 GnuTLS 에서 최소 버전 설정이 되지 않을대 환경 변수에 추가 (필수가 아닌 옵션)

export GNUTLS_DEBUG_LEVEL=3
export GNUTLS_FORCE_CLIENT_TLS_VERSION=TLS1.1

 

 

 

결론은? 아래 내용 추가..

        tds version = 7.0
        tls protocol = TLSv1.1

 

 

관련 내용

IIS Crypto 적용 시 주의? 유의 사항 구버전 MS SQL 연결 - 추천 Cipher Suites — 삽질은 내 운명

IIS Crypto 적용 시 주의? 유의 사항 구버전 MS SQL 연결 - 추천 Cipher Suites