Access 2007: Digital signatures are lost in ADE files

Some weeks ago I posted the following message in microsoft.public.access.security. Here I will reproduce and extend it, since I have not received any response there, to spread the words and reach a higher audicence. Maybe someone would give me a hint some day...

I also made a translation of this article in spanish called Access 2007: La firma digital se pierde cuando se compacta un .ADP o se crea un fichero .ADE

We have been using digital signatures for years (Office 2003) to deploy MS Access complied project files (.ADE) to avoid users to accept security warnings every time they opened the application.

Now we are using Office 2007 and whenever we create ADE files (with signed VB code), we receive (in Access status bar, on the bottom) the following warning (maybe the warning is not exactly as this, since it is a self-made translation from Spanish):

"The changes made to the databes or project had invalidated the linked digital signature"

"Las modificaciones realizadas en la base de datos o proyecto han invalidado la firma digital asociada." (this is the original warning).

After that, when any user tries to open the created ADE file, they receive the usual warning as if the file had no digital signature at all:

Microsoft Office Access Security Notice A potential security concern has been identified. Warning: it is not possible to determine that this content came from a trustworthy source. You should leave this content disabled unless the content provides critical functionality and you trust its source.

That dialog confirms that the original warning shown in the status bar was right. The signature is lost when you compile the ADP into an ADE file.

I have checked that this happens when creating a brand new database project:

1. Create a new database project (ADP) and connect it to any SQL Server / Northwind you have at hand.
2. Create a form with a single button in it and use the following code:

   Option Compare Database
   Option Explicit
   Private Sub Command0_Click()
   MsgBox ("this is a test")
   End Sub

3. Sign the code: Tools -> Digital signatures...
4. Create ADE
5. Try to open the ADE you created before

Can this be classified as a bug or a feature? Is there any place to submit feedbacks for Access 2007?

Thanks in advance.

To this post, and after having done more tests, I would also say that a similar behaviour is shown whenever you compact and repair an ADP file the digital signature is lost too.

It does not matter that there are alternative ways to prevent the security warnings from appearing (i.e. Trust Center). I think this is a real bug that prevents anyone that owns a Digital Certificate from signing and distributing code (without distributing the code, i.e. ADE files) the way we were used to do with Access 2003.

I hope staff at MS solve it better sooner than later. I'm also willing to hear from your experiences on this field.

Links

Access 2007: Digital signature is lost when you create ADE files in Access Security

Access 2007: La firma digital se pierde cuando se compacta un .ADP o se crea un fichero .ADE

Access 2007: Digital signatures are lost in ADE files in Access Database General Questions

Access 2007: Digital signatures are lost in ADE filesin Access ADP SQL

DFS replication: Inter-site problems

We had DFS replication (the one released with Windows Server 2003 R2) between 3 servers, two of them on the central site (MAIN1 & MAIN2) and the other in the branch site (BRANCH1). The topology we had set up was a hub and spoke with MAIN1 as the center of the star: MAIN2 <-> MAIN1 <-> BRANCH1

One day we thought that it would be better to have a full mesh topology, just in case MAIN1 had any problem, in our case the full mesh was equivalent to hub and spoke with a secondary or alternative hub server. We just added a new connection in the connection tabs for every replication group we had already set up and running, enabling the option to 'Create a second connection in the opposite direction', so that we had also a connection as MAIN2 <-> BRANCH1 besides the others that we had already up and running for months. And forgot about it.

It seemed so simple... If a less connected topology had been working for such a long time without any problem, why would there be any problem with a more tighten topology? As I said, we just add the new connections and switch over to other things to do.

After some days, our clients start complaining about missing files (mismatch) between servers. We checked they were right and went to see event logs for the servers. In summary there were these types of errors, warnings and informational messages:

Events in MAIN1

Event Type: Error
Event Source: DFSR
Event Category: None
Event ID: 5012
Date:  03/05/2007
Time:  16:47:50
User:  N/A
Equipo: MAIN1
Description:
 The DFS Replication service failed to communicate with partner MAIN2 for replication 
 group Orders. The partner did not recognize the connection or the replication group 
 configuration.

 Partner DNS Address: main2.my-domain.com

Optional data if available:
 Partner WINS Address: main2
 Partner IP Address: 192.168.1.9

The service will retry the connection periodically.

Additional Information:
 Error: 9026 (The connection is invalid)
 Connection ID: 0A5392D6-D3D5-49BF-9D1A-F4DFF1C4F0F2
 Replication Group ID: 4F725FDD-34A9-42CA-A3D1-194175AD8F23

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Events in MAIN2

Event Type: Error
Event Source: DFSR
Event Category: None
Event ID: 5008
Date:  03/05/2007
Time:  16:43:37
User:  N/A
Equipo: MAIN2
Description:
 The DFS Replication service failed to communicate with partner BRANCH1 for replication
 group Orders. This error can occur if the host is unreachable, or if the DFS Replication 
 service is not running on the server.

 Partner DNS Address: branch1.my-domain.com
 Optional data if available:
  Partner WINS Address: branch1
  Partner IP Address: 192.168.3.3
 The service will retry the connection periodically.

 Additional Information:
  Error: 1722 (The RPC server is unavailable.)
  Connection ID: 543733B4-D8CC-4C99-9F03-11635B5BC966
  Replication Group ID: 4F725FDD-34A9-42CA-A3D1-194175AD8F23

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Event Type: Information
Event Source: DFSR
Event Category: None
Event ID: 5016
Date:  03/05/2007
Time:  16:47:36
User:  N/A
Equipo: MAIN2
Description:
 The DFS Replication service detected that the connection with partner branch1.my-domain.com
 for replication group Orders has been removed or disabled.

 Additional Information:
  Connection ID: 543733B4-D8CC-4C99-9F03-11635B5BC966
  Replication Group ID: 4F725FDD-34A9-42CA-A3D1-194175AD8F23

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Event Type: Warning
Event Source: DFSR
Event Category: None
Event ID: 6804
Date:  03/05/2007
Time:  16:47:36
User:  N/A
Equipo: MAIN2
Description:
 The DFS Replication service has detected that no connections are configured for replication
 group Order. No data is being replicated for this replication group.

Additional Information:
 Replication Group ID: 4F725FDD-34A9-42CA-A3D1-194175AD8F23
 Member ID: 453E3E21-9989-4795-9C47-5022FC79872A

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Event Type: Information
Event Source: DFSR
Event Category: None
Event ID: 2010
Date:  03/05/2007
Time:  16:48:11
User:  N/A
Equipo: MAIN2
Description:
 The DFS Replication service has detected that all replicated folders on volume E: have been
 disabled or deleted.

Additional Information:
 Volume: AE7DC6D0-1C7C-11DB-A9C6-001372640D81

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Event Type: Information
Event Source: DFSR
Event Category: None
Event ID: 1206
Date:  03/05/2007
Time:  16:49:11
User:  N/A
Equipo: MAIN2
Description:
 The DFS Replication service successfully contacted domain controller main2.my-domain.com to
 access configuration information.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

This latest informational message was repeated every 5 minutes approximately and buried deep in the logs the real errors and warnings. Note that when DFS is working fine, this message is not shown (at least not so repeatedly). One particular thing was that another replication group called Backups, that kept in synch files between MAIN1 and MAIN2 was working fine.

In summary, we had intra-site-replication groups that worked fine, and all inter-site-replication groups were exposing errors and not replicating at all since we did the full-mesh topology change. Just for testing, we created a new replication group from start between MAIN1 and MAIN2 and it replicated fine.

Then we added BRANCH1 to the replication group with a hub-and-spoke topology centered in MAIN1 and still replicated fine. Finally, changed the topology to be full-mesh and the same behavior appeared. Weird. Googling for information about my problem I reached How Active Directory Replication Topology Works and read:

Active Directory KCC Architecture and Processes [...] One domain controller in each site is selected as the Intersite Topology Generator (ISTG). To enable replication across site links, the ISTG automatically designates one or more servers to perform site-to-site replication. These servers are called bridgehead servers. A bridgehead is a point where a connection leaves or enters a site. [...]

I noticed they were using DFS terminology: connection, replication... but the fact was that the document was related to AD replication, not DFS replication. I decided however to give it a try: I opened Active Directory Sites and Services to check which of my servers was(were) bridgehead server(s).

Only MAIN1 was a bridgehead server for IP transport, MAIN2 was NOT configured as a bridgehead server. And new questions arose: How was this configured? Is this automatically or manually configured? Should we override this configuration and select two servers as bridgehead servers?...

As stated in Active Directory Operations Guide, Managing Sites

Bridgehead Server Selection By default, bridgehead servers are automatically selected by the intersite topology generator (ISTG) in each site. Alternatively, you can use Active Directory Sites and Services to select preferred bridgehead servers. However, it is recommended for Windows 2000 deployments that you do not select preferred bridgehead servers. Selecting preferred bridgehead servers limits the bridgehead servers that the KCC can use to those that you have selected. If you use Active Directory Sites and Services to select any preferred bridgehead servers at all in a site, you must select as many as possible and you must select them for all domains that must be replicated to a different site. If you select preferred bridgehead servers for a domain and all preferred bridgehead servers for that domain become unavailable, replication of that domain to and from that site does not occur. If you have selected one or more bridgehead servers, removing them from the bridgehead servers list restores the automatic selection functionality to the ISTG.

Mhh... you must select as many as possible... it sounded as it would be a good idea to enable MAIN2 as a bridgehead server too, despite of my DFS problems, this change would tighten our AD replication topology and so we changed it. Luckily this little change of enabling MAIN2 as bridgehead server solved all the DFS replication problems.

I have found lots of HOWTOs, documentation and technical documents about setting up DFS replication and none of them said a word about the importance of bridgehead servers for inter-site-replication of DFS, not only for AD replication. I hope this blog entry could help someone and save them some days of headaches as I had.

Keywords: DFS, replication, active directory, ad, bridgehead servers, problems, errors, inter-site, intra-site, active directory sites and services

Links:

Step-by-Step Guide for the Distributed File System Solution in Windows Server 2003 R2
Active Directory Operations Guide
How Active Directory Replication Topology Works

Partial multi-column reports in Reporting Services

Scenario. You need to create a multicolumn report in reporting services, but unfortunately, you just need the multicolumn behaviour in the details part of the report, not also on the master.

Suppose you have an invoice (master: invoice number, customer, total sum, etc, details: every item being invoiced), or any other type of report that has a master/details structure and you just need the multi column behaviour in the details part of it.

Problem. As stated in Writing Multi-Column Reports:

A multi-column layout applies to the entire report. It is not possible to specify a multi-column layout on the top half of the report, and a tabular layout on the bottom half of the report.

It seems you really have a problem... Microsoft says that it is impossible to do what you need to. Fortunately there is a...

Workaround. The idea behind this workaround is letting RS believe that the report is just a tabular report as usual. In fact, all you need to do is pure T-SQL behind the scenes, the final report just have one column, but will seem to have 2 columns (or any) where you need to. Let's see it with an example. Suppose you have your data:

Invoice Customer Item Description  Qty  Price    Sum
 394483 JOHN DOE    1 FRENCH FRIES  1    3.00  17.00
 394483 JOHN DOE    2 BISCUITS      1    4.00  17.00
 394483 JOHN DOE    3 BEERx6        1    5.00  17.00
 394483 JOHN DOE    4 MILK          2    2.00  17.00
 394483 JOHN DOE    5 HAM           1    1.00  17.00

And you want to make your invoice with the details (Item, Description, Qty and Price) displayed in two columns. We need to transform the data into something like:

Invoice Customer Item Description  Qty  Price    Sum Item2 Description2 Qty2 Price
 394483 JOHN DOE    1 FRENCH FRIES  1    3.00  17.00     2 BISCUITS      1    4.00
 394483 JOHN DOE    3 BEERx6        1    5.00  17.00     4 MILK          2    2.00
 394483 JOHN DOE    5 HAM           1    1.00  17.00  NULL NULL         NULL  NULL

Do yo see the point? You need to create new fields (ended with 2 suffix) that will store the next row of the needed data, and return half the rows (plus one if the count is not pair). Having transformed your underlying data in such a way, you can create your report as usual, placing a table where you need to and doubling (in case you want 2 columns) the number of columns:

Item Description  Qty  Price         Item2 Description2  Qty2  Price2

How can we do such a transormation using T-SQL?

Let's suppose you have prepared a stored procedure for retrieving the underlying data for RS to use. If you have not, you will need to because we will be doing a couple of transformations, not just a simple select query, so you cannot embed the query into the report itself. You will need to use a stored procedure for it, let's call it RS_MyCustomReport.

You might have RS_MyCustomReport defined as something like:

USE [mydatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[RS_MyCustomReport](@Filter1 int = NULL, @Filter2 varchar(8)=NULL)
AS
  SELECT {your list of fields}
  FROM {your tables or views}
  WHERE ({FieldX} = @Filter1 OR @Filter1 IS NULL) AND
        ({FieldY} = @Filter2 OR @Filter2 IS NULL)

Where FieldX and FieldY are the fields to use to filter using the optional parameters @Filter1 and @Filter2. All you need to do is the following (the changes are highlighted):

USE [mydatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[RS_MyCustomReport](@Filter1 int = NULL, @Filter2 varchar(8)=NULL)
AS
  SELECT IDENTITY(int,1,1) AS ID, AUX1.*
  INTO #TEMP
  FROM (
    SELECT {your list of fields}
    FROM {your tables or views}
    WHERE ({FieldX} = @Filter1 OR @Filter1 IS NULL) AND
          ({FieldY} = @Filter2 OR @Filter2 IS NULL)
  ) AUX1

  SELECT * FROM
   ( SELECT T1.*, T2.{FieldA} AS FieldA2, T2.{FieldB} AS FieldB2 {...}
     FROM #TEMP T1 LEFT OUTER JOIN #TEMP T2 ON (T1.ID+1 = T2.ID OR T2.ID IS NULL)
   ) AUX
  WHERE AUX.ID % 2 = 1
  ORDER BY AUX.ID

You can replace the number of joins and the % 2 operator if you need to create multi column reports of 3 or more columns. A whole running script that you can test follows:

SELECT IDENTITY(int,1,1) AS ID, AUX1.*
INTO #TEMP
FROM (SELECT 13 AS Col, 'one' AS Descr UNION
      SELECT 18, 'two' UNION
      SELECT 35, 'three' UNION
      SELECT 51, 'four' UNION
      SELECT 67, 'five') AS AUX1

SELECT * FROM #TEMP

SELECT * FROM (
  SELECT T1.*, T2.Col AS Col2, T2.Descr AS Descr2
  FROM #TEMP T1 LEFT OUTER JOIN #TEMP T2 ON (T1.ID+1 = T2.ID or T2.ID IS NULL)
) AUX WHERE AUX.ID % 2 = 1
ORDER BY AUX.ID

DROP TABLE #TEMP

How to: Enable Remote Errors (Reporting Services Configuration)

Simple, but it took me a while to find it when I needed it. So here it is: How to: Enable Remote Errors (Reporting Services Configuration)

You can edit the ConfigurationInfo table in the report server database to set EnableRemoteError to True, but if the report server is actively used, you should use script to modify the settings.

Error 0x8007007f: A problem is preventing Windows from accurately checking the license for this computer

SYMPTOMS After you install SP2 for Windows Server 2003 x64 you get the following error message when you try to login using local console or RDP:

A problem is preventing Windows from accurately checking the license for this computer: Error Code 0x8007007f

Un problema impide que Windows compruebe con precisión el estado de la licencia para este equipo. Código de error: 0x8007007f

RESOLUTION Some days after having approved SP2 for Windows Server 2003 x64 in WSUS and having it installed on several servers without any problem, I tried to log in using RDP on another one of them and noticed the former error message. Then I went to the local console and see a bunch of error messages on screen (without having anyone logged on) such as "not enough virtual memory...", "application xxxx will now terminate", and so on, poping up in front of the usual "press ctrl+alt+del to log in" dialog. So I needed to accept all those error messages before being able to actually log in the server from the console.

After having read and accepted 5 or 6 of them, I started to just press Ok without even read the messages... Finally I was able to press ctrl+alt+del and try to log in... and the same error regarding code 0x8007007f appeared, logging me off automatically (logging in process did not even finish).

So I went back to my XP client and used the Event Viewer to remotely connect to the server experiencing the problem and see the System event log. Quite near the top of the list of events I found:

Event Type: Information
Event Source: NtServicePack
Event Category: None
Event ID: 4371
Date:  21/03/2007
Time:  15:21:52
User:  NT AUTHORITY\SYSTEM
Computer: SERVERNAME
Description:
Windows Server 2003 Service Pack 2 was installed (Service Pack 1 was previously installed).

Event Type: Information
Event Source: Windows Update Agent
Event Category: Installation 
Event ID: 19
Date:  21/03/2007
Time:  15:21:59
User:  Not available
Computer: SERVERNAME
Description:
Installation Successful: Windows successfully installed the following update: Windows Server 2003 Service Pack 2 for x64 Editions

Event Type: Information
Event Source: Windows Update Agent
Event Category: Installation 
Event ID: 22
Date:  21/03/2007
Time:  15:21:59
User:  Not available
Computer: SERVERNAME
Description:
Restart Required: To complete the installation of the following updates, the computer will be restarted within 5 minutes: 
- Windows Server 2003 Service Pack 2 for x64 Editions

But no trace of the expected reboot was found at on the rest of the System event log. So I just used shutdown -i to interactively shutdown the server and restart it again. That solved the problem completely.

SCENARIO

• Dell PowerEdge 2950 (Dual Xeon, 4Gb RAM)
• Windows Server 2003 Standard x64 R2 + SP1
• SQL Server 2005 Standard Edition
• WSUS is used to deploy updates to computers in the domain
• Windows Server 2003 Service Pack 2 (SP2) for x64 Editions is released and aproved in WSUS for automatic install

KEYWORDS Windows 2003 Server, R2, x64, 64 bits, SP2, Service Pack 2, WSUS, 0x8007007f, licence, problem, error, prevent, accurately, check, license

2007-03-27 UPDATE Only 5 days have passed since the original date of this post and I have noticed a big amount of traffic (compared to my historic) directed by Google regarding this Error 0x8007007f. It seems it is a relative common problem but I would like to have some feedback about it: If your problem is fixed only with a reboot, I would like to hear about it; if it does not, I would like you to drop some lines too; if you solved your problem in any other way, it would be interesting for everybody to know. Thanks.

2007-04-07 UPDATE Due to the limited feedback I have received, I am investigating this issue on myself (even though my servers do not show this error anymore). It seems there is a workaround for this error, in Microsoft's Knowledge Base 914232: You may receive error code 0x80004005 or other error codes when you try to start a Windows XP-based computer:

Error code 0x8007007f or error code 0x8007007e
This problem frequently occurs after you upgrade a service pack. After you upgrade, there appears to be a corrupted file, a missing file, or a file mismatch.
[...]
Workaround for error code 0x8007007f or error code 0x8007007e
To work around this problem, uninstall the service pack that you installed. Then, reinstall the service pack.

This does not seem to be in contradiction with what happened to me: If the server, for whatever the reason, did not reboot, it seems feasible that this error 0x8007007f appears because of a file mismatch (since the reboot did not occur the files that were in use were not replaced).

Before you uninstall and reinstall SP2, I would check if the reboot did take place and, if not, doing a remote reboot of the server might solve the problem.

Top 10 searches that lead to this blog during february '07

Keywords	Visits	Blog entry
mssql$microsoftsmlbiz	10	Showing posts with label business contact manager
messenger sharing folders usn journal reader service	9	Microsoft Live Messenger released
sql server cte csv	9	INNER JOIN with a comma separated values CSV field
fwsrv	7	ISA Server 2004: fwsrv stopped responding to all requests
sqlagent$microsoftsmlbiz	7	Outlook 2003 Business Contact Manager served by SQL Server 2005
could not find row in sysindexes for database id 7, object id 1, index id 1. run dbccchecktable on sysindexes.	6	Outlook 2003 Business Contact Manager served by SQL Server 2005
"msdewriter" informó acerca de un error 0x800423f4. esto forma parte del estado del sistema. la copia de seguridad no puede continuar.	5	VSS & ntbackup errors
schema_option	5	How to deploy foreign keys to subscribers that are using queued updates
select permission denied on object 'sysobjects', database 'mssqlsystemresource',schema'sys'	4	Showing posts with label sql server.
microsoftsmlbiz	4	Showing posts with label business contact manager.
Totals:	1277

It might seems not to be much visits at first glance, but if you consider that this blog has only 32 entries up to now, they are rather technical, and it has less than 9 months old... well, maybe you change your mind. Thanks to you all. I am willing to hear your comments.

How to convert several rows into a CSV string

Scenario

I recently posted just about the opposite (see INNER JOIN with a comma separated values CSV field ) and now I want to show how to create a CSV list when you have several rows to merge, grouping by some criteria. Let's suppose you have a set of data such as:

F1  F2  Item
-----------------
1  4    A
1  4    B
3  2    A
3  3    C
5  1    B
5  4    A
5  4    E
5  4    F
6  1    E
6  1    W

And you need to generate something like:

F1  F2  ItemsList
-----------------
1  4    A, B
3  2    A
3  3    C
5  1    B
5  4    A, E, F
6  1    E, W

Solution

This procedure is based on the information I found on Converting Multiple Rows into a CSV String (Set Based Method) but tuned to be able to group by a set of fields, not only one:

IF NOT OBJECT_ID('tempdb..#Source') IS NULL
  DROP TABLE #Source
IF NOT OBJECT_ID('tempdb..#t') IS NULL
  DROP TABLE #t

SELECT 1 F1, 4 F2, 'A' Item INTO #Source
UNION ALL SELECT 1, 4, 'B'
UNION ALL SELECT 3, 2, 'A'
UNION ALL SELECT 3, 3, 'C'
UNION ALL SELECT 5, 1, 'B'
UNION ALL SELECT 5, 4, 'A'
UNION ALL SELECT 5, 4, 'E'
UNION ALL SELECT 5, 4, 'F'
UNION ALL SELECT 6, 1, 'E'
UNION ALL SELECT 6, 1, 'W'

CREATE TABLE #t (
  F1 int not null,
  F2 int not null,
  Item varchar(7) not null,
  ItemsList varchar(8000)
)

INSERT INTO #t (F1, F2, Item)
  SELECT TOP (100) PERCENT F1, F2, Item
  FROM #Source
  ORDER BY F1, F2, Item

DECLARE
    @List varchar(8000),
    @F1_Last int,
    @F2_Last int

SELECT
    @List = '',
    @F1_Last = -1,
    @F2_Last = -1

UPDATE #t
  SET
    @List = ItemsList =
      CASE
        WHEN (@F1_Last <> F1) OR
             (@F2_Last <> F2)
          THEN Item
        ELSE @List + ', ' + Item
      END,
    @F1_Last = F1,
    @F2_Last = F2

SELECT TOP (100) PERCENT F1, F2,
  CASE
    WHEN LEN(MAX(ItemsList)) > 512
      THEN CONVERT(varchar(512), LEFT(MAX(ItemsList), 509) + '...')
    ELSE CONVERT(varchar(512), MAX(ItemsList))
    END AS ItemsList
FROM #t
GROUP BY F1, F2
ORDER BY F1, F2
GO

Regarding efficency I must say that using this approach in my production environment, where the original internal query (real table instead of #Source temporary table) would return near 5800 rows, 5 columns, needed grouping by 4 of them (integers) and creating a list combining the fifth column (varchar(512)), this procedure does the job in just 3 seconds, whereas our old iterative-cursor-based procedure took more than 23 seconds to complete.

Outlook 2007 Business Contact Manager served by SQL Server 2005

My last post dealed with installing BCM 2003 on a remote dedicated server; in this case, the same subject but related to the new Office Outlook 2007 with Business Contact Manager is addressed here. Microsoft has this procedure fully documented on Outlook 2007 Document: Deploying Outlook 2007 with Business Contact Manager in a Remote Database Configuration

Outlook 2007 with Business Contact Manager uses a SQL Server 2005 database for storing its data. By default, configuring Outlook 2007 with Business Contact Manager with any Outlook profile creates a SQL database on the local computer. Instead of creating a database on the local computer, you can choose to connect to an existing database on a remote server.

The main quiz of the document is installing a named instance called MSSMLBIZ and set up to listen to TCP port 5356. The documents talks about moving the database data using a backup and a restore, but if you want to try other procedure, you can simply:

1. Stop MSSQL$MSSMLBIZ service running on the original PC
2. Copy database files (called MSSmallBusiness.ldf and MSSmallBusiness.mdf by default on BCM2007) to the new coputer that will be serving them
3. Attach the database files (using sp_attach_db or the IDE in VS2005)
4. Configure proper permissions, open ports, etc.

The most useful point in the former Microsoft document (IMHO) is the one that explains how to Use a database backup file from a previous version of Business Contact Manager for Outlook on a remote server.

Users may have used a previous version of Business Contact Manager for Outlook 2003, and may have created a backup of their database from that previous version. Specifically, the backup file may have been created by using the user interface and it may be in an .msbcm file or a .sbb file. This section describes how these backup files may be restored to a Business Contact Manager 2007 database on a remote server.

Happy upgrading ;)

Outlook 2003 Business Contact Manager served by SQL Server 2005

When you install Outlook 2003 with Business Contact Manager Update , a named instance of MSDE is installed (under %PROGRAMFILES%\Microsoft SQL Server\MSSQL$MICROSOFTSMLBIZ ). If you are the only one to work with your contact database it will be fine, but if you plan to share the database with your coworkers in a workgroup or domain, either you need to leave your computer permanently online or need to move the database files to a real server. In this document I explain how to move your contacts database to a remote server running SQL Server Express 2005 so that you could benefit from not being overloaded with having to manage and run MSDE on your computer, being able to restart or turn off your computer without advising your collegues, scheduled backups and, in summary, all the benefits that you could achieve having your data served by a dedicated computer instead of your personal one.

1. On the computer that will be used as server, install a named instance of SQL Server Express 2005 called SERVERNAME\microsoftsmlbiz, enable it for network access and make it listen to tcp port 56183 (tcp port 1433 is the default)
2. Stop your local MSDE instance (net stop MSSQL$MICROSOFTSMLBIZ from your command prompt)
3. Copy your MSBusinessContactManager.ldf and MSBusinessContactManager.mdf files along to the server that will be hosting them. The files on your computer are located under: %USERPROFILE%\Local Settings\Program Data\Microsoft\Business Contact Manager\ The destination directory on the server that will be hosting the database depends of your configuration, but it can be something like: %PROGRAMFILES%\Microsoft SQL Server\MSSQL.3\MSSQL\Data
4. Once you have those two file on the destination server, attach the database to the server and grant proper permissions to users.
5. For every client that was connecting to your MSDE instance, open Outlook and configure it to use the remote server instead.
6. Restart Outlook.
7. You can safely set MSSQL$MICROSOFTSMLBIZ and SQLAgent$MICROSOFTSMLBIZ services startup type to Disabled or Manual for all computers that are conecting to a remote database.

Since you can only create new databases on the local computer (Using Create or select a new database menu), if you want to create the database from scratch (no data) using SQL Server 2005, you will need to disable/stop MSDE service, install SQL Server 2005 (or Express) on your local computer (a named instance called MICROSOFTSMLBIZ and listening to tcp port 56183), and then you can use Outlook wizard to create the new database on your local server that will be SQL Server 2005 (or Express) instead of MSDE. You can then move that database file (using sp_detach_db y sp_attach_db to the final server).

Remember that database files created with the newer versions of SQL Server 2005 cannot be attached to older engines (i.e. SQL Server 2000 or MSDE), they are not backwards compatible. If you try to attach a MSBusinessContactManager database created with SQL Server 2005 to a MSDE engine, you will get the following error (in ERRORLOG file):

In spanish:

Error: 602, Severity: 21, State: 50
No se encuentra la fila en sysindexes de la base de datos con Id. 7, Id. de objeto 1, Id. de índice 1. Ejecute DBCC CHECKTABLE en sysindexes..

In english:

Error: 602, Severity: 21, State: 50
Could not find row in sysindexes for database ID 7, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes..

This is important because if you ever need to upgrade to Office 2007, you will need a way to move the database back to the local MSDE engine to allow the upgrade procedure reuse your existing data as part of the upgrade.

Process Explorer for Windows

Previously developed by Sysinternals (acquired by Microsoft in July, 2006), Process Explorer for Windows v10.21 was released last November 1st, 2006. This is a must-have-tool for admins. Whenever you need to know which process has open which files or what DLLs are being used by whom, you will need it.

http://www.microsoft.com/technet/sysinternals/utilities/ProcessExplorer.mspx Simple to download, install and run: a single file sized 3.5Mb (1.5Mb zipped).