Does the Azure SQL Data Sync Agent require specific permissions on the On-Prem database?

Question

I am setting up Azure SQL Data Sync - I have an Azure SQL SyncDB for the metadata and 2 member databases - one is Azure SQL and the other is an on-prem SQL DB (express). The agent is running correctly (test connection succeeded), and I am able to see my database and tables in the Sync Group console. However, when I initiate the sync, I get 2 errors:

In reference to the on-prem DB:

Database provisioning failed with the exception "SqlException Error Code: -2146232060 - SqlError Number:15247, Message: User does not have permission to perform this action. SqlError Number:2759, Message: CREATE SCHEMA failed due to previous errors.  For more information, provide tracing ID ‘28170ee7-5dfe-4a7b-a7d2-75618086f6ba’ to customer support."

In reference to the SyncDB:

Database provisioning failed with the exception "Cannot insert the value NULL into column 'provision_timestamp', table 'StoreLiveSyncDB.DataSync.provision_marker_dss'; column does not allow nulls. INSERT fails.The statement has been terminated.Inner exception: SqlException ID: 37fdc491-3c1b-4339-a81a-0a63fc793330, Error Code: -2146232060 - SqlError Number:515, Message: Cannot insert the value NULL into column 'provision_timestamp', table 'StoreLiveSyncDB.DataSync.provision_marker_dss'; column does not allow nulls. INSERT fails. SqlError Number:3621, Message: SQL error with code 3621  For more information, provide tracing ID ‘e5db3144-f8c9-45c3-a8e1-41a61c8a8279’ to customer support."

I only have SELECT privileges on the on-prem.

I have full privileges on the SyncDB.

I need to resolve these two issues, and time is of the essence (isn't it always!).

Answer 1

Well, it is quite clear from the error messages that SELECT permission on the on-prem DB is not enough. I have not worked with Azure Data Sync myself, so I don't know exactly what permissions that are needed, but it seems like a good idea to check the documentation. (Which I don't have the time to do myself right now, as I am only posting a quick answer.)

As for the other message, I don't know, but possibly it is a consequence of the first.