I was trying to add a new host to a new BizTalk server and got this error first;
“failed to create SQL login and grant SQL privilege configurations on the Management database server “SQL07DEV” / database name “BizTalkMgmtDb” while creating BizTalkHost “test”
with the following additional information:
“This operation requires granting or revoking of SQL database level user access. Ensure that you have sufficient SQL permission to perform this operation. You must either be: 1) a member of the ‘sysadmin’ SQL fixed server role; or a member of the ‘db_owner’ or db_ddladmin’ SQL fixed database role. Contact your SQL database administrators for further assistance”
So I contacted our DBA. I was in the BizTalk Administrators group and in the SSOAdministrators group on that SQL server. He granted the BizTalkAdminstrators group db_ddladmin on the database above.
I tried to create a host and got a different error. This time I needed db_accessadmin to the same database. This role was granted and then I got the similar error but on the BizTalkDTAdb database.
After granting these roles on all the BizTalk database I got the error in the title namely;
“Failed to update MessageBox server will creating BizTalk Host. Couldn’t create SQL login or update SQL roles for BizTalk Host Windows group “HOME\BizTalk Host User Group” to access the MessageBox server “SQL07DEV\BizTalkMsgBoxDb”. The role BTS_test_USERS’ does not exist in the current database. Cannot find the user ‘BTS_test_USERS’, because it does not exist or you do not have permission….”
What now? This time there are no instructions on what to do. After looking up http://msdn.microsoft.com/en-us/library/aa559845(BTS.10).aspx I discovered we had done all the right things namely;
- User rights granted to BizTalk Server Administrators
- securityadmin SQL Server role on all SQL Servers
- db_securityadmin and db_accessadmin SQL Server Database roles in the BizTalk Tracking, Rule Engine, BizTalk Management, BAM Primary Import and BizTalk MessageBox databases
- db_ddladmin SQL Server Database role on all BizTalk MessageBox databases
- SSO Affiliate administrators
I have reached this stage many times with other DBA’s and at this stage they have tossed in the towel and granted my login ‘sysadmin’ rights while I created the BizTalk hosts. This DBA was different he started to dig deeper.
He discovered that i could not create a new role because the role cannot be owned by the group that I was in. He solved the problem by adding my login a user of the SQL server. Now when i try to create a host there a no errors and the host is created. If you now look at the roles you find that they are owned by my login. The owner was then changed to a more suitable SQL user.
