BES SQL Permissions Explained - Part 2

In the last post we discussed the SQL Server roles required to create and configure the BES database on the SQL server. In this post we will discuss the database permissions required by the login the BES server uses.

First let's go back to our DbaMgr2k console and pull up the properties of the bbdbowner login, which was the SQL login we used to create the BESMgmt database. Now take a look at the Database access tab:



We can deduce a couple of things from this dialog:

1) Our SQL login 'bbdbowner' has 'Permit' checked for access to the BESMgmt database.

2) Our SQL login 'bbdbowner' is associated with the auto-created 'dbo' user of this database. I still don't understand why you need separate database accounts linked to SQL logins, but hey, I'm a newbie at this.

3) Our SQL login 'bbdbowner' has the 'public' database role assigned. This is a built-in SQL role allowing basic database access.

4) Our SQL login 'bbdbowner' has the 'db_owner' database role assigned. This is a built-in SQL role allowing all operations on the database. I guess we were assigned this role since we created the database.

Now in most installations the same SQL login is used to:

a) Initially create the database
b) Authenticate BES servers
c) Authenticate BES admins via the BlackBerry Manager console

In order to clarify just what roles require what permissions, we will break out these separate functions into separate SQL logins. We already have the 'bbdbowner' login which corresponds to a) above, the initial creation of the database.

Let's now create a login 'bbserver' and see what permissions it needs to allow a BES server to operate. First we will give it no permissions at all:


Then we will use the BlackBerry Server Configuration panel to change the SQL authentication from the 'bbdbowner' account to the new 'bbserver' account:

Note: Using the above utility only changes the SQL login credentials used by the BlackBerry Windows services, which are located in:

HKEY_CURRENT_USER\Software\Research In Motion\BlackBerry Enterprise Server\Database

To change the SQL login used by the Domino BES task we need to also modify the 'Login' and 'Password' values located in:

HKEY_USERS\.DEFAULT\Software\Research In Motion\BlackBerry Enterprise Server\Database

And here is the result when we click the 'Test SQL Server Connection' button:


OK, I guess we need to add some permissions. Let's start with at least adding 'Permit' access to the BESMgmt database...



...and try again:


OK, now that's a little better. Let's try to start some BES services now and see what happens:


Oops, I am getting an error code 5608 when trying to start this service. Interestingly, some other services started without error. They must not need access to the SQL database. Let's take a look:

BlackBerry Alert - Started Successfully
BlackBerry Attachment Service - Started Successfully
BlackBerry Controller - Started Successfully
BlackBerry Dispatcher - Failed with error code 5608
BlackBerry MDS Connection Service - Started, then stopped a few minutes later
BlackBerry Policy Service - Failed with error code 5003
BlackBerry Router - Started Successfully
BlackBerry Synchronization Service - Failed with error code 5203

What happens when we try to start the BES Domino task? Let's see...

> load bes
> Cannot start database notification system
04/13/2007 02:36:21 PM Error initialising BES. Terminating.
04/13/2007 02:36:21 PM Shutting down Performance Monitoring and SNMP
04/13/2007 02:36:21 PM BlackBerry Mailbox Agent for Lotus Domino shutdown complete

If we look in the Event Viewer we can see many entries which refer to 'permission denied', such as this when starting the BES task above:

ReportEnvironmentDB::ReportDBSchemaVersion: COM Error 0x80040E09 - IDispatch error #3081 - Source: "Microsoft OLE DB Provider for SQL Server" - Description "SELECT permission denied on object 'ServerDBVersion', database 'BESMgmt', owner 'dbo'." - Command ""

...so I guess we need to go a little further than just 'Permit' & 'public' role access to the BESMgmt database. If we look further down the database role list, we can see that in addition to the built-in database roles, the BES database installation script created some custom roles as well. One of these custom roles is called 'rim_db_bes_server', so let's assign that role as well, since it just makes sense.

04/13/2007 02:38:40 PM BlackBerry Mailbox Agent for Lotus Domino started

Ah, that's much better. The other Windows services started up just fine too. So we can determine from our little experiment that the following minimum SQL permissions are required in order for a BES server to operate:

- SQL Server roles: None required
- BESMgmt database roles: rim_db_bes_server

Next: Minimum permissions for remote BES administration, and role-based security in 4.1