none
Azure VM SQL Server connection from PowerBI

    Question

  • I have some issues with connecting to a datasource on an Azure VM

     from Office365. I have uploaded a PowerPivot workbook to PowerBI, and setup the gateway successfully from the Azure VM to PowerBI. I copied the connection string from the PowerPivot workbook, and tried to create a new data source in PowerBI ,but I get an error referring to the gateway when trying to enter credentials.

    The error meesage says something like "Failed to verify gateway status" The remote name could not be resolved:…..

     

    I am suspecting the server name to be wrong, but what should the connection string look like ?


    dakine

    Wednesday, May 28, 2014 3:43 PM

Answers

  • dakine,

    1. Currently the gateway could only run from an on-premises machine, Azure VM is not supported.

    2. However if your data model only connect to SQL Azure, then you don't need a gateway to refresh your workbook, you don't even need to create the data source from power bi admin center.

    If that does not anwser your question, please share more information about where is your database, on SQL Azure, or Azure VM?


    Thursday, May 29, 2014 1:54 PM
  • dakine,

    The normal process to setup a schedule work book refresh is somthing like below:

    1. upload your workbook to sharepoint online, and config the schedule refresh.

    2. Copy the connection string from your power pivot.

    3. Go to Power BI Admin Center (where you tried to create a data source), and use the connection string you got from step 2.

    Here you need to specify a gateway that serves the refresh, and you need enter the credential (username/password) of you SQL Server so gateway can access your DB.

    And you need to do the step 3 from within your Azure VM (where your gateway is installed), otherwise you will likey to hit the issue "Failed to verify gateway status"

    Wednesday, June 04, 2014 12:26 AM

All replies

  • dakine,

    1. Currently the gateway could only run from an on-premises machine, Azure VM is not supported.

    2. However if your data model only connect to SQL Azure, then you don't need a gateway to refresh your workbook, you don't even need to create the data source from power bi admin center.

    If that does not anwser your question, please share more information about where is your database, on SQL Azure, or Azure VM?


    Thursday, May 29, 2014 1:54 PM
  • Thank you for your answer.

    My datasource is an Azure VM  (SQL Server is installed on the VM).
    This was bad news - i would assume this was a key feature of MS cloud offering ? Seems to me Azure is way to immature too be an enterprise offering yet.

    I did successfully configure the gateway, though - I'm just not having success connecting to the data source from PowerBI.

    Guess I need to go back to an on-premise server :(


    dakine

    Friday, May 30, 2014 8:14 AM
  • Hi,

    Refreshing from a sql in Azure VM actually should work - there is no difference between sql in VM or sql on-prem to the gateway. Can you please:

    1. Verify the status of the gateway is ok in the configuration manager.
    2. Ensure the sql in azure VM can be accessed from the vm containing the gateway.
    3. Remote to the vm containing the gateway, open admin center and provide credential.

    The last step in important as we want to ensure user stays in the same network with the gateway to provide credentials for security consideration.

    Samuel

    Monday, June 02, 2014 6:48 AM
  • Hi.

    1. The gateway is ok.
    2. I'm not entirely sure what you mean here ? There is no problem connecting to the SQL Server on the VM when logged onto the VM. I have also managed to connect to the SQL Server on the VM from my local computer (local SSMS).
    3. What admin center, and what credentials are you refering to ?


    dakine

    Tuesday, June 03, 2014 8:42 AM
  • dakine,

    The normal process to setup a schedule work book refresh is somthing like below:

    1. upload your workbook to sharepoint online, and config the schedule refresh.

    2. Copy the connection string from your power pivot.

    3. Go to Power BI Admin Center (where you tried to create a data source), and use the connection string you got from step 2.

    Here you need to specify a gateway that serves the refresh, and you need enter the credential (username/password) of you SQL Server so gateway can access your DB.

    And you need to do the step 3 from within your Azure VM (where your gateway is installed), otherwise you will likey to hit the issue "Failed to verify gateway status"

    Wednesday, June 04, 2014 12:26 AM
  • Great - when I did it from within the Azure VM it worked!

    Still have one issue though. It failed the first time due to a chart based on PowerPivot data. When that was removed it worked fine.

    How can it be I am not able to have a chart in my workbook ?


    dakine

    Thursday, June 05, 2014 9:08 PM
  • Can you share the error message, so we can further investigate the root cause?
    Sunday, June 08, 2014 11:23 PM
  • We were unable to refresh one or more data connections in this workbook. The following connections failed to refresh: ThisWorkbookDataModel

    I should add that I created the report in Excel 2010 - then opened it (and converted it) in Excel 2013 before uploading the document.


    dakine

    Tuesday, June 10, 2014 11:00 AM
  • Sorry to ask again, did you get  a Correlation ID besides the error message?

    With a Correlation ID, we then be able to get much more details of what went wrong during refresh.

    Wednesday, June 11, 2014 12:15 PM
  • 148f16b4-bfbe-4ded-be51-ed54eab710cb


    dakine


    • Edited by dakine81 Thursday, June 12, 2014 10:44 AM
    Thursday, June 12, 2014 10:43 AM
  • Did the Correlation ID above make it anything clearer ?

    dakine

    Wednesday, June 18, 2014 9:43 AM
  • Hi Dakine,

    Just to let you know we're still looking into this, sorry for the wait.

    Samuel

    Monday, June 23, 2014 6:05 AM
  • Hi Dakine,

    Sorry for the delayed response.

    Our engineer investigated the failure, and found that it failed to refresh some measures in your workbook.

    The measure are likely to be [Total of SpeedAvg] and [Sum of SpeedAvg]. Please check your workbook, make sure they are still working.

    In the meantime, we are also looking if there any improvment we could do on the service side.

    Thursday, July 10, 2014 2:14 AM