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.