feat: Support SQL Server flags for self-signed SSL certificates

[edrogers]

Is your feature request related to a problem?

I need to connect to SQL Server being hosted with self-signed SSL certificates. ODBC (and by extension PyODBC) supports this option with some flags. In PyODBC, a connection string might look like:

conn_str = (
    f"Driver={driver};"
    f"Server={server};"
    f"Database={database};"
    "Trusted_Connection=yes;"
    "TrustServerCertificate=yes;"
)

AFAICT, pymssql does not support this functionality

Describe the solution you'd like

A PyODBC implementation would be great 🤞 😅

What version of ibis are you running?

6.0.0

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

MSSQL

Code of Conduct

• I agree to follow this project's Code of Conduct

[gforsyth]

Thanks for opening this @edrogers and for providing us with the keyword arguments needed to connect to your SQL Server!

I've looked over the documentation for pymssql and I'm pretty sure that there is no way to pass this information to SQL Server using pymssql.

In https://pymssql.readthedocs.io/en/stable/ref/_mssql.html#_mssql.MSSQLConnection there is an option for conn_properties but this is a raw SQL string that is executed immediately after connecting, so can't be used to specify authentication options.

I think this is one more reason for us to investigate adding an option to connect using pyodbc.

xref #6306

[cpcloud]

@edrogers Is this still an issue for you on master? We cut over to using pyodbc for the mssql backend.

[cpcloud]

We support the query argument to the sqlalchemy pyodbc mssql dialect, so I'm going to close this out.

Thanks for the report!