Hey,
Can anyone help me in this regard. Please do let me know if im missing any configuration here. Im trying to resolve oracle database using sqlresolver and im getting an error
"
failed to retrieve users: (DatabaseError) ORA-12505: TNS:listener does not currently know of SID given in connect descriptor None None"
Blockquote
Note : Im using an ubuntu machine and my TNS get resolved and im able to connect to databse using
sqlplus64 USERNAME/PASSWORD@DATABASE
My Debug logs:
[2018-09-03 12:00:54,093][2116][139726866171648][DEBUG][privacyidea.lib.config:72] The singleton <class ‘privacyidea.lib.config.ConfigClass’> already exists.
[2018-09-03 12:00:54,096][2116][139726866171648][DEBUG][privacyidea.lib.user:185] Entering get_user_from_param with arguments ({u’Map’: u’{ “userid” : “USER_NUMBER”, “username”: “USER_NAME”,“email” : “EMAIL” }‘, u’Database’: u’DATABASE’, u’Driver’: u’oracle+cx_oracle’, u’Editable’: False, u’Server’: u’HOST_IP’, u’Limit’: u’100000000000’, u’User’: u’USERNAME’, u’Table’: u’USER_TABLE’, u’Password’: u’PASSWORD’, u’type’: u’sqlresolver’, u’Port’: u’1521’},) and keywords {}
[2018-09-03 12:00:54,097][2116][139726866171648][DEBUG][privacyidea.lib.user:185] Entering User with arguments () and keywords {‘login’: ‘’, ‘realm’: ‘’, ‘resolver’: None}
[2018-09-03 12:00:54,097][2116][139726866171648][DEBUG][privacyidea.lib.user:197] Exiting User with result
[2018-09-03 12:00:54,097][2116][139726866171648][DEBUG][privacyidea.lib.user:197] Exiting get_user_from_param with result
[2018-09-03 12:00:54,098][2116][139726866171648][DEBUG][privacyidea.lib.config:72] The singleton <class ‘privacyidea.lib.policy.PolicyClass’> already exists.
[2018-09-03 12:00:54,099][2116][139726866171648][DEBUG][privacyidea.lib.audit:185] Entering getAudit with arguments (<Config {‘JSON_AS_ASCII’: True, ‘PI_HSM’: ‘default’, ‘PI_LOGFILE’: ‘/var/log/privacyidea/privacyidea.log’, ‘pi_hsm’: {‘obj’: <privacyidea.lib.security.default.DefaultSecurityModule object at 0x7f1497941b90>}, ‘PI_AUDIT_KEY_PUBLIC’: ‘/etc/privacyidea/public.pem’, ‘SQLALCHEMY_POOL_RECYCLE’: None, ‘SQLALCHEMY_ECHO’: False, ‘PI_PEPPER’: ‘E05aCs2ex6xaWSDBATyiZD6_’, ‘SQLALCHEMY_TRACK_MODIFICATIONS’: True, ‘SQLALCHEMY_POOL_TIMEOUT’: None, ‘SQLALCHEMY_RECORD_QUERIES’: None, ‘PI_ENCFILE’: ‘/etc/privacyidea/enckey’, ‘SESSION_COOKIE_DOMAIN’: None, ‘SESSION_COOKIE_NAME’: ‘session’, ‘pi_token_types’: [‘registration’, ‘hotp’, ‘pw’, ‘spass’, ‘paper’, ‘sms’, ‘email’, ‘sshkey’, ‘radius’, ‘yubico’, ‘motp’, ‘daplug’, ‘vasco’, ‘remote’, ‘certificate’, ‘tiqr’, ‘ocra’, ‘4eyes’, ‘totp’, ‘u2f’, ‘yubikey’, ‘question’], ‘pi_resolver_types’: [‘UserIdResolver’, ‘ldapresolver’, ‘scimresolver’, ‘UserIdResolver’, ‘sqlresolver’, ‘UserIdResolver’, ‘passwdresolver’, ‘UserIdResolver’], ‘PI_EXTERNAL_LINKS’: True, ‘SQLALCHEMY_NATIVE_UNICODE’: None, ‘MAX_CONTENT_LENGTH’: None, ‘PERMANENT_SESSION_LIFETIME’: datetime.timedelta(31), ‘SQLALCHEMY_POOL_SIZE’: None, ‘SQLALCHEMY_MAX_OVERFLOW’: None, ‘TRAP_HTTP_EXCEPTIONS’: False, ‘PRESERVE_CONTEXT_ON_EXCEPTION’: None, ‘SESSION_COOKIE_PATH’: None, ‘SQLALCHEMY_DATABASE_URI’: ‘mysql://pi:w0bE5mDZMxL_@localhost/pi’, ‘LOGGER_NAME’: ‘privacyidea.app’, ‘SECRET_KEY’: ‘EqfHw70vouUOTF9WLc31uzL2’, ‘APPLICATION_ROOT’: None, ‘SERVER_NAME’: None, ‘BABEL_DEFAULT_LOCALE’: ‘en’, ‘PREFERRED_URL_SCHEME’: ‘http’, ‘TESTING’: False, ‘BABEL_DEFAULT_TIMEZONE’: ‘UTC’, ‘PI_VASCO_LIBRARY’: None, ‘USE_X_SENDFILE’: False, ‘PI_AUDIT_MODULE’: ‘privacyidea.lib.auditmodules.sqlaudit’, ‘SESSION_COOKIE_SECURE’: False, ‘SQLALCHEMY_BINDS’: None, ‘DEBUG’: False, ‘SQLALCHEMY_COMMIT_ON_TEARDOWN’: False, ‘CACHE_TYPE’: ‘simple’, ‘JSONIFY_PRETTYPRINT_REGULAR’: True, ‘PROPAGATE_EXCEPTIONS’: None, ‘PI_LOGLEVEL’: 10, ‘TRAP_BAD_REQUEST_ERRORS’: False, ‘JSON_SORT_KEYS’: True, ‘SESSION_COOKIE_HTTPONLY’: True, ‘SEND_FILE_MAX_AGE_DEFAULT’: 43200, ‘PI_AUDIT_KEY_PRIVATE’: ‘/etc/privacyidea/private.pem’, ‘SUPERUSER_REALM’: [‘super’]}>,) and keywords {}
[2018-09-03 12:00:54,099][2116][139726866171648][DEBUG][privacyidea.lib.audit:185] Entering getAuditClass with arguments (‘privacyidea.lib.auditmodules.sqlaudit’, ‘Audit’) and keywords {}
[2018-09-03 12:00:54,099][2116][139726866171648][DEBUG][privacyidea.lib.audit:76] klass: <class ‘privacyidea.lib.auditmodules.sqlaudit.Audit’>
[2018-09-03 12:00:54,100][2116][139726866171648][DEBUG][privacyidea.lib.audit:197] Exiting getAuditClass with result <class ‘privacyidea.lib.auditmodules.sqlaudit.Audit’>
[2018-09-03 12:00:54,100][2116][139726866171648][DEBUG][privacyidea.lib.auditmodules.sqlaudit:92] using the connect string mysql://pi:w0bE5mDZMxL_@localhost/pi
[2018-09-03 12:00:54,101][2116][139726866171648][DEBUG][privacyidea.lib.auditmodules.sqlaudit:99] Using SQL pool_size of 20
[2018-09-03 12:00:54,101][2116][139726866171648][DEBUG][privacyidea.lib.audit:197] Exiting getAudit with result <privacyidea.lib.auditmodules.sqlaudit.Audit object at 0x7f14839e58d0>
[2018-09-03 12:00:54,102][2116][139726866171648][DEBUG][privacyidea.lib.config:185] Entering get_from_config with arguments (‘OverrideAuthorizationClient’,) and keywords {}
[2018-09-03 12:00:54,102][2116][139726866171648][DEBUG][privacyidea.lib.config:72] The singleton <class ‘privacyidea.lib.config.ConfigClass’> already exists.
[2018-09-03 12:00:54,103][2116][139726866171648][DEBUG][privacyidea.lib.config:197] Exiting get_from_config with result None
[2018-09-03 12:00:54,104][2116][139726866171648][DEBUG][privacyidea.api.resolver:185] Entering test_resolver with arguments () and keywords {}
[2018-09-03 12:00:54,104][2116][139726866171648][DEBUG][privacyidea.lib.resolver:185] Entering pretestresolver with arguments (u’sqlresolver’, {u’Map’: u’{ “userid” : “USER_NUMBER”, “username”: “USER_NAME”,“email” : “EMAIL” }‘, u’Database’: u’DATABASE’, u’Driver’: u’oracle+cx_oracle’, u’Editable’: False, u’Server’: u’HOST_IP’, u’Limit’: u’100000000000’, u’User’: u’USERNAME’, u’Table’: u’USER_TABLE’, u’Password’: u’PASSWORD’, u’type’: u’sqlresolver’, u’Port’: u’1521’}) and keywords {}
[2018-09-03 12:00:54,104][2116][139726866171648][DEBUG][privacyidea.lib.config:185] Entering get_resolver_list with arguments () and keywords {}
[2018-09-03 12:00:54,104][2116][139726866171648][DEBUG][privacyidea.lib.config:549] None
[2018-09-03 12:00:54,105][2116][139726866171648][DEBUG][privacyidea.lib.config:197] Exiting get_resolver_list with result set([‘privacyidea.lib.resolvers.PasswdIdResolver’, ‘privacyidea.lib.resolvers.SCIMIdResolver’, ‘privacyidea.lib.resolvers.SQLIdResolver’, ‘privacyidea.lib.resolvers.LDAPIdResolver’])
[2018-09-03 12:00:54,105][2116][139726866171648][DEBUG][privacyidea.lib.config:682] using the module list: set([‘privacyidea.lib.resolvers.PasswdIdResolver’, ‘privacyidea.lib.resolvers.SCIMIdResolver’, ‘privacyidea.lib.resolvers.SQLIdResolver’, ‘privacyidea.lib.resolvers.LDAPIdResolver’])
[2018-09-03 12:00:54,105][2116][139726866171648][DEBUG][privacyidea.lib.config:690] import module: privacyidea.lib.resolvers.PasswdIdResolver
[2018-09-03 12:00:54,106][2116][139726866171648][DEBUG][privacyidea.lib.config:690] import module: privacyidea.lib.resolvers.SCIMIdResolver
[2018-09-03 12:00:54,106][2116][139726866171648][DEBUG][privacyidea.lib.config:690] import module: privacyidea.lib.resolvers.SQLIdResolver
[2018-09-03 12:00:54,106][2116][139726866171648][DEBUG][privacyidea.lib.config:690] import module: privacyidea.lib.resolvers.LDAPIdResolver
[2018-09-03 12:00:54,106][2116][139726866171648][DEBUG][privacyidea.lib.config:503] module: <module ‘privacyidea.lib.resolvers.PasswdIdResolver’ from ‘/usr/lib/python2.7/dist-packages/privacyidea/lib/resolvers/PasswdIdResolver.pyc’>
[2018-09-03 12:00:54,107][2116][139726866171648][DEBUG][privacyidea.lib.config:503] module: <module ‘privacyidea.lib.resolvers.SCIMIdResolver’ from ‘/usr/lib/python2.7/dist-packages/privacyidea/lib/resolvers/SCIMIdResolver.pyc’>
[2018-09-03 12:00:54,107][2116][139726866171648][DEBUG][privacyidea.lib.config:503] module: <module ‘privacyidea.lib.resolvers.SQLIdResolver’ from ‘/usr/lib/python2.7/dist-packages/privacyidea/lib/resolvers/SQLIdResolver.pyc’>
[2018-09-03 12:00:54,107][2116][139726866171648][DEBUG][privacyidea.lib.config:503] module: <module ‘privacyidea.lib.resolvers.LDAPIdResolver’ from ‘/usr/lib/python2.7/dist-packages/privacyidea/lib/resolvers/LDAPIdResolver.pyc’>
[2018-09-03 12:00:54,108][2116][139726866171648][DEBUG][privacyidea.lib.resolvers.SQLIdResolver:447] SQL connectstring: ‘oracle+cx_oracle://USERNAME:PASSWORD@HOST_IP:1521/DATABASE’
[2018-09-03 12:00:54,108][2116][139726866171648][INFO][privacyidea.lib.resolvers.SQLIdResolver:471] using the connect string oracle+cx_oracle://USERNAME:PASSWORD@HOST_IP:1521/DATABASE
[2018-09-03 12:00:54,114][2116][139726866171648][DEBUG][privacyidea.lib.resolver:197] Exiting pretestresolver with result (-1, ‘failed to retrieve users: (DatabaseError) ORA-12505: TNS:listener does not currently know of SID given in connect descriptor\n None None’)
[2018-09-03 12:00:54,115][2116][139726866171648][DEBUG][privacyidea.api.resolver:197] Exiting test_resolver with result <Response 391 bytes [200 OK]>
Thankyou in advance.