you are reading Article 7
In the previous articles we accomplished the initial work to setup and share BCM and MOA across a network from a Remote SQL Database. To review what we’ve done to this point:
- We installed SQL Server on a dedicated Server machine
- We established an instance named MSSMLBIZ
- We installed Business Contact Manager on one of the Client Workstations on our network and pointed it to our MSSMLBIZ instance
- We moved the data and log files (.mdf and .ldf) to a more universal and stable location within the file structure of our Server
Now that the BCM database (.mdf and .ldf) files have been re-attached to the MSSMLBIZ instance, we move on to installing and then integrating Microsoft Office Accounting 2008 with our existing BCM installation. The end result will provide both Office Accounting and Business Contact Manager functionality as a unified platform on which to conduct business from our network.
Key integration points for those new to the platform:
Microsoft Office Accounting 2008’s database can be MERGED with an existing Business Contact Manager database. The merging provides significant benefits to Small Businesses looking to achieve a fully integrated Customer Relationship and Accounting platform. The integration can be accomplished over a network and makes the data and functionality available to multiple users. The price/performance ratio makes this combination a “very good value” given that BCM is as little as $150/seat and Office Accounting Pro is $199/seat. The final piece needed is SQL Server 2005 Express or higher. While SQL Server 2005 Express is “free software” and will work similarly, our article series is written based on using SQL Server 2005 Standard on a dedicated SQL Server.
Order of Software Installation: Office Accounting 2008 is installed ”after” Business Contact Manager has been completely configured. One copy of Office Accounting must be installed on the SQL Server. Because integration uses Accounting’s menus, the full task of completing integration must be finished at the SQL Server before other “workstation” installations of Office Accounting can be added. These subsequent ”Client” installations are directed to the integrated database. Client installations do not then require a local SQL database as part of their installation (see Custom Workstation Installations of Office Accounting at end of article).
PLEASE NOTE: we have chosen to use a dedicated SQL Administrator account for the initial install of Microsoft Office Accounting on the SQL Server. This provides access to the full array of tools and functionality. Since our SQL Admin account “owns” the database, we have unrestricted administration rights and can change the installation as needed using SQL Configuration Manager, SQL Management Studio and the proprietary Office Accounting data tools, (please read earlier articles for explanation).
Integration Procedure Office Accounting 2008 and Business Contact Manager for Outlook 2007
On the SQL Server - install Office Accounting 2008 using the SQL Administrator account (details about this account earlier in the series)
This initial installation of the “first” Office Accounting 2008 to be installed uses the same MSSMLBIZ instance already installed on the SQL Server for BCM. During Accounting setup, a second database will be created within the MSSMLBIZ instance. This second database is exclusively for Office Accounting. Note that at this point the databases of BCM and Accounting have not been merged.
To integrate the Microsoft Office Accounting database with Business Contact Manager for Outlook 2007 database, follow these steps:
- Open Microsoft Office Accounting Professional
- Go To Company | Integrate with Business Contact Manager
- The Integrate with Microsoft Office Outlook with Business Contact Manager wizard will launch
- Review the Welcome text, click Next
- Select a Business Contact Manager for Outlook database
- This should be the same database name created for BCM originally, or the one currently active if you have created additional databases since first installing the software.
- Select the database from the drop-down menu, click Next
- Back up databases
- Backing up both of the existing BCM and MOA databases is REQUIRED prior to Integration. This is to protect your existing Business Contact Manager data (as it currently exists) AND your Office Accounting data (as it currently exists)
- Browse to the directories in which you want to store your BCM and MOA backups
- Type a password to protect your data (if necessary)
- Be ABSOLUTELY CERTAIN TO WRITE DOWN AND RETAIN THE PASSWORD! It is required to restore data if ever needed.
- Click Next
- Business Contact Manager Email Auto Linking
- Make your choice as to Retain the Email Auto-linking settings or Disable them
- Click Next
Merge Duplicate Accounts and Customers
!!! IMPORTANT !!! if you have been using Office Accounting and Business Contact Manager separately for some time (meaning “prior to merging them”), consider this carefully:
- SCENARIO 1: If you have a small contact database and you are not certain as to what records are current what records are outdated; you may want to ALLOW duplicate records to be created. With only a few records, the time necessary to scrub the data (clean it up) may not be excessive.
- SCENARIO 2: however !!… if you have a large database of BCM “Contacts & Accounts” and a large database of Accounting “Customers” and these programs have been used “separately”, you may want to audit the records in both programs prior to merging them (which means STOP HERE AND DO NOT INTEGRATE YET!). Allowing duplicates to be created when there are “many many” records creates obviously “much more work”. Think sensibly about duplication before making your selection and always attempt to keep records current so that the data merged is exactly the “same” data :).
- NOTE: remember this only applies when BCM and MOA have been used separately prior to integration. If this is a completely new and clean install of the programs, you will have no records to merge, so merging therefore would not be an issue.
Make your choice, click Next
The list of CURRENT OFFICE ACCOUNTING USERS will be presented at this point.
To review considerations regarding permissions (for both applications), read this Article.
Here is a quick review of the topic:
ALL users with permissions in Office Accounting will have permission to use Business Contact Manager
The same is not true in reverse. Business Contact Manager Users must be “explicitly granted permission to have to access in Office Accounting (i.e. accessing financial records of Customers, generating quotes and invoices, viewing and changing financial records for Banking, etc….)
On a domain, access to accounting is based upon Active Directory Users (not Groups). Users located in the directory are given “permission” to log on to Accounting. The level of access available upon log on varies. Users “level of access” is generally determined by the business Owner/Manager. These decisions should be made prior to integrating BCM and Office Accounting. Finalizing access considerations assures that Users will have the “full” functionality (in their roles) that is expected of the Office Accounting software once integration is completed.
Office Accounting offers the following roles which are assigned to users on the domain:
- No Access
- Read-only User
- Salesperson
- Accountant
- Office Manager
- Owner
Confirm your selections for Office Accounting users
Click Next
Reviewing the function and importance of the .sbc file in Microsoft Office Accounting
Business Contact Manager for Outlook was oriented to the MSSMLBIZ instance in Article 4 of our series. BCM locates the shared database “differently” than Microsoft Office Accounting.
To review Microsoft Office Accounting design when locating a shared database:
Office Accounting locates the SQL Server by querying a Small Business Company text file using the file extension (.sbc). The .sbc text file is located somewhere on the network and is literally “how” Microsoft Office Accounting gets directed to the Remote Database Server. Deciding where to store and how to access the .sbc file is a fundamental consideration whenever Office Accounting is being shared amongst multiple computers. The (.sbc) file is always required in “shared scenarios” and will still be required after integrating an Office Accounting database with a Business Contact Manager database.
Choosing a filename and location for the .sbc file in the Microsoft Office Accounting integration wizard
To complete the basic steps in configuring Office Accounting for multiple users, the location from which the company.sbc is accessed must be properly SHARED to users. All users requiring access to Microsoft Office Accounting will require access to this file from their local machines. Place the file in a Network Share and let every person running Office Accounting know where it is and how it can be accessed on the network
Once the location for the .sbc file has been determined and confirmed as SHARED.
- Select that location from within the Integration Wizard
- confirm the “name” of the .sbc file
- click Save, and click Next
That’s it. Office Accounting now knows where (and how) to access the shared database. The next step is to complete your work to this point and integrate (merge) the databases.
Here we go…. Drum rollllll pleeeaaassee.
To continue, click Integrate
Backup of the database will now begin
Followed by moving and merging the necessary tables as well as re-defining the database schema for both programs
The wizard will then assign the Office Accounting Permissions to domain users on the network as specified earlier
and then…
COMPLETED (ta da!)
Turn on the OPEN sign, we’re ready for business
What is inside a Small Business Company (.sbc) file?
On workstation installations, Microsoft Office Accounting must be instructed “where to find” the Office Accounting Remote Database. To review our earlier statements, Office Accounting answers this question by use of the .sbc “text file”. The text file (yourcompany.sbc) details specifics about the remote SQL Database (you can open the .sbc file with any text editor). Here is what the .sbc file contains:
- the NAME of the database
- the NAME-OF-THE-SERVER the database is located on
- the SQL-PORT-NUMBER on which you must make your requests
How we choose to Share the .sbc file and its parent directory
- Locate the FOLDER in your TEAMFOLDER (see earlier article) in which you will store and share the .sbc file
- Right click on the chosen folder and select Sharing and Security
- Share the folder containing the .sbc file
Set permissions for folder containing .sbc file. Our practice is to create two Groups first
- Management “Admin/Management Group“
- BCMMOA “Users Group“
The Management group “could include” the Network Administrator and potentially the Owner/Office Manage
- provide “Modify” permissions to the Management “Group”
The BCMMOA group included all other Users (employees, salespersons, bookkeepers, etc…)
- provide “Read” permissions to the BCMMOA “Group”.
Using these permissions, regular users (the BCMMOA Group) can launch Office Accounting locally on their Client machine. Office Accounting would then READ the .sbc file from the network share. The information in the .sbc directs the Client to the SQL Server. All Client machines read and write to the same database.
Management Group Users do the same, however, with MODIFY permissions (the Management Group) permissions on the share could be altered as needed. In unusual cases even “deny” ACLs could be used. REMEMBER however that Office Accounting provides control of permissions from within the software itself. Access rights can be set to No Access using the GUI. Be careful about using Deny ACLS on the share as this will completely bar Users from accessing the Accounting 2008 .sbc file which in turn would override any permissions in Office Accounting because the .sbc pointer to Accounting would be closed to access.
Once these permissions are properly set, you are clear to finish your Client installations. Congratulations.
About Microsoft Office Accounting on Workstations and Laptops
Custom Installation: During Client installation of Accounting, simply choose “CUSTOM” during the first steps of installation. You can tell the installer not to install SQL Server as well as provide the location of the .sbc file you have shared on your network.
Remember Permissions: For each Workstation installation, Office Accounting requires you to explicitly permit access to each User in the domain. Permissions are granted using the Accounting software program itself. Groups are not recognized in granting permission so individual Users must be granted access one by one. Here are the levels of access:
Available Roles:
- No Access
- Read-only User
- Salesperson
- Accountant
- Office Manager
- Owner
Accounting access can be granted at the SQL Server (using the Office Accounting GUI). Permissions can also be granted from a Workstation “IF” the logged on User has the Owner role or Office Manager role.
Using the SQL Administrator Account to grant access:
REMINDER: our practice throughout this article series (when working at the SQL Server) has been to use a master SQL Administrator account (for installs of Office Accounting and BCM). “By default” this account has the highest level of access possible for your Office Accounting installation. The SQL Admin Account is a valuable resource if something goes wrong with permissions. You can always go to your SQL Server, logon using SQL Administrator Account, launch Office Accounting 2008 and set permissions for Users. This is always possible because (the SQL Administrator Account owns the database) and is grated Owner permissions in Accounting “by default”.
If permissions are every negatively altered from a workstation (by an Owner or Office Manager) in a way that is destructive to access, the SQL Administrator Account should provide the necessary level of access to restore proper function.
CLIENT INSTALLATIONS OF OFFICE ACCOUNTING 2008 - (AVOIDING LOCAL INSTALLATIONS OF SQL SERVER ON WORKSTATIONS)
One improvement over BCM that exists is in the Office Accounting 2008 setup and installation procedure is that Accounting offers the ability/choice to not install a SQL database at time of first installation. BCM does not give this option. For Accounting installations use the Advanced Options to avoid installation SQL and to point Office Accounting to the existing database. The advantages:
- it is a big time saving element during install
- it improves system performance by not having an unneeded SQL install on the machine
NOTE: in existing BCM installations that are connected to a Remote Database (as with the example in this article series) - for client “Desktop” machines, you may uninstall SQL Server 2005 Express and the MSSMLBIZ instance entirely from that client “Desktop” machine. SQL is not needed on any “Desktop” machine permanently connected to the network and requesting its data from the Remote SQL Server. Removing the local SQL instance takes only a little time and the performance gains are significant (especially for older machines).
NOTE: for laptop machines, keeping the MSSMLBIZ instance of SQL Server 2005 Express installed is advisable. Having a local instance of MSSMLBIZ on laptops enables the ability to use the company’s central BCM Database offline. This enables Salespersons to travel outside the office (taking a copy of the database with them), they can conduct business “in the field” using the company BCM database locally on their laptops and then sync up the database upon return to the office. This feature is “huge” in the real world for SalesPeople especially because items from Office Accounting (Services and Products) can be quoted in BCM while “unattached from the network”. This allows the Salesperson to take their products and services with them! For those unfamiliar with the feature, Quoting Services and Products in BCM is accomplished using the Opportunity feature and it’s very cool.
This concludes Article 7
About WIGITAL. We are IT Experts providing services to companies that seek technology to empower business. We are located in Southern California. Please contact us at WIGITAL.
Thank you for reading 
Mark Raborn - WIGITAL
