We got a new issue when our customer found provisioning a Sync Group named 'XXX' failed with error: Database re-provisioning failed with the exception 'SqlException ID: XX-900f-42a5-9852-XXX, Error Code: -2146232060 - SqlError Number:229, Message: SQL error with code 229.'. Following I would like to share some details what is the error and the solution to fix it.
Let’s break this down:
- Sync Group Issue: The Sync Group 'XXX' is experiencing a problem.
- Database re-provisioning failed: The attempt to reset or reprovision the database in this group failed.
- SqlException ID: A unique identifier associated with this particular SQL exception.
- Error Code -2146232060: The error code associated with this exception.
- SqlError Number 229: This points to the error number from the SQL Server. In SQL Server, error 229 is related to a "Permission Denied" error.
Root Cause
The SqlError Number 229, “Permission Denied,” is the most telling part of the error message. It means that the process trying to perform the action doesn’t have adequate permissions to carry out its task.
In the context of Sync Groups, several operations occur behind the scenes to ensure data is kept consistent across all nodes. These operations include accessing metadata tables, system-created tracking tables, and executing certain stored procedures. If any part of this chain lacks the necessary permissions, the entire sync process could fail.
Solution
The error was ultimately resolved by granting SELECT, INSERT, UPDATE, and DELETE permissions on sync metadata and system-created tracking tables. Moreover, EXECUTE permission was granted on stored procedures created by the service.
Here's a more detailed breakdown:
-
SELECT, INSERT, UPDATE, and DELETE Permissions: These CRUD (Create, Read, Update, Delete) permissions ensure that all basic operations can be performed on the relevant tables. Without these, data synchronization is impossible, as the system can't read from the source, update the destination, or handle discrepancies.
-
EXECUTE Permission on Stored Procedures: Stored procedures are sets of precompiled queries that might be executed during the sync process. Without permission to execute these procedures, the sync process might be hindered.
Conclusion
Errors like the "SqlException ID" are more than just roadblocks; they're opportunities for us to delve deep into our systems, understand their intricacies, and make them more robust. By understanding permissions and ensuring that all processes have the access they need, we can create a more seamless and error-free synchronization experience. Always remember to regularly audit permissions, especially after updates or system changes, to prevent such issues in the future
If you need more information how DataSync works at database level, enabling SQL Profiler (using, for, example, the plugin of SQL Profiler in Azure Data Studio) for you could see a lot of internal details.
Enjoy!
