728x90

OS: CentOS 6.6

DBMS: IBM DB2 9.1.0.5


위의 환경에서 데이터베이스를 생성하고 JDBC 클라이언트 및 로컬에서 특정 사용자로 접속시도하면 오류가 발생했습니다.

지원되지 않는 패스워드 알고리즘으로 패스워드를 생성한 것이 원인이었습니다.


db2 커맨드로 접속시 발생한 오류

$ db2 connect to dbname user ??? using ???

SQL30082N  Security processing failed with reason "15" ("PROCESSING FAILURE").

SQLSTATE=08001


자바(jdbc)를 통한 연결 오류

com.ibm.db2.jcc.am.SqlInvalidAuthorizationSpecException: [jcc][t4][2010][11246][4.16.53] Connection authorization failure occurred.  Reason: Local security service non-retryable error. ERRORCODE=-4214, SQLSTATE=28000


아래와 같이 authconfig를 사용하여 현재 설정된 패스워드 알고리즘 방식을 확인합니다.

CentOS 6.6에서는 기본이 sha512 알고리즘을 사용하는 모양입니다.


# authconfig --test | grep password

 shadow passwords are enabled

 password hashing algorithm is sha512 


아래와 같이 authconfig를 사용하여 현재 설정된 패스워드 알고리즘 방식을 확인합니다.

CentOS 6.6에서는 기본으로 sha512 알고리즘을 사용하는 모양입니다. DB2 9.1에서는 Crypt, MD5. SHA1 알고리즘만 지원한다고 합니다.

따라서 아래와 같이 설정하고 DB2 사용자 패스워드 재설정한 후에는 접속이 잘 이루어졌습니다.


# authconfig --passalgo=md5 --update


아래는 해당 내용에 대한 IBM문서입니다. 참고하시기 바랍니다.


SQL30082 RC=24 or RC=15 returned when connecting to database on Server when pwd_algorithm is Crypt, MD5, SHA1, SHA256, SHA512, Blowfish

Problem(Abstract)

A local or remote connection to a database using:

db2 connect to sample user <userid> using <password>

returns

SQL30082N Security processing failed with reason "24" ("USERNAME AND/OR 
PASSWORD INVALID"). SQLSTATE=08001 

The error in db2diag.log is 
2011-01-01-17.21.43.367890-300 I1011620A272 LEVEL: Warning 
PID : 123456 TID : 1 
FUNCTION: DB2 Common, Security, Users and Groups, secLogMessage, probe:
20 
DATA #1 : String, 66 bytes 
Password validation for user db2inst1 failed with rc = -2146500507 

OR 

SQL30082N Security processing failed with Reason Code 15
"Security processing at the server failed " 

2011-10-19-14.05.06.682505-300 I2778202A437 LEVEL: Warning 
PID : 10813678 TID : 6958 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : CMDB 
APPHDL : 0-117 
EDUID : 6958 EDUNAME: db2agent (CMDB) 0 
FUNCTION: DB2 UDB, bsu security, sqlexLogPluginMessage, probe:20 
DATA #1 : String with size, 67 bytes 
Password validation for user testuser failed with rc = -2029059891

Symptom

No connections can be made to the database when userid and password are provided. These connections fail with SQL30082N with Reason "24" or "15" returned.


Cause

DB2 Version 9.1 and Version 9.5 up to Fixpack 3 support the following encryption algorithms.

Crypt 
MD5 
SHA1

From DB2 Version 9.5 Fixpak 4 and versions beyond, the following algorithms are supported:

Crypt 
MD5 
SHA1 
SHA256 
SHA512 
Blowfish

For example, on AIX this is coded in the /etc/security/login.cfg file.

The error is received if you use the unsupported encryption algorithm and may show up in the db2diag log like this: 

2011-10-19-14.05.06.682321-300 I2777674A527 LEVEL: Severe
PID : 9633910 TID : 258 PROC : db2ckpwd 0
INSTANCE: db2inst1 NODE : 000
EDUID : 258 EDUNAME: db2sysc 0
FUNCTION: DB2 UDB, oper system services, sqloGetUserAttribByName, probe:50
MESSAGE : Unsupported password encryption algorithm in use! Configure the 
Operating System to use a supported algorithm.
DATA #1 : Hexdump, 4 bytes
0x07000000003F7F00 : 870F 00CD .... 

To check what encryption algorithm is being used the login.cfg file will contain something like:

usw:
shells = /bin/sh,/bin/bsh,/bin/csh,/bin/ksh,/bin/tsh,/bin/ksh93,/usr/bin
/sh,/usr/bin/bsh,/usr/bin/csh,/usr/bin/ksh,/usr/bin/tsh,/usr/bin/ksh93,/usr/sbin
/sliplogin,/usr/sbin/uucp/uucico,/usr/sbin/snappd,/bin/false
maxlogins = 32767
logintimeout = 60
pwd_algorithm = sha256
auth_type = STD_AUTH

OR the passwd.txt file will show something like this: 

root:
password = {sha256}06$SBysqAi4UQQ1nxC3$L55aKwmscvwxnKskkVrMk0HddbJyNkoE
v6HNXoLO.kH


Environment

ALL

Resolving the problem

Please use one of the supported encryption algorithms based on the version of DB2 that is being used.



http://m.blog.naver.com/june0313/50163164703

https://www-304.ibm.com/support/docview.wss?uid=swg21470246

https://www-304.ibm.com/support/docview.wss?uid=swg21416382

http://www-01.ibm.com/support/docview.wss?uid=swg21623221


728x90

'Db2 > Db2 troubleshooting' 카테고리의 다른 글

VALID 상태가 아닌 루틴 재컴파일하기  (0) 2019.07.22
오라클 호환모드에서 트리거 생성시 SQL0206N 오류발생  (0) 2016.11.28
SQL0727N 오류 관련 ( SQLCODE -204 , SQLSTATE 42703 )  (0) 2015.06.03
SQL0805N Package SYSLH203 was not found  (0) 2015.05.07
DB2 does not support PL/SQL array constructor syntax.  (0) 2014.11.03
728x90

SQL30082N Security processing failed with reason “26″


SQL Error [28000]: [jcc][t4][207][11243][3.59.81] Connection authorization failure occurred.  Reason: GSSAPI non-retryable error. ERRORCODE=-4214, SQLSTATE=28000


DB2 JDBC및 Native Client 연결 관련하여 다음과 같은 오류가 발생하여, 검색한 결과 유사한 내용을 찾을 수 있었습니다.

linux에서 설정하는 패스워드 알고리즘 관련 문제였습니다. 참고하시기 바랍니다.


If you are getting

SQL30082N Security processing failed with reason „15“ („PROCESSING FAILURE“)

when connecting to the db2 database using db2 CONNECT TO dbname USER username or

Connection authorization failure occurred. Reason: Local security service non-retryable error. ERRORCODE=-4214, SQLSTATE=28000

when connecting remotely (via JDBC driver) read on. I finally found what causes this error and a solution!

[ad]

Environment

Problem

Tho whole problem is in the format of the passwords in/etc/shadow. DB2 doesn't seem to like the passwords generated when changing password using the standard passwd command. In Fedora Core 10 the password is hashed using sha-512 and the entry for user username looks like:

username:$6$ef­WWOYRY$z5DNL1kLQ­U4AmPkFBqbQh6LOh­0Qjxq654dS9jE46iP­Np8Zces8I4bP8GLZ3G3RWLo/­6o.LYOV5neYSKxXbL­.M1:14375:0:99999:7:::

Solution

DB2 works fine with passwords hashed with standard crypt function. Password in the desired format can be obtained by callingopenssl passwd desiredPassword. The output of openssl can be passed to usermod --password The complete command to change user's password then look like:

usermod --password `openssl passwd desiredPassword`usename

Bigger image

I tried to hand-edit /etc/shadow and insert salted MD5 password, which can be obtained by openssl passwd -1 desiredPasswordand the connection was sucessfuly established. To sum it up, the whole problem in the end seems to be that DB2 doesn't like SHA-512 hashes in /etc/shadow. on the other hand, it works fine with hashes generated by crypt and MD5.

Changing default algorithm in Fedora Core

Fedora Core contains a nice tool authconfig. To change the default hashing algorithm to MD5 run

authconfig –passalgo md5 –update

All the passwords inserted in the password database will be stored in MD5 from now on. It will, of course, not change the hashes of the current passwords.

Ubuntu

Ubuntu ships with sha512 as default hash algorithm for passwords in /etc/shadow. The easiest way to change the default algorithm is to edit the file /etc/pam.d/common-password and change the line that reads

password [success=1 default=ignore] pam_unix.so obscure sha512

to

password [success=1 default=ignore] pam_unix.so obscure md5

The information about the algorith is also included in the file/etc/login.defs, which is used by chpasswd for example. Therefore change the line

ENCRYPT_METHOD SHA512

to

ENCRYPT_METHOD MD5



http://blog.stastnarodina.com/honza-en/spot/db2-sql30082n-security-processing-failed-with-reason-15/

http://www-01.ibm.com/support/docview.wss?uid=swg21623221

728x90

'Db2 > Db2 troubleshooting' 카테고리의 다른 글

Collecting data for index corruption and/or index or data inconsistency issues  (0) 2013.05.31
DB2 database corruption causing Tivoli Storage Manager crash  (0) 2013.05.28
"Detected client termination" in db2diag.log file.  (0) 2013.05.03
LIC1449N error applying DB2 Enterprise Server Edition license on a 32-bit Linux system  (0) 2013.04.04
DB생성시 SQL1005N 오류  (0) 2013.03.14

+ Recent posts

티스토리툴바