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