Connecting Power BI with an Azure SQL Server Database

[SamFosterSCL]

Hello,

I am currently trying to connect an Azure SQL Server Database to Power BI.

I have put in the correct server name and I am using the log in details for the DB however it is not connecting.

Any Help? Am I using the correct credentials, are there other credentials I should be using?

[Anonymous]

Have you modified the Azure SQL Db firewall settings to allow connections from your pc to the database?

(Have you tried connectiong to the db with Managment Studio for instance?)

When you first start designing a report on PowerBI Desktop you need to establish the connection from your pc to Azure SQL DB.

You also need to set the firewall settings to allow access to Azure services.

After that your report should connect just fine.

[SamFosterSCL]

I have connected fine to Management Studio, just wondering why Power BI wont work.

[v-yuezhe-msft]

Hi @SamFosterSCL,

I am able to connect to Azure SQL database in the latest version of Power BI Desktop(2.42.4611.901). What error message do you get when you connect to  your Azure SQL database?

In your scenario, if you have ever connected to Azure SQL database in Power BI Desktop, I would recommend you go to “File –> Options and Settings –> Data Source Settings”, under “Global permissions”, select any old Azure SQL data source connections and click on clear permission. Then re-connect to Azure SQL database from Desktop,  also enter database name during the connection.

Thanks,
Lydia Zhang

[SamFosterSCL]

Hi @v-yuezhe-msft,

I get the error message 'User Not Authorised'. I am putting in the credentials of the database.

[v-yuezhe-msft]

Hi @SamFosterSCL,

Could you please post a screenshot about your sceanrio? And do you enter a SQL login and password?

In addition, do you use latest verison of Power BI Desktop? And have you deleted old Azure SQL data sources?

Thanks,
Lydia Zhang

[SamFosterSCL]

It asks for either a Database log in or a Windows one.

I get the error 'User Not Authorised'

[v-yuezhe-msft]

Hi @SamFosterSCL,

Could you please post a screenshot about your sceanrio? And do you enter a SQL login and password?

Thanks,
Lydia Zhang

[SamFosterSCL]

This is the error message I receive when logging into the Azure DB with the correct DB details.

[v-yuezhe-msft · Accepted solution]

Hi @SamFosterSCL,

Do you use latest verison of Power BI Desktop? In your scenario, do you install SSMS on the same machine that has Power BI Desktop installed? If not, except the “allow access to Azure services” setting, please ensure that you have set up a firewall rule to allow Power BI Desktop to make a connection to Azure SQL.
 

Thanks,
Lydia Zhang

[SamFosterSCL]

Hi,

I needed to let my firewall in azure accept the VM's IP Address I was on.

Thanks for all the help!