Azure Data Sync Agent key issues

Question

Hi,

I am in the process of testing options for a fleet of SQL services that would use Azure and on-premise databases.

Having read some more into Azure Data Sync it would appear to be a useful solution to a couple of issues we are experiencing. However, I am unable to fully test this theory as I cannot get the agent to connect. I receive the following error with no additional logs available:

"Please enter a valid agent key to proceed. The key must be generated from SQL Data Sync web portal"

Scenario:
-VM with SQL 2017 and Data Sync 2 v15.2000
-Connectivity to the Azure DB from VM confirmed working, both queries and data tier export/import
-Clock on both server and database read as UTC
-SQL user in use has the relevant permissions on the relevant DBs
-Both VM and Azure SQL DB are in Australia East
-Agent key generated and tested within a minute or so, multiple times

My initial reading on this issue seems to suggest it is a time mismatch but if I query server times they match.

What am I missing? It will be something obvious no doubt.

Thanks

Answer 1

@PSavage-7455 Thank you for reaching out.

This error is usually caused by insufficient user permissions. To be able to successfully configure the local sync agent you will need these permissions:

• CONTRIBUTOR Role on Azure SQL server
• A member of DataSync_Executor role in sync metadata database
• On hub/member databases

----------

Please check and let us know if you have any further questions.
If an answer is helpful, please "Accept answer" or "Up-Vote" which might help other community members reading this thread.