0

currently I am accessing my MS SQL database from Python using SQLalchemy with the Native SQL Client. According to this page it is deprecated you should use the Microsoft OLE DB Driver for SQL Server instead. My problem now is that I cannot figure out how to change the connection string. My old working string for the Native client was:

engine = sqlalchemy.create_engine(f'mssql+pyodbc://{databaseAccess}@{Hostname:Port}/{database}?driver=SQL+Server+Native+Client+11.0', echo=True)

For the new string my best guess is the following but of course it does not work. I assume the driver variable must be different and maybe the pyodbc must also be replaced.

sqlalchemy.create_engine(f'mssql+pyodbc://{databaseAccess}@{Hostname:Port}/{database}?driver=Microsoft+OLE+DB+Driver+19+for+SQL+Server', echo=True)

Just to make this post complete: The error I get is the following sadly it is in German but it basically says: that the data source could not be found as the driver is no default driver. I strongly assume that this just means my string is wrong.

(pyodbc.InterfaceError) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Der Datenquellenname wurde nicht gefunden, und es wurde kein Standardtreiber angegeben (0) (SQLDriverConnect)')
(Background on this error at: https://sqlalche.me/e/14/rvf5)
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Manuel
  • 649
  • 5
  • 22
  • Here's how I write my connection string - might be of use? https://stackoverflow.com/a/72546285/2432555 – JonTout Jun 22 '22 at 08:11
  • Be aware that, as with [ODBC Driver 18 for SQL Server](https://techcommunity.microsoft.com/t5/sql-server-blog/odbc-driver-18-0-for-sql-server-released/ba-p/3169228) and [JDBC Driver 10.2 for SQL Server](https://techcommunity.microsoft.com/t5/sql-server-blog/jdbc-driver-10-2-for-sql-server-released/ba-p/3100754), the OLE DB Driver 19 for SQL Server has [breaking changes](https://learn.microsoft.com/en-us/sql/connect/oledb/release-notes-for-oledb-driver-for-sql-server) to do with encrypted connections, i.e. `Encrypt=True/Yes;` is the new default setting. – AlwaysLearning Jun 22 '22 at 09:16
  • 1
    Even OLE DB is problematic, at best. YMMV, but accessing SQL Server from SQLalchemy applications is perhaps best achieved using ODBC Driver for SQL Server - at least it's supported on macOS and Linux in addition to Windows. – AlwaysLearning Jun 22 '22 at 09:20
  • 2
    To clarify: SQLAlchemy does not support OLE DB connections to SQL Server. ODBC connections are supported via `mssql+pyodbc://` and Microsoft's ODBC drivers (e.g. ODBC Driver 17/18 for SQL Server). – Gord Thompson Jun 22 '22 at 17:49

0 Answers0