SPFx and SQL in Azure

[Pieter Veenstra]

Are there any examples available of SPFx using a SQL Azure database?

[#2 · Accepted solution]

Since there's no native SQL connectivity from JavaScript, which makes sense (no way to hide connection details), you'd be looking to have a service, which provides the needed access. This could be implemented as WebAPI or as Node.js application hosted in Azure.

You'd secure the call from SPFx to WebAPI using Azure capabilities and then provide needed operations towards your SQL Azure database (or any other service) from the WebAPI.

See following for additional details on how to secure the traffic between SPFx solution and WebAPI - https://dev.office.com/sharepoint/docs/spfx/web-parts/guidance/connect-to-api-secured-with-aad. 

[#3]

thanks a lot @Vesa Juvonen

The link in your response got corrupted by the full stop. For completeness the right link:

https://dev.office.com/sharepoint/docs/spfx/web-parts/guidance/connect-to-api-secured-with-aad

[#4]

Hi @Vesa Juvonen,

That all seems to fit the work that we are investigating our options for.

One more question though ....

In our case we have an Office 365 tenant (running an SPFx web part). This web part will talk to a database through an API (preferably through the iframe option so that we don't get extra login dialogs). This all matches 100% your setup.

Now the extra complexity. We also have users that are not allowed to have any kind of access to the Azure API/SQL Database. These users are to login in a separate office 365 tenant and will use an SPFx web part to talk to an API in Azure whcih in its turn will do database updates. 

I've got the feeling that these second set of users will need some authentication sorted out. Is the best way now to create a second Azure environment and make this aware of my users that aren't allowed to access my first Azure environment? Or am I missing an easy option here?

[#5]

Hey Peter, 

Just wondering how this worked out for you, and if you had any lessons learned you could share on this. I am heading down this path myself.

Also, did you consider using BCS to connect to sql azure?

[#6]

@Vesa Juvonen 

What about SPFx web part that Posts and Gets to and from Cosmos DB?

They have an express example that we can probably use to create the connection but if someone already did something similar, it will be easier.

Thanks