core: Recorder not working with MS SQL Database

Home Assistant release with the issue:

Last working Home Assistant release (if known): 0.103.0b0

Operating environment (Hass.io/Docker/Windows/etc.): Ubuntu 18.04 LTS / Docker /DB Server: Windows Server 2019 + MS SQL 2017

Integration: Recorder

Description of problem: After updating to 0.103.0 recorder is not working with database running on MS SQL

ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: No module named ‘pymssql’ (retrying in 3 seconds)

Problem-relevant configuration.yaml entries and (fill out even if it seems unimportant):

configuration.yaml
recorder:  
   db_url:  mssql+pymssql://user:password@SERVER_IP/DB_NAME?charset=utf8

Traceback (if applicable):

Additional information:

About this issue

Original URL
State: closed
Created 5 years ago
Reactions: 2
Comments: 29 (22 by maintainers)

Most upvoted comments

Seems to be fixed in 0.103.2

Add or change the following lines to configuration.yaml

recorder: db_url: mssql+pyodbc://my-username:my-password@my-sql-server/my-db?charset=utf8;DRIVER={FreeTDS};Port=1433;

gambith on Dec 19, 2019

You can use https://hub.docker.com/r/sqldiablo/home-assistant-pymssql as a temporary fix until my changes make it into the next build of the stock container. You’ll need to change your db URL to look like this:

'mssql+pyodbc://my-username:my-password@my-sql-server/my-db?charset=utf8;DRIVER={FreeTDS};Port=1433;'

Despite the container name having pymssql, it’s actually pyODBC and the FreeTDS driver, like what I’ve added to the stock container.

sqldiablo on Dec 14, 2019

I have this issue as well. On HassOS. Changing config to use pyodbc does not work, and generates the following error message in logs:

Error during connection setup: (pyodbc.InterfaceError) (‘IM002’, ‘[IM002] [unixODBC][Driver Manager]Data source name not found and no default driver specified (0) (SQLDriverConnect)’) (Background on this error at: http://sqlalche.me/e/rvf5) (retrying in 3 seconds)

johnkiddjr on Dec 13, 2019

Ok, this is weird. When I pulled latest, I got 103.0 of Home Assistant, but the container does include the fix for this issue. So, you won’t get 103.1 just yet, but you will get 103.0 with the FreeTDS driver.

sqldiablo on Dec 18, 2019

While I cannot eliminate the possibility that moving the imports could have caused an issue with MS SQL Server in particular, I don’t think that declaring a dependency on the MS SQL Server python library is the correct solution.

Have you had a look at the documentation? This lists manual steps for several databases including MS SQL Server. https://www.home-assistant.io/integrations/recorder/#ms-sql-server

Maybe there is a smarter way to dynamically load the correct dependency for a particular database engine?

exxamalte on Dec 12, 2019