Unanswered

Connecting to Azure SQL Database using Entra ID authentication

(0) Share

Report

Posted on by JonathanGibbs

313

Help - I'm going wrong trying to connect to an Azure SQL Database using Entra ID authentication in a new Azure directory. (This is part of a project to improve overall cyber security...) Here's what I've done:

In a newly-provisioned Azure tenancy ['newDomain'], create a new SQL Server object ['newSQLServer'] and a new database within it ['newDB']
newSQLServer has a Server admin login and a Microsoft Entra admin ['MicrosoftEntraAdmin']
In newSQLServer | Networking, set Public network access to disabled and tick Exceptions - 'Allow Azure services and resources to access this server'
Migrate data into newDB from elsewhere (a previous VM-based SQL instance)
Create a new User ['newUser@newDomain'] who is going to own and create PowerApps apps
Give newUser a Power Apps Per User licence
Log into the server in SSMS using MicrosoftEntraAdmin
Run the script CREATE LOGIN [newUser@newDomain] FROM EXTERNAL PROVIDER (not sure if I need this or not, but it ran ok..)
Log into Office 365 - PowerApps as newUser@newDomain
Create a Connection to SQL Server with Authentication type 'Azure AD Integrated' and account newUser@newDomain
Create a test canvas app from data in the default environment using SQL Server, server name newSQLServer and database name newDB. Get error '..server was not found or was not accessible..'
Try again with Server name 'newSQLServer.database.windows.net'. This time I get 'primaryRuntimeUrl is required'

I'm missing something somewhere along the line, or just misnaming the server perhaps - where am I going wrong, please?!

Categories:

Governance & administering

I have the same question (0)

All responses (5)

Answers (1)

Verified answer

JonathanGibbs 313 on at

Like (0)

Report

Answering myself - I think the problem is that newUser@newDomain does not have a read/write role for the database. When I assign the PowerApps licence to MicrosoftEntraAdmin instead (which has full admin rights over the database) and create an app as MicrosoftEntraAdmin, it works as expected.
Now just need to find how to assign newUser to the appropriate roles..

Was this reply helpful? Yes No
chiflado8 2 on at

Like (0)

Report

Hello @JonathanGibbs, I have a similar issue (error message 'primaryRuntimeUrl is required') when trying to connect to our SQL Database. Were you able to solve your problem by giving the user read/write privileges? Thanks.

Was this reply helpful? Yes No
JonathanGibbs 313 on at

Like (0)

Report

Hi @chiflado8
Yes, sorting out privileges worked
I found I had to do the following in SQL:
In Master database
CREATE LOGIN [xxx@yyy] FROM EXTERNAL PROVIDER
CREATE USER [xxx@yyy] FROM LOGIN [xxx@yyy]
In the database to be used
CREATE USER [xxx@yyy] FROM LOGIN [xxx@yyy]
ALTER ROLE db_datareader ADD MEMBER [xxx@yyy]
ALTER ROLE db_datawriter ADD MEMBER [xxx@yyy]
ALTER ROLE exec_access ADD MEMBER [xxx@yyy]
Your roles may be different, of course, but the point is you need to work in both Master and the db you want to give access to when using Azure SQL Database

Was this reply helpful? Yes No
PK-27081136-0 Microsoft Employee on at

Like (0)

Report

Hi @JonathanGibbs ,

We made SQL connection using an account by making the account as "Entra Admin". We are facing issue if any other user is trying to access the same PowerApps. The SQL connection is failing to connect to the DB. Error Screenshot is attached. Please let us know if you know any solutions for this.

error entra id.jpg

Was this reply helpful? Yes No
JonathanGibbs 313 on at

Like (0)

Report

Hi @PRATHEEK
First thought is, do your other users have the necessary premium PowerApps licences (used to be 'Power Apps Per User' or 'Power Apps Per App' but they've changed them recently)?

Was this reply helpful? Yes No