Windows Authentication for SQL Server?

Hello everyone,

I’m building an application that I need to be able to read/write to an existing SQL database for a customer.

The network administrator told me

“They are setup to use windows authentication to log into SQL. So, your read/write access will depend on if the Network User you are logging in as, has rights to do so in SQL.”

I know that bubble provides a SQL database connector already. However, the connector only say Postgres, MySQL and MSSQL.

Would I be able to connect to a SQL database with windows authentication through the bubble SQL Database connector?

If not, does anyone have any advice if this is possible and the best way to do it?

I’m very new to programming so not sure if this standard or unique. I’m trying to figure out if connecting to this database will be a problem, before I commit to doing it.

Thanks!

How do you plan on distributing the app? Through a Windows app (Using a hybrid to native converter) or just a webpage?

The website won’t be able to access which user is logged in.

Yeah the bubble app wont be able to pickup the current windows user and pass that though as windows authentication. Since all of the “stuff” will be happening on bubbles servers and not on the users browsers.

SQL Server is used interchangeably with MSSQL. So normally when people say SQL server they are talking about MSSQL.

Bubble uses this kind of connection string for MSSQL mssql://username:password@my-db-instance.endpoint.us-east-1.rds.amazonaws.com:PORT/db_name so you would need an SQL user and pass created on the database server. Also something to bear in mind is that the connection is going to be coming from Bubble. So they would need to open up access to the IP ranges that bubble servers use which could make the IT admin feel a bit uneasy.

Hope that helps!

Thanks

@chrislarge305 - Thanks for the response. A few questions.

Yeah the bubble app wont be able to pickup the current windows user and pass that though as windows authentication.

Does this mean I can’t do the integration?[quote=“chrislarge305, post:3, topic:12914”]
SQL Server is used interchangeably with MSSQL. So normally when people say SQL server they are talking about MSSQL.
[/quote]

Perfect. Thank you for the clarification. [quote=“chrislarge305, post:3, topic:12914”]
Bubble uses this kind of connection string for MSSQL mssql://username:password@my-db-instance.endpoint.us-east-1.rds.amazonaws.com:PORT/db_name so you would need an SQL user and pass created on the database server. Also something to bear in mind is that the connection is going to be coming from Bubble. So they would need to open up access to the IP ranges that bubble servers use which could make the IT admin feel a bit uneasy.
[/quote]

With this statement, it sounds like you’re saying it is possible to do this integration? As long as I get a SQL username and password and if the IT folks open up the correct IP ranges?

If I can’t use the bubble SQL connector plugin, any other advice on the best way to make this integration?

Here’s an email I was given from the database administrator.


The connection info. we discussed is below. Please note that you guys are currently using Windows Authentication.

SERVER=SERVMAN2
DSN=Driver={SQL Server Native Client 10.0};Server=SERVMAN2;Database=Servman01;Trusted_Connection=yes;MARS_Connection=yes;Uid=;Pwd=
DATABASE=Servman01
USERNAME=
PW=

If they have given you some info for the USERNAME= PW= fields then it should do with a connection string. But they have given you a local DNS name of SERVMAN2. I highly doubt that there SQL server is open up on the internet and is most likely hidden behind a firewall in the network and not in the DMZ.

I think what you would need to ask them is are you happy for an external server to access your database directly. They can lock down the permissions on the user (i.e if you only need read access to certain parts of the DB). However they are more than likely going to be quite security conscious and not want to open up the server to external IPs. If that is the case then you wont be able to connect to the server. So in that case you have a couple of options. Either get a script written to push data from the SQL server to the bubble DB (this could be done with APIs) or build a web service that can be held on their systems that you can query the DB data through via an API.

Hope that helps.

Thanks

@chrislarge305 This is very useful.

It sounds like you’re saying “yes, this is possible, but there are a few conditions…”

Yes, they will give me a specific user with username and PW.

It is locally hosted. [quote=“chrislarge305, post:5, topic:12914”]
I think what you would need to ask them is are you happy for an external server to access your database directly. They can lock down the permissions on the user (i.e if you only need read access to certain parts of the DB). However they are more than likely going to be quite security conscious and not want to open up the server to external IPs. If that is the case then you wont be able to connect to the server.
[/quote]

I will ask them this. There is a high level of trust, so my guess is that they will be okay with it.

I will need to read and write to the database, so there’s added risk here as I could delete something. But I can structure it to put a copy/archive of any records that is changed, so there’s a back up.

Thanks for these ideas too!

PS - Any chance I could hire you to build this, if needed?!

It is locally hosted.

Yeah sorry there may of been a bit of confusion here. I didn’t mean that they were externally hosted but they probabaly haven’t got it open to the outside world (if they have then thats very bad!!)

I will ask them this. There is a high level of trust, so my guess is that they will be okay with it.

Thats all good, it wouldn’t be so much of a question of trust with you personally. Depending on the data that is to be accessed it could be their security policies dont allow their data to be web accessible (which a bubble app would be web accessible). But still worth the question as the data may not be super sensitive anyway as I dont know what the data is!

PS - Any chance I could hire you to build this, if needed?!

I’m very flattered but I’m a lurker and still learning bubble myself so wouldn’t be much use! I know there are quite a few bubblers here that are providing consultancy services like @levon who have made some lovely looking (and really fancy apps like a slack app thats awesome! Slackly - Team Messaging Template | Bubble)

However bear in mind one thing I have found within this community is that people love to share, its really fantastic to see! I love it and the devs / creators / owners here are so responsive as well you could probably get an answer to most of your questions :slight_smile:

This is good question, I’m not sure if it already have any connections to the internet/outside world.

This is CRM data, so very valuable and not something that would want others to get access to. I’ll ask their IT team what they can do. I’m not sure what their policies are.

Okay, I’ll reach out to @levon

The main thing I’m struggling with right now is simple the research of what I need to ask and/or understand to know if an integration is possible. So, I need to know the right questions to ask both their IT team, and also about bubble’s capabilities.

@chrislarge305

I asked my other customer and their CRM is running on SQL 2014. I asked him if it was MySQL and he said it was just SQL. Is this something that bubble can integrate with?

Hi @topherwilliams,

It will be MSSQL not MySQL which Bubble integrates with (bubble integrates with MySQL, Postgres and MSSQL (SQL Server)) see image below of the SQL connector plugin. MSSQL server is normally reffered to as just SQL server you can also be more certain when someone says SQL Server + Year name (i.e SQL Server 2014). MySQL / Postgres etc doesnt use year names as there version numbers they use typical version numbers like MySQL 5.2 or Postgres 9.6.2 etc

Hope that helps.

Thanks

Yes, it does. I was a little worried it might not be able to work. Thanks!

In general, going through an API is a preferred way of integrating, vs database access. To ensure data integrity, to update a database you’d need to learn the way the CRM uses the data, and some of that may be undocumented. Plus it may be different in the next version.

Still its often worthwhile, especially if there’s no compatible API.

Thanks. I completely agree with you, the issue is that they’re using super old software! So the only way to read/write in their database is through a database connection.

Haha I get you.

Where you have complex queries I recommend creating custom views for Bubble to read. And where you update multiple tables, stored procedures are handy to keep the transaction consistent.

@mishav Thanks for the advice…I wish I knew what that meant! Hahaha, I’m sure I’ll figure it out soon enough.

Any chance I could hire you to build this?

If you’re not in a hurry … I’m busy the next two weeks, but can fit in a couple of minutes to help work out what you need.

@chrislarge305 might be interested too? : )

I don’t mind lending a helping hand at all as it would be a good learning process I wouldn’t want to take on consultancy work at the moment as I don’t feel I know bubble well enough. But if you have any questions at all please feel free to post here and I’ll do my best :slight_smile:

Ok thank you! If I need to have it happen, it won’t be for a week or so anyway. I’ll let you know.