connect to sql server azure vm power query

1
votes

I cannot connect to either my SQL Server instance hosted on an Azure VM or my Azure SQL Server Database using on-premise Power Query or Power BI Desktop. I CAN connect using the Excel DATA ribbon or PowerPivot but, using the same server name and credentials, cannot connect using Power Query.

I have tried the following, none of which solved my problem:

  • Added Inbound TCP rule for port 1433 to Azure VM
  • Added Outbound TCP rule for port 1433 to local machine
  • Ensured that my Azure SQL Server Database allows Windows Azure Services
  • Ensured that my Azure SQL Server Database allows all relevant IP addresses
  • Tried both DNS and IP address as server names
  • Used IP address, Port as server name
  • Ensured that the Azure SQL Server VM SQL credentials have full admin rights
  • Connect to SQL Server VM from Power BI Desktop downloaded onto the same VM

Not sure what else to try. How is it that I am able to connect through PowerPivot but not Power Query or Power BI?

I get the following error after I enter server name and credentials and hit connect: "The user was not authorised"

What am I missing?

1
sql-serverazurepowerbipowerquery

1 Answers

0
votes

It sounds you are actually logging in OK, but on connection Power Query / Power BI is doing something your user is not allowed to do. Most likely it is when Power Query scans the schema of the target server for tables, views and TVFs.

From my understanding the main difference/improvement with PQ over PP & Excel Get Data is being able to call TVFs, so I would look at your permissions in that area.

Test #1 would be to make the PQ connection as a SQL sysadmin user to confirm my suspicion (i.e. PQ will work OK).