As you may already know, I use the A Fish Out of Water series when I want to write about topics that slightly deviate from my main focus: Enterprise Integration. This time, the subject is not purely about integration. However, it is still related, since BizTalk Server uses this functionality to back up databases to Azure Blob Storage.
While configuring SQL Server credentials for Azure Blob Storage authentication, I made a few mistakes during the setup. That led me to a simple but important question: how can I remove an incorrect credential from the sys.credentials table?
My first reason was to go to that table and manually delete that row; it’s easy! But no, because I got the error:
No rows were deleted.
A problem occurred attempting to delete row 1.
Error Source: .Net SqlClient Data Provider.
Error Message: Ad hoc updates to system catalogs are not allowed.
📝 One-Minute Brief
Managing credentials in SQL Server can be tricky, especially when dealing with orphaned or misconfigured entries. This post explains how to identify and safely delete credentials from the sys.credentials table, helping administrators resolve authentication issues and configuration conflicts.
Cause
The error “Ad hoc updates to system catalogs are not allowed” occurs when you try to directly modify or delete entries in system views or tables, such as sys.credentials those managed by SQL Server. SQL Server protects these system catalogs to ensure system integrity and prevent direct modifications that could lead to inconsistencies or break the system to break.
Solution
To delete a credential in SQL Server, you should not modify system catalogs directly. Instead, you should use the DROP CREDENTIAL statement, which is designed to remove credentials in a safe and supported way.
Here’s the correct syntax to delete a credential:
DROP CREDENTIAL [credential_name];
In my case, for this BizTalk Server context, it would be something like:
DROP CREDENTIAL [https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>];
Hope you find this helpful! So, if you liked the content or found it helpful and want to help me write more, you can consider buying (or helping to buy) my son a Star Wars Lego set.
