question

ecuadrafoy avatar image
0 Votes"
ecuadrafoy asked ·

Power Query can't connect to Azure SQL resources

I talked about this with support previously and we weren't able to find a solution to this issue. Essentially, I'm trying to connect my Azure SQL tables to power query directly but it gives me an error every time I supply my password and username.

73924-image.png


73925-image.png


73934-image.png


azure-data-factoryazure-sql-database
image.png (94.6 KiB)
image.png (27.4 KiB)
image.png (52.4 KiB)
· 4
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ecuadrafoy ,

Welcome to Microsoft Q&A forum and thanks for reaching out.

It looks like an issue with authentication type you have used. Currently below are the supported data sources and authentication types by Power Query activity.

73961-image.png


Could you please please try creating Azure SQL dataset/Azure SQL linked service with SQL authentication first and then try to use it in your Power Query activity?

I just tried and was able to connect to Azure SQL source.

73900-image.png

73866-image.png


Helpful doc: https://docs.microsoft.com/azure/data-factory/wrangling-overview#supported-sources

Hope this helps. Let us know how it goes.



Thank you
Please do consider to click on "Accept Answer" and "Upvote" on the post that helps you, as it can be beneficial to other community members.


0 Votes 0 ·
image.png (24.9 KiB)
image.png (71.0 KiB)
image.png (55.8 KiB)

I verified that I had a linked service with SQL authentification and I still get the same error. I'm attaching screenshots to show you the steps I followed.

73928-image.png

73888-image.png
73940-image.png




If I click on the Configure Connection button I get the same error as in my first post

0 Votes 0 ·
image.png (63.8 KiB)
image.png (22.0 KiB)
image.png (115.3 KiB)

@ecuadrafoy Thanks for your response and sorry for your experience. Could you please tell us the region of your ADF as well as your Azure SQL ? And you have mentioned that you have already reached out to support team, could you please share the SR# here so that we could check with the team internally.

Thank you


1 Vote 1 ·
ecuadrafoy avatar image ecuadrafoy KranthiPakala-MSFT ·

I didn't register the SR number for the chat but I remember emailing Azfretrl@microsoft.com when I showed screenshots of my issue. I have realized that my SQL database is located in Canada Central and the Azure Data Factory resource in Canada East. Could that cause problems?

0 Votes 0 ·

1 Answer

KranthiPakala-MSFT avatar image
0 Votes"
KranthiPakala-MSFT answered ·

@ecuadrafoy : I have checked with internal team and confirmed that the dataset used in the PQ activity has to be an ADF dataset. We cannot access datasets in PQ from ADF. Embedding data connectivity code in the M script won't work. It is recommended to remove the M script and build the mashup in the ADF PQ editor by adding a new source in the UI that is an ADF dataset.

Let me know how it goes.

Thank you

· 2 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I just tested creating a new instance of ADF in the same location as my SQL servers and now they can communicate with each other. Thank you for your help

0 Votes 0 ·

@ecuadrafoy Glad to know that the issue was resolved. Thanks for confirming. Please do consider to click on "Accept Answer" and "Upvote" on the post that helps you, as it can be beneficial to other community members.

Have a good rest of the day! :)



0 Votes 0 ·