mssql (user+pw connection) Timed out in bb8

[Julian-J-S]

Hi all,
I am evaluating polars instead of pandas for data related worflows and reading from databases uses connectorx which seems to be quite fast but in my first experience also not as easy to use because there are no useful error messages. It is either working or bb8 error. Took hours to get it working because I used True/yes instead of true...

I successfully managed to connect to sql server using windows authentication like this:

• connectorx: f"mssql://{SERVER}/{DATABASE}?trusted_connection=true"
• pandas: f"mssql+pyodbc://{SERVER}/{DATABASE}?trusted_connection=yes&driver=ODBC+Driver+17+for+SQL+Server"

However, I cannot get this to work using user+pw auth (works with pandas):

• pandas: f"mssql+pyodbc://{USER}:{PASSWORD}@{SERVER}/{DATABASE}?driver=ODBC+Driver+17+for+SQL+Server"

I have tried so many different variations but I am stuck now. The problem is that there is absolutely no feedback or usefull error message. It will either work or crash with RuntimeError: Timed out in bb8 so I have no idea what exactly is wrong.
Here some of the variations I tried:

• f'mssql://{USER}:{PASSWORD}@{SERVER}:1433/{DATABASE}'
• f'mssql://{USER}:{PASSWORD}@{SERVER}/{DATABASE}'
• f'mssql://{USER}:{PASSWORD}@{SERVER}:1433/{DATABASE}?TrustServerCertificate=Yes'
• f'mssql://{USER}:{PASSWORD}@{SERVER}:1433/{DATABASE}?encrypt=true&trusted_connection=true'
• f'mssql://{USER}:{PASSWORD}@{SERVER}:1433/{DATABASE}?encrypt=true&trusted_connection=false'
• f'mssql://{USER}:{PASSWORD}@{SERVER}:1433/{DATABASE}?trusted_connection=true'
• f'mssql://{USER}:{PASSWORD}@{SERVER}/{DATABASE}?trusted_connection=true'
• f'mssql://{USER}:{PASSWORD}@{SERVER}:1433/{DATABASE}?driver=SQL+Server'
• f'mssql://{USER}:{PASSWORD}@{SERVER}:1433/{DATABASE}?driver=SQL+Server&trusted_connection=true'
• f'mssql://{USER}:{PASSWORD}@{SERVER}:1433/{DATABASE}?driver=ODBC+Driver+17+for+SQL+Server&trusted_connection=false'
• f'mssql://{USER}:{PASSWORD}@{SERVER}:1433/{DATABASE}?encrypt=true&trusted_connection=true'
• f'mssql://{USER}:{PASSWORD}@{SERVER}/{DATABASE}?encrypt=true&trusted_connection=true'
• f'mssql://{USER}:{PASSWORD}@{SERVER}:1433/{DATABASE}?encrypt=false&trusted_connection=false'

Any ideas? =)

[tikkanz]

I'm having the same problem. Have also tried numerous as well as various encodings of my usercode & password as discussed here.
Is anybody successfully able to connect to MS SQL Server using the URI format above?

[orlandoflv]

This is what worked for me:

f'mssql://{USER}:{PASSWORD}@{SERVER}:1433/{DATABASE}?encrypt=true'

[johnpyrce]

Same problem. Obviously this works for some people, but I can't find what the problem is. I had problems with my SQL Server password, requiring URL encoding, but fixing that just ends up with the "Timed out in bb8" error.

[TheDataScientistNL]

Tried all types of flavours to connect to a local mssql instance, to no avail. Python==3.10.11, polars==0.19.6

In general,

f"mssql://{username}:{passwd}@localhost:1433/{dbname}"

does not work, neither with the addition of ?trusted_connection=false.

Similarly, trying MS authentication through

f"mssql://localhost:1433/{dbname}?trusted_connection=true"

does all lead to the same timeouts.

Inspecting the RUST code at https://github.com/sfu-db/connector-x/blob/main/connectorx/src/sources/mssql/mod.rs, L49 and L60, it seems that localhost is supported and that the port 1433 is inferred when no port is given.

The code also shows that to use encrypt or trusted_connection, you must use lowercase true or false, not Yes/No/True/False.

I am no rust programmer but I think it goes wrong at line 108...

Would be great if this gets resolved. In the meantime, if using polars, please check pola-rs/polars@9026165 which will hopefully be added to the new release soon. Not as quick as connectorx I guess, but at least there will be support for ODBC connections!

[adeljaouen]

Eliminating the =true part from the boolean parameters resolved the issue for me :

conn = f'mssql://{username}:{parse.quote_plus(password)}@{host}:{port}/{db_name}?trusted_connection&encrypt'` 

As detailed this in my comment here.

Hope it could help.