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!

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

BES SQL Permissions Explained - Part 1

In the last post we talked about setting up a test environment to mimic a production BES / SQL environment. In this post we will talk about the actual SQL server permissions required to create or upgrade a BES management database.

If this is an initial install, then the BES management database will most likely be automatically created and configured via the BES installation program. However, there is a pre-requisite: If using SQL authentication like we are, we need to have a SQL login defined on the SQL server with the appropriate permissions.

Using our DbaMgr2k tool, we first:

1. Double click on our SQL Server to open the Connection window. Type in the password for the default 'sa' account and click the checkbox to 'Save password'. We are using SQL authentication so you can leave the 'Trusted NT Connection' blank.



2. If you click on Logins you can see that there are only 2 users setup by default. One is the 'sa' account we used, and the other is the NT Builtin\Administrators group. Although we are using SQL authentication this account should not be removed.



3. Right click on Logins and select New Login. This new user will be the account we use in the BES installation to create and configure the BES database. Be sure to type in a password and click 'Save' before configuring any other settings; a little quirk of our free tool.



4. Click on the 'Server Roles' tab and check the 'serveradmin' role and the 'dbcreator' role. These roles give the login account permission to create new databases and do other server wide tasks. For now we can ignore the 'Database access' tab, as there is no BES database created yet. Click 'Save' and then 'Close' to save and exit.



5. Now from our BES install, we connect to the SQL server using these credentials, and we are prompted to create the database.







Just for fun, let's see what happens when the SQL login does not have the
'serveradmin' role assigned. Of course, we get the dreaded error message:



But what actually fails? Lets check the installer logs to find out:

[30000] (04/12 14:55:19.192):{0x33C} SQL being executed:
EXEC sp_addmessage @msgnum = 60002, @severity = 16,
@msgtext = N'Unable to add new BlackBerry Agent "%s" AgentID=%d Machine Name=%s',
@lang = 'us_english'

/* BESRouter errors */
IF EXISTS (SELECT error FROM sysmessages
WHERE error = 60103)
EXEC sp_dropmessage @msgnum = 60103, @lang = 'us_english'
[20000] (04/12 14:55:19.192):{0x33C} SQL Error Message from CBESDBInstaller::ExecuteSql.executeDirect: SQLSTATE: 42000 Native error: 15247 Message: User does not have permission to perform this action.

Looks like the DB installer script was unable to add custom BES error messages to the SQL server.

How about the other way around, the SQL Login has 'serveradmin' permissions but not 'dbcreator'?

[20000] (04/12 14:49:17.952):{0x12C} SQL Error Message from CBESDBInstaller::ExecuteSql.executeDirect: SQLSTATE: 42000 Native error: 262 Message: CREATE DATABASE permission denied in database 'master'.

In this case the DB installer script could not even create the BES DB and failed immediately. Looks like we need both roles in order to create or upgrade the database.


Now that we have the database created and understand the SQL Server roles required, the next post will examine the database roles required for daily server functioning and administrative tasks.

BES SQL Permissions Explained - Part 0

I work for a large law firm, so each department is separate, even within IT. When we upgraded from 2.2 to 4.0 I needed to work with our SQL folks to setup and host the new SQL DB for the BES servers.

This coordination can be difficult, especially since I know little about the operation of SQL server and had difficulty communicating what I needed. It doesn't help that RIM's documentation about SQL permissions is conflicting in many cases, and dependent upon which version of 4.x you are running, as well as which components are being installed. Ugh!

This post will attempt to decode the mystery of SQL server permissions for a BES Domino environment. Here in part zero (zero b/c I don't actually talk about permissions yet!) I will discuss setting up a test environment for exploring how these parts work together to begin with.

In order to get the skinny on all of this, I had to hack together a test environment that mimicked a production BES / SQL install. I did this by:

1) Installing the free Microsoft Virtual PC software. (Free!)
2) Creating 3 Win2K SP4 virtual machines; two BES 4.0 servers and a SQL server

I used Win2K b/c there are no messy issues with online activation required, I reused some old keys I had written on the CD's. Since this is a test environment I don't feel too bad about that.

Not having access to the SQL server code or management tools, however, I had to improvise, which was an exercise in itself. From the BES 4.1 installation media, I installed the MSDE database engine (tools\SQLRun01.msi) on my "SQL" virtual server. I then performed the following to turn it into an almost gen-u-ine SQL server:

1) Change the default 'sa' password from NULL to something else

To do this you need to use the command line OSQL tool which allows you to run SQL scripts from a command prompt. For this I used the following commands:

osql -E
1> exec sp_password @old = null, @new = 'newpassword', @loginame = 'sa'
2> go
Password changed.
1> exit

This runs a Stored Procedure called 'sp_password' on the SQL server itself to change the password. Make sure to spell 'loginame' with only one 'n'!

2) Change the authentication mode from default of "Windows" authentication to "Mixed" authentication.

Note that you can have two authentication modes in MSDE / SQL:

- NT only using AD or local Windows users / groups (Windows)
- NT + SQL where logins are managed with SQL itself (Mixed)

Note that you cannot have just SQL authentication by itself.

What this change does is allow native SQL logins to access the SQL server, so I don't have to worry about ugly NT users / groups / permissions. I find SQL authentication easier and cleaner to manage and make sense of in my head. For this we need to:

- Stop the MSSQLSERVER service
- Change HKLM\Software\Microsoft\MSSqlserver\MSSqlServer\LoginMode from 1 to 2
- Restart the MSSQLSERVER service

3) Enable network access to the "SQL" server

By default MSDE is only useful for applications local to the machine it is installed on. Changing this allows remote machines on the network to access the MSDE SQL server just like it's big brother.

- Stop the MSSQLSERVER service
- Open command prompt
- Run 'svrnetcn'
- Click 'TCP/IP' and click 'Enable'
- Click 'Named Pipes' and click 'Enable'
- Click OK
- Restart the MSSQLSERVER service

4) Test login over the network.

- Copy the OSQL.EXE utility to another machine on the network
- From a command prompt, run OSQL -S [hostname or IP] -U sa -P newpassword
- You should get a '1>' prompt, meaning you are connected to the server.


OK, so now we have a working, network accessible SQL server. How do we manage it without learning all the arcane OSQL syntax and SQL stored procedures? Fortunately there is a free management tool out there called "DBAMgr2K". Google this name and you should be able to find the website and download it.

It is basically a free, simple version of SQL Enterprise Manager for use with MSDE databases. It is great because it acts and displays very similarly to the real SQL Enterprise Manager, so when I was comfortable operating this tool, I could tell the SQL guys exactly what to do in EM to get the same result in our production environment.

Next up: Permissions details for BES SQL databases