cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Madman_WithABox
New Member

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
RezaDorrani
Dual Super User II
Dual Super User II

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

9 REPLIES 9
RezaDorrani
Dual Super User II
Dual Super User II

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

Pstork1
Dual Super User
Dual Super User

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.

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.

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.

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.

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.

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.

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.
DeeTronSEAM
Kudo Collector
Kudo Collector

Just a note for anybody who stumbles into this thread from here on out.....

 

It now (finally) is possible to use Azure AD Authentication with the SQL Server connection.  That makes three options (currently: SQL Server login, Windows (but only via onPremDataGateway ), and Azure AD).

 

PowerApps Blog post from October 21, 2019 was the preview announce but I think it's in General Availability now.

https://powerapps.microsoft.com/en-us/blog/azure-active-directory-authentication-in-the-sql-server-c... 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Carousel_PP_768x460_Wave2 (1).png

2022 Release Wave 2 Plan

Power Platform release plan for the 2022 release wave 2 describes all new features releasing from October 2022 through March 2023.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Top Solution Authors
Users online (3,094)