I’m trying to get my recorder DB off the Pi and onto an MS SQL instance I have on another machine. The recorder docs say “For MS SQL Server you may have to install a few dependencies”. But how could I do this on HASSIO since we’re in a container and can’t use pip3? I’m struggling to find any resources at all about using MS SQL.
I guess I’m just trying to figure out if anyone has done this before I go down a rabbit hole of misery.
I’m trying to do the same thing. What does your connection string look like in your config yaml? For some reason I can’t get connected.
Did you do anything special when you created the blank DB in SQL? Just DBO for permissions? Thanks
Config entry:
recorder:
db_url: mssql+pyodbc://[SQLUser]:[Password]@[xxx.xxx.xxx.xxx]/[DBName]?charset=utf8;DRIVER={FreeTDS};Port=1433;
I had to change the server authentication mode to “SQL Server and Windows Authentication Mode” and create the user HASSIO under security>logins.
I gave the HASSIO user db_owner for the bare HOMEASSISTANT DB and it created the table.
You can always look at the logs to see why SQL Server is angry:
Edit: config entry updated
After upgrading to 0.104.0 I’m now missing pymssql. Does anyone know how I can add this to my hassio install?
2020-01-16 10:27:49 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: No module named 'pymssql' (retrying in 3 seconds)
It’s in the docs.
My question is specifically related to a hassio installation. I can’t access a terminal other than the hassio CLI, which I can’t pip-install from.
This was working until this upgrade without me adding the dependency and there was no notes in the changelog about this being broken.
Sometimes I have trouble seeing the highlighted text in front of me! Thanks! That works.
I do wonder why mssql+pyodbc worked until now…
I’m also trying to use a MS SQL Express Server to store my HA DB. If I adress it just by IP how can I name the instance that is meant to be used?
If you’re referring to selecting the correct database, replace DB_NAME with the name of the empty DB you created for HA.
I’m wondering the same thing, how does it know what instance to try to connect to? So far I’m having no luck connecting.
By instance do you mean the instance of MS SQL Server (like the computer you’re trying to connect to) or the database (created inside the running instance on that computer)?
You can share your config and I can try to assist, you would probably want to obfuscate your password though.
I tried a couple of versions of the connection string, gave up, installed MariaDB on my Win Server and had it up and running in 10 minutes.
Yes, I have two instances of SQL Server running. I stopped one of them, just to see if that would help. It didn’t. My setup is on Windows 10, VritualBox, here’s the details:
| arch | x86_64 |
|---|---|
| chassis | vm |
| dev | false |
| docker | true |
| docker_version | 19.03.11 |
| hassio | true |
| host_os | HassOS 4.14 |
| installation_type | Home Assistant OS |
| os_name | Linux |
| os_version | 5.4.69 |
| python_version | 3.8.6 |
| supervisor | 2020.11.0 |
| timezone | America/Los_Angeles |
| version | 0.117.5 |
| virtualenv | false |
| dashboards | 1 |
|---|---|
| mode | storage |
| resources | 0 |
| views | 4 |
In SQL Server, I setup a new database “HomeAssistant”, and a new user “ha”, giving that user permissions for the new database. My SQL has the remote access checkbox checked.
My problem has been that in just 30 or 40 days, my database has gotten to 30GB, and I cannot purge it. I start a purge, and watch the files, I see those two temporary file created and the one grows up to about 2GB or so (differs each time), then that stops for several minutes, then next thing I know the two temp files disappear, and start flashing off and on the screen as they are created and deleted (same normal activity I see when not doing a purge). I ended up deleting my db, and let it be recreated last night. I’m trying to use the exclude in recorder to not log as much data.
Since I have SQL Server running already, and one person said that it would be faster than SQLLite, I decided to try that. Plus I’d be in full control of the SQL Server, able to do whatever I wanted.
Anyway, in recorder, I have my SQL Server I’m trying to switch to:
db_url: mssql+pyodbc://ha:[email protected]/HomeAssistant?charset=utf8;DRIVER={FreeTDS};Port=1433;
I did not do any special setup, in the recorder docs, those 5 lines they say to run don’t work. They are supposed to “install a few dependancies” and pyodbc. There is no “sudo” command. So I am assuming that stuff may already be installed on my HassOS? BTW: Port 1433 is open on my firewall.
Under SQL server logs, I see no attempt, not reached there. Under the HA logs, I see the following:
2020-11-13 09:26:59 WARNING (MainThread) [homeassistant.setup] Setup of recorder is taking over 10 seconds.
2020-11-13 09:27:49 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
2020-11-13 09:28:49 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
2020-11-13 09:29:49 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
2020-11-13 09:30:49 WARNING (MainThread) [homeassistant.bootstrap] Waiting on integrations to complete setup: recorder
2020-11-13 09:31:10 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: (pyodbc.OperationalError) (‘08S01’, ‘[08S01] [FreeTDS][SQL Server]Unable to connect: Adaptive Server is unavailable or does not exist (20009) (SQLDriverConnect)’)
(Background on this error at: http://sqlalche.me/e/13/e3q8) (retrying in 3 seconds)
Let me know if you have any ideas, thanks in advance!
I got mine working under MS SQL Server. The main issue was the 1433 port. Under the Computer Management/SQL Server Configuration Manager, I had to turn on TCP. Then my port number was some weird dynamic port number, so I changed that to blank on the dynamic port, and typed in 1433 under “TCP Port” under the IPAll section of IP’s. Then I had one issue with the sql user permissions, where I’d checked all boxes, including the two “deny” permissions. Don’t check the deny permissions! Now I have it working under MS SQL Server. I hope this helps someone else.
This page helped me: http://lexisnexis.custhelp.com/app/answers/answer_view/a_id/1095989/~/sql-server-configuration-manager-general-information
Yup! I got the same error and just joined the forum to see if there is any solution / workaround. My Pi 3B is doing fine with HA, but I’m concerned with the database writing constantly to the MicroSD, so I’d rather offload it to my MSSQL instance on my VM.
Running SQL Server 2012, I can see 3 tables getting created, but no data is being stored.
Back to the default DB for now…
(incidentally, is there an easy way to import the existing DB in MSSQL?)
