Need SQL Server Help

mat.lawrence · 2020-06-27T10:29:42.891Z

Hi, I am relatively new to Bubble and have found working with the Bubble database quite easy to learn.
My next step would be to get connected to an Azure SQL Server database which is currently secure from all external access.

My question is what would be the most secure way to allow Bubble access to my database without compromising security? Can the Bubble team provide static IP for me to whitelist for example?

Anyone else out there successfully achieved a secure Azure connection?

Thanks in advance.

mat.lawrence · 2020-06-28T08:24:16.870Z

Can anyone from Bubble help please?
Without this I will need to look at alternatives which would really be a shame.
Thanks

simon7 · 2020-06-29T06:24:57.926Z

Maybe this post will help: Settings parameters for SQL

In short, Bubble does not use a static ip, so you cant use whitelisting. Another, but more complex option is to create you own api in Azure that you then connect with the api connector in bubble. I’m not familiar with Azure, but from my knowledge you could create your api endpoints with Azure Functions, and give you Azure Functions access to your db through the security settings.

I run a similar setup, but in the AWS universe. It makes it possible for me to run heavy duty workflows outside bubble, and also access my own database securely.

mat.lawrence · 2020-06-29T10:49:56.083Z

Thanks Simon. I must admit I am surprised that Bubble not only use dynamic IP’s but they also haven’t thought about secure integrations with Azure and AWS yet.
Guess I will need to learn Azure API’s to get to where I need to be.
Appreciate you taking the time to respond.

simon7 · 2020-06-29T11:24:07.514Z

Hi Mat,
I think part of the reason is that they use Lambdas (among a lot of other AWS stuff), and static ip can be kind of a hassle with those. And, even if they did use a static ip, you would be open for attacks from other users on the bubble platform (lower risk, but still…).

So in my opinion the best and most secure way is having your own gateway/api in front of the database, its more work, but the solution can be used everywhere afterwards. So if you ever need to move from bubble, your database + api already exists. Ditto if you make a iOS app, a spinoff product etc. etc.

mat.lawrence · 2020-06-29T11:37:04.714Z

Cheers Simon.

anthony.lyon · 2020-12-10T20:34:55.847Z

How do you authenticate the API gateway (Azure)? I am using the same type of Infrastructure. But I do not know how to authenticate bubble user for using API gatway.
I have got three options

Basic Auth
Certificates
Active directory

I want to use Active directory, but users do not have an active directory access. Hence, I do not know how to authenticate them using active directory.

Let me know if you have any suggestions.

simon7 · 2020-12-12T06:33:28.017Z

As of now my whole app authenticates via a token. In each flow I then do authorisation. So for my api calls I always include a couple of ids (accountId, userId).
I did experiment with sending both a JWT and also a custom encryption method but am not using this right now (decided it was overkill). See this topic as well:

Any ways to handle user authentication outside of Bubble? Need help

I have actually tested out a system like that. I use a lot of external apis in AWS and looked at ways to create unique tokens on the fly to make authentication and authorization easy on the receiving end. It doesn’t fit the use case discussed here, but might be an inspiration for others. In short, wrote a small serverside plugin with crypto (vanilla-node, so no dependancies). The plugin takes whatever string or array I throw at it, and creates a encrypted token with aes-256-ctr. The tokens co…