One project we are working on is the move of an existing SBS 2003 domain onto a totally new SBS 2008 setup. The previous SBS 2003 setup was too messed up to even consider a migration, plus we were walking into a totally new server and workstation setup anyway.
One product we are working with in this case is the Thomson Reuters Practice Creative Suite (PCS). It is a product that users use to keep track of their time while working on their client tasks.
Note that, during the Practice CS install routine, install the client software
too as it may be required when testing database connectivity.
Also, install SQL 2005 Express into a new instance leaving the default CREATIVESOLUTION instance name.
The database move was actually quite simple as all that needs to be done is detach the database in Microsoft SQL Server Management Studio Express. Once the database and log file are released, copy them onto a USB hard drive and copy them up to the default CREATIVESOLUTION instance that was created when Practice CS was installed on the new SBS 2008 box.
There are a number of gotchas in this process. One of them is the actual NTFS permissions on the two files once they are in place in the SQL directory.
Since we are talking about SBS 2008 with its own SQL 2005 Express db installed, the default directory will probably be:
C:\Program Files (x86)\Microsoft SQL Server\MSSQL.2\MSSQL\Data
The simplest way to line up the correct permissions on the two SQL files is to right click on the CSP_0_Sample.mdf file and click on
Properties. Click on the Security tab and then click on the
Continue button which will require a UAC acknowledgement.
There should be three sets of permissions with FULL CONTROL set:
- SYSTEM
- SQLServer2005MSSQLUser$WIN-XXXX-CREATIVESOLUTIONS
- Administrators
Duplicate those permissions for the newly copied in database and log files. It is important to do this step first as the attach process will choke if the permissions are incorrectly set with an error about Read-Only and Performing a database recovery" in SQL Server Management Studio Express.
Once the permissions are correctly set on the databases, attach them. Make sure to close SQL Server Management Studio Express (SSMSE) if it is open first. Right click on the SSMSE and Run as administrator. If this step is missed, the database attach will choke.
Okay! We now have a nicely installed set of databases in SSMSE. But, none of the default permissions that Practice needs will be set yet. Verify that the SBS domain admin account is owner of the database within SSMSE by right clicking on the CSP_FIRM#_FIRM# database and clicking on Properties.
If the domain admin does not own the database, then we need to run the SQL 2005 Surface Area tool. Again, right click and Run as Administrator. Click the Add New Administrator link and move the CSP_FIRM#_FIRM# database over to the "Privileges that will be granted to SBSDOMAIN\AdminAccount and click OK.
With the correct permissions set, we need to verify the Users under the Security folder for the CSP_FIRM#_FIRM# database to have dbo (Login name: CreativeSolutionsPracticeCsDatabaseOwner), INFORMATION_SCHEMA, and sys.
The dbo is critical. In our case, the domain admin account was the login setting for the dbo and we were not able to shift to the CreativeSolutionsPracticeCsDatabaseOwner account.
We created a new account called dbo-PCS, set the needed user account as login and give the Database role membership of db_owner.
New dbo-PCS account with ownership set
We now have Practice CS installed on SBS with the requisite reboots, the database has been properly attached, and we have installed Practice CS onto the workstations (no
Remote Entry option), and/or laptops (with
Remote Entry), a connection attempt can be made. More than likely it will fail.
We need to do the following on the SBS 2008 server itself:
SBS 2008 Firewall Exceptions
We need to set up Inbound rules for the following:
- TCP 1433
- UDP 1434
- SQLBrowser.exe ( ..\90\Shared\sqlbrowser.exe)
- SQLServer.exe ( ..\MSSQL.X\MSSQL\Binn\sqlserver.exe)
MSSQL.X indicates the SQL 2005 Express instance. If Practice CS is the first product to be installed on SBS 2008 with an additional SQL Instance, then X=2. The SQL application to be exempted needs to be the one that resides in the same folder structure as the Practice databases.
Run the Practice CS client on the server, and a firm logon page should happen.
Now, run Practice CS on a Vista client, and more than likely there will need to be one more firewall rule set up ... but not on the server, in Windows Vista.
Windows Vista Outbound Firewall Rule for Practice CS
Once we created the outbound exception on the Windows Vista client box, we were still seeing a connection error.
There was one more step to the puzzle. On the SBS server, run the client and connect to the firm database. A logon is not required. Click cancel for the logon dialogue box to get to the Practice program, click on Help then About Practice CS. There is a Download Licenses button. Click on this to enter in the necessary particulars for the firm and allow the license files to download.
Once the files have downloaded, the Practice CS client will run a reinstall routine. If Practice CS is installed on all of the network clients already, then they too will run through the reinstall routine.
Once the license files are installed, Practice CS will fire up and the firm's database will be available for login.
Because this was a very time consuming and labourious process, some steps may be missed. Here is an overview of the process:
- Detach on original SBS
- Install Practice CS including client. Reboots required.
- Db and Log File copy to new Practice CS SQL instance directory.
- Files permissions set identical to the Practice Sample Db and Log File settings.
- Attach in SSMSE (Right click and Run as Administrator to open).
- Run SQL Server Surface Area Configuration and set domain admin to CREATIVESOLUTIONS instance.
- Set proper permissions for dbo in SSMSE.
- Set up SBS 2008 Firewall permissions for two applications and two ports.
- Test connection in Practice CS to firm database.
- Cancel login and download and install the firm's license files.
- Set Outbound program rule in Windows Vista for Practice CS.
- Run Practice CS on Vista client and connect to database.
Please comment if anything is caught missing or out of line with the process, and we will update the blog post.
Philip Elder
MPECS Inc.
Microsoft Small Business Specialists
*All Mac on SBS posts are posted on our in-house iMac via the Safari Web browser.
