Help the Newbie! SQL Connection not working anymore

Sasha

Backstory: My company bought Domo over a year ago, and the lone executive that wanted it left. I'm a Power BI Developer so I put it on the back burner. We just got acquired and the new president…likes Domo! Uh-Oh!

Problem: I need to access data on a database that wasn't previously connected. When I attempted to connect I got an error. I checked the way I had configured the other database, which still refreshes, but when I click save I get the same error!

TLDR: The old connection throws an error when I click save, but the connection works just fine if I don't save.

What does this error really mean, and why am I getting it when adding a database or saving an older database.

MarkSnodgrass

I would suggest using Workbench if you are able. It is very easy to use. With the SQL connectors, you are going to need to make sure the correct firewall ports are open, which can be challenging for some organizations.

In Workbench, you can create an ODBC connection and then use the connection for your connection string. Workbench will transfer the data securely for you and gives you flexibility over scheduling and data transformations. It's definitely worth looking into.

MarkSnodgrass

You can install Workbench on a VM. That's where mine is installed. Just needs to be any Windows OS. A VM would be the best location because, as you said, if it is on your laptop, then it will only run when your laptop is on and connected to the internet.

MarkSnodgrass

Federated datasets are typically fully materialized and come from another Domo instance or from Snowflake, etc. They currently can't be used in ETLs because of how Domo stores the data.

If you are using Workbench, these won't be federated datasets and you can use Magic ETL on these datasets to combine or manipulate them.

Hope this helps.

MarkSnodgrass

Are you using Domo Workbench to connect to your SQL database? Or are you using a connector that you choose from in the Data Center?

Sasha

Thanks for responding, and good question. I have been using the SQL Connector (highlighted gray in the image), but I am watching videos on the workbench.

MarkSnodgrass

I would suggest using Workbench if you are able. It is very easy to use. With the SQL connectors, you are going to need to make sure the correct firewall ports are open, which can be challenging for some organizations.

In Workbench, you can create an ODBC connection and then use the connection for your connection string. Workbench will transfer the data securely for you and gives you flexibility over scheduling and data transformations. It's definitely worth looking into.

Sasha

First and foremost, thank you for your help. I just downloaded it, and I'll work on configuring it today.

I am confused and frustrated as to why it has been working for a year, but throws errors when I open the connection. Plus, half my database will be coming through a SQL connection and the other half through Workbench. I'm also going to have to explain this to my security guy with words that sound more professional that "I dunno".

Sasha

Also, I haven't found anything anywhere (yet) suggesting I download Workbench on a virtual machine. If I install this on my personal local laptop, can I assume it will only run when I'm connected to it?

MarkSnodgrass

Personally, I would make a plan to migrate all of your datasets over to Workbench so that they all run through that. Your security should like that better actually.

As far as the SQL connector, if you have some that are running and some that aren't, if you have verified that they are all configured the same, then you might need to open up a support ticket and support will be to identify more detailed error messaging than what is being presented to you.

MarkSnodgrass

You can install Workbench on a VM. That's where mine is installed. Just needs to be any Windows OS. A VM would be the best location because, as you said, if it is on your laptop, then it will only run when your laptop is on and connected to the internet.

Sasha

If you have a second to answer one more question. I read a book on Domo called Data Democratization back in January. In that book, it suggested using Federated connection if you already have a good database. When I tried that yesterday, it wouldn't let me transform the data in Magic ETL or SQL ETL. Why would the book recommend that, and why I can't I transform a Federated Data Source?

MarkSnodgrass

Federated datasets are typically fully materialized and come from another Domo instance or from Snowflake, etc. They currently can't be used in ETLs because of how Domo stores the data.

If you are using Workbench, these won't be federated datasets and you can use Magic ETL on these datasets to combine or manipulate them.

Hope this helps.