Appsheet Integration On-Premise SQL - Connection Problem

[EfeKentli]

When I try to add an on-premises database in AppSheet, it prompts me to use a DreamFactory connection, but I prefer not to use DreamFactory. I have already configured the firewall to allow access to the SQL Server via public IP, and I have also allowed access from AppSheet’s EU IP ranges.In the database connection form, I enter the public IP and port number in the “Server” field, and for the “User Email,” I provide the SQL Server login credentials (email and password) that I created on the SQL Server.However, when I click "Test Connection," I receive a connection error.Could you help me understand what I might be doing wrong and how to fix the issue?

[Steve]

Please contact AppSheet Support for help with this.

[EfeKentli]

I have spoken with AppSheet support, and they mentioned that using DreamFactory is a mandatory requirement for connecting on-premises data. However, I believe there must be alternative methods. For example, wouldn't it be possible to pull data from an on-premises SQL Server into Google Sheets using App Script or a similar solution? When I checked DreamFactory's pricing, I saw that it costs around $1,000–$1,500 per month, which seems unreasonable just for enabling an on-premises SQL data connection. Is there no more cost-effective or even free and secure alternative to establish this connection?

[xeonavn]

I am using on prem mysql for years, and there is no problem, I can also automate from gsheet to mysql onprem(VV) without any problem. If ur adding new data source, use Cloud Database configuration, instead choosing On-premises Database.

[ModernAir]

I ran into this before in a similar environment and realized even using an IP for the server address, i needed to prefix it with https:// which is not documented anywhere.

[EfeKentli]

I also tried adding https:// to the beginning, but I still couldn't connect. Did you make this on-premises SQL connection recently? Could the Dream Factory requirement have just been introduced?

[Steve]

Dream Factory has been involved for years.

[Steve]

---

@EfeKentli wrote:

For example, wouldn't it be possible to pull data from an on-premises SQL Server into Google Sheets using App Script or a similar solution?

---

That'd only be reasonable if the spreadsheet was read-only for the app.

---

Is there no more cost-effective or even free and secure alternative to establish this connection?

---

I don't know but I doubt it.

[pbalerio]

When you open a firewall port to see your on-premises database from the web you should not need a Dreamfactory connection. 

Instead, use appsheet as connecting to a regular SQL DB.

I would not use the ip:port but only the ip, since the port is open by the firewall.  When I did this I have created an IP virtual SQL server machine with external IP xxx.xxx.xxx.xxx that maps to to  local IP 10.yy.yyy.yyy.  And provide a ports TCP tunnel (external port to a port of the instance of the SQL)

Please take into account that even if you make a virtual SQL your data is exposed.

[pbalerio]

And the responsiveness of an on-premises SQL is usually less than a cloud hosted SQL (Azure, Amazon WS, Google CS)