Tuesday, July 5, 2011

BizTalk backups to network share

Over the last few months, as well as doing actual development work, I've been assisting a client build and configure a set of BizTalk environments to cater for everything from development, system integration testing, user acceptance testing, training, pre-production, production and disaster recovery.

One of the final steps in our build for certain environments has been to configure the BizTalk backup SQL job to regularly backup the BizTalk databases to a network share. Not only is this good practice, but it's a mandatory part of setting up BizTalk log shipping as part of a DR capability.

We created a hidden ($) network share, and assigned "Full Control" permissions to a specially-created "BizTalk_Backups" Active Directory group - both at the share level and the filesystem level. We then placed the service account used to execute the SQL job in this group.

However, when it came to executing the BizTalk backup job, we encountered an "Access denied" type error: "BackupDiskFile::CreateMedia: Backup device '...' failed to create. Operating system error 5(failed to retrieve text for this error. Reason: 15105)."

We double-checked the permissions we'd configured, re-created the share not hidden, even checked whether the same service account could write to a different share on the same server... Nothing succeeded.

What did work however was adding the "Everyone" group with "Full Control" permissions on the share and filesystem... but hang on, the SQL service account was a member of the "BizTalk_Backups" group which already had "Full Control" permissions to the share etc... Hmmm... So, we removed "Everyone", and explicitly added the SQL service account with "Full Control" permissions to the share and filesystem... and it worked!

We're still not sure why exactly, but it seems as though the account needed to be added explicitly, rather than via membership in a group... well, other than the "Everyone" group... So problem solved, but a mystery nonetheless. Interested to hear if anyone else has had a similar experience.

UPDATE: I was speaking recently with a colleague of mine who suggested the issue may have been a result of not having restarted the SQL Server and SQL Agent services after we'd added the SQL service account to the "BizTalk_Backups" group. These services may have been caching the group membership of the SQL service account - and a restart of the service may have caused this to be refreshed. I haven't had a chance to check this out, but it sounds plausible.

No comments:

Post a Comment