cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Madman_WithABox
Level: Power Up

Link PowerApps to AzureSQL with ActiveDirectory Auth

I've been playing around developing some PowerApps for my company for a little while on our Development Server, but I was never able to connect to it using anything other than a plain SQL account.  They've allowed me to have an SQL account on the development server, but on the Production server I have to use something like Integrated Authentication (AD), or Windows Authentication because they don't want to have to create and manage separate accounts for every user on the server when they have something centralized/single sign-on (AD).

 

Is there a way to connect to a SQL database using an authentication other than SQL?  I know that an on-premises gateway has the "Windows Authentication" option, but since the database is in the cloud (Azure SQL) this isn't an option.  I can connect to the database with PowerBi so it seems like there should be a similar option for PowerApps.

1 ACCEPTED SOLUTION

Accepted Solutions
Dual Super User
Dual Super User

Re: Link PowerApps to AzureSQL with ActiveDirectory Auth

Hi @Madman_WithABox 

 

Based on my knowledge, only option at the moment is SQL authentication

There is work planned to provide other authentication options

 

Regards,

Reza Dorrani

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

View solution in original post

8 REPLIES 8
Dual Super User
Dual Super User

Re: Link PowerApps to AzureSQL with ActiveDirectory Auth

Hi @Madman_WithABox 

 

Based on my knowledge, only option at the moment is SQL authentication

There is work planned to provide other authentication options

 

Regards,

Reza Dorrani

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

View solution in original post

Dual Super User
Dual Super User

Re: Link PowerApps to AzureSQL with ActiveDirectory Auth

You can use Windows Authentication with an On-Premises SQL server using the ON-premises gateway.  But you can't change the authentication type when connecting to Azure SQL.  So right now it only supports SQL login users.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Madman_WithABox
Level: Power Up

Re: Link PowerApps to AzureSQL with ActiveDirectory Auth

Thanks for clearing that up.  Couldn't find a concrete "no this isn't possible" anywhere so at least now I know.  I hope this is something that really is in the works soon.  I can't be the only one who would really be in need of something like this.  I guess, for now at least, PowerApps just won't be of use to us as there's no way I can get duplicate SQL accounts for everyone who'd need to use the app when they already have AD accounts.

Dual Super User
Dual Super User

Re: Link PowerApps to AzureSQL with ActiveDirectory Auth

Why do you say you need duplicate SQL accounts?  The SQL account is embedded in the connector as a connection account.  Everyone who uses the connection uses that one account.  Or are you trying to retrieve content based on permissions in SQL?



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Madman_WithABox
Level: Power Up

Re: Link PowerApps to AzureSQL with ActiveDirectory Auth

I might be over thinking that part of it since I've not been at a point to get multiple people to test all of the apps.  You are correct that some of them will work (they work now in development) with a single account.  I do have some apps that check the username against a table to determine what type of employee is using the app and they get slightly different displays (sometimes functionality) in the app.  The more I think about it, that may still work, but I'm just covering my bases.

 

My initial question still stands to issue though because they don't want to give me an SQL account on the production server.  I'm hoping if I can get enough weight behind the need/want for these apps I might be able to persuade them to give me a single SQL account with specified non-admin permissions.

Highlighted
Dual Super User
Dual Super User

Re: Link PowerApps to AzureSQL with ActiveDirectory Auth

The bottom line is that right now the SQL connector to an Azure SQL database can only use SQL authentication.  There is no other option.  That is the account that controls the connetion.  What you do with the data inside the app based on different users is up to you. 



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Madman_WithABox
Level: Power Up

Re: Link PowerApps to AzureSQL with ActiveDirectory Auth

Yeah.  Thank you all so much for clearing that up.  I spent a lot of time scouring the web for an answer on how to do it, but it seemed like nobody had asked before if it was even possible to use other Authentication methods for Azure.

 

I certainly want to upvote for some work on getting other Authentication methods to SQL for PowerApps though.  I'm sure i'm not the only one behind a corporate wall on this kind of thing.

Dual Super User
Dual Super User

Re: Link PowerApps to AzureSQL with ActiveDirectory Auth

Just to be clear the other authentication methods are available if you are using the connector to go through an on-premises gateway.  Its only when using the connector to go directly to a SQL server, like Azure, that you are limited to SQL authentication.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 341 members 6,581 guests
Please welcome our newest community members: