Open mavestergaard opened 1 year ago
Don't think you need to declare the driver, but I notice you have not assigned the server port, try something like this: f"mssql://{username}:{parse.quote_plus(pw)}@{servername}:{port}/{dbname}?encrypt=true"
Only add trusted_connection=true if your DB is using AD (MS Entra) authentication.
Connecting to MSSQL works, but not with encrypt = true as shown here: https://sfu-db.github.io/connector-x/databases/mssql.html
To reproduce: import connectorx query = "SELECT current_timestamp as ct" mssql_url = f'mssql://localhost:1433/AdventureWorksDW2019?trusted_connection=true' df = connectorx.read_sql(mssql_url, query)
Fails with timeout: mssql_url = f'mssql://localhost:1433/AdventureWorksDW2019?encrypt=true&trusted_connection=true'.
encrypt=false works
As with the OP's environment, trusted and encrypted connection works in PyODBC, Pandas, etc.
Was encountering this myself and long story short, it was a certificate issue. I'm using the connection string format below, and was getting the same non-descript error about time out in bb8.
"mssql://username:password@server:port/database?encrypt=true"
I did a little digging and found out bb8 was the connection manager, so went looking into a few other connection alternatives. The package pymssql
was able to connect with no issues, so it's definitely not a network connectivity problem. However, when trying pyodbc
with a connection string like:
"DRIVER={ODBC Driver 18 for SQL Server}; Server=server; UID=username; PWD=password; DataBase=database; Encrypt=yes"
I got the following error:
pyodbc.OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: [error:0A000086:SSL routines::certificate verify failed:unable to get local issuer certificate] (-1) (SQLDriverConnect)')
If I added TrustServerCertificate=yes;
to the connection string, though, the connection would work. Since connectorx doesn't have this option available (it would be great if it did in the future), I had to get the .crt chain file from the sql server I was connecting to and add it to the ca-certificates:
copy crt file to "/usr/local/share/ca-certificates/sql-server-chain-file.crt"
run "chmod 644 /usr/local/share/ca-certificates/sql-server-chain-file.crt"
run "update-ca-certificates"
I suspect the error is related to this https://github.com/sfu-db/connector-x/issues/233 (or vice versa).
Also related https://github.com/sfu-db/connector-x/issues/528
Hi, any update on the MSSQL bug? Thanks
After a lot of trial and error I managed to get the following to connect to an Azure SQL DB:
mssql://<user>:<password>@<server>:<port>/<database>?driver=ODBC+Driver+17+for+SQL+Server&encrypt=true
seemed &encrypt=true
did the trick
Please let me know if this works for others?
What language are you using?
Python.
What version are you using?
Python 3.10 connectorx==0.3.1
What database are you using?
MSSQL
What dataframe are you using?
Polars
Can you describe your bug?
I am trying to connect to our Microsoft SQL Database. It works with telnet, SQL Server Management Studio and pyodbc. When I connect with connectorx i get error: RuntimeError: Timed out in bb8
What are the steps to reproduce the behavior?
Here are the strings I have tested: from urllib import parse import connectorx as cx
return f"mssql://{username}:{parse.quote_plus(pw)}@{servername}/{dbname}?driver=ODBC+Driver+18+for+SQL+Server" return f"mssql://{username}:{parse.quote_plus(pw)}@{servername}/{dbname}?driver=ODBC+Driver+17+for+SQL+Server" return f"mssql+pyodbc://{username}:{parse.quote_plus(pw)}@{servername}/{dbname}?driver=ODBC+Driver+18+for+SQL+Server" return f"mssql+pyodbc://{username}:{parse.quote_plus(pw)}@{servername}/{dbname}?driver=ODBC+Driver+17+for+SQL+Server" return f"mssql://{username}:{parse.quote_plus(pw)}@{servername}/{dbname}" return f"mssql://{username}:{parse.quote_plus(pw)}@{servername}/{dbname}?driver=SQL+Server" return f"mssql://{username}:{parse.quote_plus(pw)}@{servername}/{dbname}?trusted_connection=true&encrypt=true" return f"mssql://{username}:{parse.quote_plus(pw)}@{servername}/{dbname}?trusted_connection=false&encrypt=true" return f"mssql://{username}:{parse.quote_plus(pw)}@{servername}/{dbname}?trusted_connection=false&encrypt=false" return f"mssql://{username}:{parse.quote_plus(pw)}@{servername}/{dbname}?trusted_connection=true&encrypt=false"
Database setup if the error only happens on specific data or data type
Cannot connect to the db, so query doesnt matter
Example query / code
Same as above cx.read_sql(conn = con_str, query = "SELECT 1")
What is the error?
RuntimeError: Timed out in bb8
Really hope you can help - thank you very much :D