Common Azure Synapse Link for SQL storage permission issues

Reading Time: 4 minutes

In this post I want to cover common Azure Synapse Link for SQL storage permission issues. Since I helped a fellow MVP out with this recently.

To be more precise, I want to show how you can fix one of the most common issues I tend to encounter with Azure Synapse Link for SQL. Which is access to the Data Lake Storage Gen2 account.

I have encountered issues like this a few times now. For example, when I was performing my file tests for Azure Synapse Link for SQL Server 2022.

Symptoms of storage permissions issue

I tend to check if there is a problem accessing storage by going through the below steps.

1. I check the monitor hub and see the replication has not started.
2. In Azure Data Lake storage no new files or folders have been created.
3. I check SQL Server and change feed has been started (usually I do not do this).

In reality, you must be patient when testing Azure Synapse Link for SQL. Because it can take a while for it to start and for files to appear in your Azure Data Lake storage container.

When it is working properly you should have a folder appear that has the same value as the continuous run ID value for the link. Which you can find in the Link connections section within the Monitor hub.

If the folder does not appear it is probably due to a permissions issue. You can try a couple of different things to resolve this.

Before doing anything though I do recommend stopping the link gracefully in Synapse Studio. That way you avoid further issues at the database end.

Once it has stopped gracefully you can try one of the below methods.

Common Azure Synapse Link for SQL storage permission issue for existing links

If your link worked before you can try refreshing the access token used for it. I find this tends to be an issue if you have not used your link for a while. Which is exactly what happened when I was performing my file tests for Azure Synapse Link for SQL Server 2022.

Make sure you publish back up to the service afterwards before you start the link again. If you do not get asked to publish I recommend doing something small so that Synapse Studio prompts you to publish. For example, a small change to a SQL script.

For brand new links for Azure Synapse Link for SQL

If you are looking into issues with a brand new link, the first thing to do is to go into the Integrate hub and select the link that you created. From there check your link properties on the right-hand side of the screen.

If you are unable to see all the link properties on the right hand side you probably need to zoom out in your browser. One quick way to do this is by pressing CTRL and the plus (+) button.

Check that you have the right location selected for the landing zone. If you need to change anything I highly recommend that you select the Rotate token option and then publish your changes.

Once you are happy with your settings you can restart your link. If a new folder does not appear in your container with the same value as your continuous run ID I strongly recommend creating a new linked service and using that one instead.

Linked service for Azure Synapse Link for SQL advice

When creating a new linked service for your Azure Data Lake Storage Gen2 account follow the original instructions from Microsoft fully first. Afterwards, go through the steps for Synapse Studio again to create the Azure Synapse Link connection.

However, if your link still does not work you can try changing the authentication of the linked service. So that it uses an Authentication type of Account key instead.

If that works than the issue is probably due to the Managed Identity permissions you configured for your Data Lake Storage account. Which you need to resolve.

Of course, if the link is running and it is for a quick demo or test you can leave it as it is. Just make sure it is configured correctly before using it in a production environment.

Final words about Common Azure Synapse Link for SQL storage permission issues

I hope this post about common Azure Synapse Link for SQL storage permission issues helps you resolve some of the issues that I have encountered. If you use any of this material in this post elsewhere I would appreciate it if you credit the source.

If you are interested in finding out how you can update tables that are part of your link gracefully you can watch last months video Azure Synapse Analytics and Microsoft MVP video series.

Of course, if you have any comments or queries about this post feel free to reach out to me.