BES SQL Permissions Explained - Part 3

In this post we will discuss the necessary SQL permissions to allow administrative access to the BES database, as well as the new role based administration features in 4.1.

First, let's create a new SQL login named 'bbadmin' with no server roles or database access or database roles.


Now, what happens when we try to use this login and password from a remote BlackBerry Manager console?

[20000] (04/16 11:50:20.563):{0x150} COM Error 0x80004005 in ConnectionItem::ConnectToDB() - Cannot open database requested in login 'BESMgmt'. Login fails. - Unspecified error (connection string- -Provider=SQLOLEDB;Server=SQLTEST000;Database=BESMgmt;uid=bbadmin;pwd=)

So now let's give Permit access to the BES database.


And once again...

[20000] (04/16 11:54:34.257):{0x4E0} [ODBCRecord::DoGetFirstValue] SQL error: [0x80040E09 SELECT permission denied on object 'ObjectDefn', database 'BESMgmt', owner 'dbo'.] Source: [Microsoft OLE DB Provider for SQL Server] SQL State: [42000] NativeError: [229]

Same error popup, but the logs tell a different story. We get into the database but then have permissions issues when trying to do SQL calls. Per the RIM documentation, administrators also need the 'rim_db_bes_server' database role in order to do their work. So let's follow their advice and add that role to the account:

And voila, we are in!


Ummm, or are we? Everything is empty here. Not much to manage! Interesting that we have 'Unknown Authority' listed at the top. Although the 'rim_db_bes_server' role was sufficient in v4.0 to manage the server, apparently things have changed in 4.1.

New database roles were added in order to avoid giving full access to anyone that needed to do anything on the server. Let's take a look at them:


These are all the roles, the ones that have been added in 4.1 all start with 'rim_db_admin...'. We can also ignore the 'audit' roles which are read-only and for training purposes only, which leaves us with:

rim_db_admin_jr_helpdesk
rim_db_admin_sr_helpdesk
rim_db_admin_handheld
rim_db_admin_enterprise
rim_db_admin_security

Let's first remove our legacy 'rim_db_bes_server' role, then add the 'rim_db_admin_jr_helpdesk' role and see what happens.


Excellent, we have access now, and it even shows our authority level correctly at the top. Let's move up to the 'rim_db_admin_sr_helpdesk' role and see if there is a difference.


Hmmm, the only obvious change is in the title bar, which reflects our promotion to Sr. Helpdesk. Maybe there are some tasks now available in the menus, but nothing obvious. Let's move up to the 'rim_db_admin_handheld' role.


Now we are getting somewhere, I can see a whole new tab 'Software Configurations' available. Perhaps there are other subtasks available too. Let's go ahead and switch to the 'rim_db_admin_enterprise' role.


The only difference now appears to be the addition of the MDS service under the 'Servers' leaf of the Explorer View tree on the left. Perhaps we are now able to manage MDS with this new authority. How about we now go to the ultimate, 'rim_db_admin_security'?


Wow, now I can see everything plus the Role Administration tab. I guess now I can add people and assign/remove roles without having to use the SQL administration tools, let's try it out! Why don't we first list the administrators that are in the 'rim_db_admin_security' role, it should just be ourselves, the 'bbadmin' account.

Uh oh. I thought we had the top level of access, what is going on here? I can't even list the members of a role? Something is messed up.

Oh, wait. I seem to recall RIM saying that System Administrator (sysadmin) access on the SQL server is needed to perform role-based administration. Ummm, well that is the highest level of access available on the SQL server, so I am sure the SQL team is going to be pretty uncomfortable with that, but it makes sense that we would need this access in order to manage SQL logins and database roles through the front end of the BlackBerry Manager, which is really what we are doing after all. So let's go ahead and add 'sysadmin' server role to this account and try again.


OK, so we have full access now, at the expense of an angry SQL admin team. I guess I can forgo role based admin via BlackBerry manager, and have the SQL guys remove the 'sysadmin' server role. That just means I will have to work with them to add logins & roles in the future, so I will be sure to get on their good side!