You may already know that I typically use the series A Fish Out of Water when I want to write something that slightly deviates from my main blog topic: Enterprise Integration. This time, it is not an Enterprise Integration topic, but it is somehow related to it, as BizTalk Server utilizes this functionality to back up databases into Azure blob storage.
While I was trying to configure the SQL Server credentials for authentication to Azure Blob Storage, I made some incorrect configurations with the credentials I was setting up. The next question I asked myself was, how can I delete this 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.
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, which are managed by SQL Server. SQL Server protects these system catalogs to ensure the integrity of the system and prevent direct modifications that could lead to inconsistencies or cause 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.
