CODEWITHSUNDEEP
powerbiazure-sql-database
Veena
Veena

Reputation: 11

Power BI using SQL Credential for Azure SQL for direct query, cannot disable local auth for the SQL Server

I am using SQL authentication from Power BI for Direct query. To remove all SQL authentications. What is the best way to authenticate my database from PowerBI? Note: the Azure SQL Server is in PME tenant.

I tried Microsoft authentication for my credentials but the *microsoft.com is not recognized in AAD for PME and does not allow me to add this user to DB to assign any role.

I am expecting a solution where Power BI reports can access an Azure SQL database in PME tenant without using SQL credentials.

Upvotes: 0

Views: 67

Answers (1)

Pratik Lad
Pratik Lad

Reputation: 8301

You can authenticate your Power BI reports to access an Azure SQL database in a PME tenant without using SQL credentials by utilizing Microsoft Entra Id (Azure Active Directory) authentication.

  • Check if your user exists in PME tenant. If it is not there you can Add your User or user from different tenant as a guest user.

Using Microsoft Entra admin center and you can follow this MS document.

enter image description here

  • Set your user as Azure Active Directory admin for your Azure SQL Server. Go to Your Azure SQL Server >> Active Directory admin >> set the AAD admin if it's not already set.
  • If you have already Azure Active Directory admin set for your Azure SQL Server create a contained database user for the AAD user and assign necessary roles:
CREATE USER [[email protected]] FROM EXTERNAL PROVIDER;

EXEC sp_addrolemember 'db_datareader', '[email protected]'; 
EXEC sp_addrolemember 'db_datawriter', '[email protected]';

Now, In Power BI while connecting from authentication method, select Microsoft Account and sign in with the AAD credentials you added to the database or the AAD admin which you set in the database.

Upvotes: 0

Related Questions