Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

bug(mssql): authentication issues #6012

Closed
1 task done
ededovic opened this issue Apr 14, 2023 · 4 comments
Closed
1 task done

bug(mssql): authentication issues #6012

ededovic opened this issue Apr 14, 2023 · 4 comments
Labels
bug Incorrect behavior inside of ibis mssql The Microsoft SQL Server backend

Comments

@ededovic
Copy link

What happened?

I keep getting authentication error when attempting to connect to ibis.mssql.connect(...). In general, I connect to that database using pyodbc w/o password, and I'm not entirely sure how to set it up in ibis.

What version of ibis are you using?

5.1

What backend(s) are you using, if any?

MSSQL

Relevant log output

No response

Code of Conduct

  • I agree to follow this project's Code of Conduct
@ededovic ededovic added the bug Incorrect behavior inside of ibis label Apr 14, 2023
@cpcloud
Copy link
Member

cpcloud commented Apr 15, 2023

Hi @ededovic!

Can you show the traceback, or if not that, some more information from the error you're seeing? It's hard to tell what could be going wrong without that!

@cpcloud cpcloud added the mssql The Microsoft SQL Server backend label Apr 17, 2023
@cpcloud
Copy link
Member

cpcloud commented Apr 21, 2023

@ededovic Any updates here?

@ededovic
Copy link
Author

ededovic commented Apr 25, 2023

mssql connection is just lighlty wrapped by a class to pass the parameters. Typically I connect to it via odbc:
pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=' + self.host + ';DATABASE=' + self.database + ';Trusted_Connection=yes;')

for ibis I'm passing this, but maybe it is not correct, as it is handled via windows authentication. I also tried by dropping password.

ibis.mssql.connect(user=self.user, password=self.paswd, host=self.host, database=self.database)

This is edited snipped of stack trace of the error:

---------------------------------------------------------------------------
MSSQLDatabaseException                    Traceback (most recent call last)
File src\pymssql\_pymssql.pyx:647, in pymssql._pymssql.connect()
File src\pymssql\_mssql.pyx:2109, in pymssql._mssql.connect()
File src\pymssql\_mssql.pyx:701, in pymssql._mssql.MSSQLConnection.__init__()
File src\pymssql\_mssql.pyx:1818, in pymssql._mssql.maybe_raise_MSSQLDatabaseException()
File src\pymssql\_mssql.pyx:1835, in pymssql._mssql.raise_MSSQLDatabaseException()

MSSQLDatabaseException: (18456, b"Login failed for user 'ZZZ'.DB-Lib error message 20018, severity 14:\n
General SQL Server error: Check messages from the SQL Server\n
DB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (HOST)\n
DB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (HOST)\n")

During handling of the above exception, another exception occurred:

OperationalError  Traceback (most recent call last)
File PATH\envs\py310\lib\site-packages\sqlalchemy\engine\base.py:3361, in Engine._wrap_pool_connect(self, fn, connection)
   3360 try:
-> 3361     return fn()
   3362 except dialect.dbapi.Error as e:

File PATH\envs\py310\lib\site-packages\sqlalchemy\pool\base.py:327, in Pool.connect(self)
    320 """Return a DBAPI connection from the pool.
    321 
    322 The connection is instrumented such that when its
   (...)
    325 
    326 """
--> 327 return _ConnectionFairy._checkout(self)

File PATH\envs\py310\lib\site-packages\sqlalchemy\pool\base.py:894, in _ConnectionFairy._checkout(cls, pool, threadconns, fairy)
    893 if not fairy:
--> 894     fairy = _ConnectionRecord.checkout(pool)
    896     fairy._pool = pool

File PATH\envs\py310\lib\site-packages\sqlalchemy\pool\base.py:493, in _ConnectionRecord.checkout(cls, pool)
    491 @classmethod
    492 def checkout(cls, pool):
--> 493     rec = pool._do_get()
    494     try:

File PATH\envs\py310\lib\site-packages\sqlalchemy\pool\impl.py:445, in StaticPool._do_get(self)
    444 def _do_get(self):
--> 445     rec = self.connection
    446     if rec._is_hard_or_soft_invalidated():

File PATH\envs\py310\lib\site-packages\sqlalchemy\util\langhelpers.py:1113, in memoized_property.__get__(self, obj, cls)
   1112     return self
-> 1113 obj.__dict__[self.__name__] = result = self.fget(obj)
   1114 return result

File PATH\envs\py310\lib\site-packages\sqlalchemy\pool\impl.py:405, in StaticPool.connection(self)
    403 @util.memoized_property
    404 def connection(self):
--> 405     return _ConnectionRecord(self)

File PATH\envs\py310\lib\site-packages\sqlalchemy\pool\base.py:388, in _ConnectionRecord.__init__(self, pool, connect)
    387 if connect:
--> 388     self.__connect()
    389 self.finalize_callback = deque()

File PATH\envs\py310\lib\site-packages\sqlalchemy\pool\base.py:690, in _ConnectionRecord.__connect(self)
    689 except BaseException as e:
--> 690     with util.safe_reraise():
    691         pool.logger.debug("Error on connect(): %s", e)

File PATH\envs\py310\lib\site-packages\sqlalchemy\util\langhelpers.py:70, in safe_reraise.__exit__(self, type_, value, traceback)
     69     if not self.warn_only:
---> 70         compat.raise_(
     71             exc_value,
     72             with_traceback=exc_tb,
     73         )
     74 else:

File PATH\envs\py310\lib\site-packages\sqlalchemy\util\compat.py:211, in raise_(***failed resolving arguments***)
    210 try:
--> 211     raise exception
    212 finally:
    213     # credit to
    214     # https://cosmicpercolator.com/2016/01/13/exception-leaks-in-python-2-and-3/
    215     # as the __traceback__ object creates a cycle

File PATH\envs\py310\lib\site-packages\sqlalchemy\pool\base.py:686, in _ConnectionRecord.__connect(self)
    685 self.starttime = time.time()
--> 686 self.dbapi_connection = connection = pool._invoke_creator(self)
    687 pool.logger.debug("Created new connection %r", connection)

File PATH\envs\py310\lib\site-packages\sqlalchemy\engine\create.py:578, in create_engine..connect(connection_record)
    577             return connection
--> 578 return dialect.connect(*cargs, **cparams)

File PATH\envs\py310\lib\site-packages\sqlalchemy\engine\default.py:598, in DefaultDialect.connect(self, *cargs, **cparams)
    596 def connect(self, *cargs, **cparams):
    597     # inherits the docstring from interfaces.Dialect.connect
--> 598     return self.dbapi.connect(*cargs, **cparams)

File src\pymssql\_pymssql.pyx:653, in pymssql._pymssql.connect()

OperationalError: (18456, b"Login failed for user 'ZZZ'.DB-Lib error message 20018, severity 14:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (HOST)\nDB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (HOST)\n")




@cpcloud cpcloud changed the title bug: mssql authentication issues bug(mssql): authentication issues May 27, 2023
@cpcloud
Copy link
Member

cpcloud commented Nov 16, 2023

This should be fixed in #7317.

Please try this out on the 8.x.x branch if you're up for it!

@cpcloud cpcloud closed this as completed Nov 16, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Incorrect behavior inside of ibis mssql The Microsoft SQL Server backend
Projects
None yet
Development

No branches or pull requests

2 participants