Unable to create state connection

[Kedhar-P]

Hi,

We are attempting to migrate to SQLMesh. We are using the Trino execution engine, which works great for us. However, we have been unable to setup a working state connection for production (as Trino is currently unsupported). Our tech stack includes Trino and MSSQL. Both are authenticated to using Kerberos only. While this works great for Trino, we have been unable to make it work with MSSQL. This is because we require our connection to be trusted, but this is only a parameter with pyodbc, not pymssql (which is used in SQLMesh)

Is there a way to either enable support for Kerberos authentication with the MSSQL engine (instead of username/password), perhaps using the pyodbc library, or will there be support for storing the state connection with Trino?

Thanks

[erindru]

Conceptually - MSSQL is exactly the right type of database to use for state in production.

However, as you discovered we use pymssql so there is no way to connect to MSSQL via mechanisms other than what pymssql supports. I think you'd need to create a local user and use username/password authentication to make this work today.

We have Fabric support on our roadmap in which case SQLMesh will gain support for pyodbc because pymssql does not support the the array of authentication types required to interact with anything in Azure

Contributions are always welcome as well, a good starting point would be adjusting MSSQLConnectionConfig to support pyodbc in addition to pymssql

[Kedhar-P]

Hi, thanks for your response. Unfortunately username/password authentication is not possible for us, so this issue is a blocker for us migrating to SQLMesh unfortunately.