Interoperating with Microsoft Data Engine

Published: October 1, 2001

Visimation, Inc.

Microsoft Certified Partner

Applies to:

Microsoft Data Engine (MSDE) 1.0

Microsoft Data Engine (MSDE) 2000

Microsoft Visio Professional 2002

Microsoft Visio Enterprise Network Tools 2002

Microsoft Visio Enterprise 2000

Summary: This article describes issues reported when integrating products with MSDE 1.0. The Microsoft products discussed in this article include Visio 2000 AutoDiscovery and Layout (AD&L), Microsoft Project Central, SharePoint Team Services, and Windows 2000 Terminal Services. The article also describes interoperability between MSDE 2000 and the AD&L solution from Visio Enterprise Network Tools 2002.

For the latest information, please see https://www.microsoft.com/office/visio/prodinfo/default.mspx

On This Page

Introduction
Products Integrating with MSDE
Planning: AD&L Solution from Visio Enterprise Network Tools, MSDE, and SQL Server
Recommended Reading
Appendix A

Introduction

Microsoft® Data Engine (MSDE) is a client/server-based local data storage tool compatible with Microsoft SQL Server™. MSDE runs under Microsoft Windows® NT 4.0 or later and Windows 95 or later. It is designed and optimized for use on smaller computer systems, such as a single user computer or small workgroup server. MSDE is based on the same data engine as SQL Server, and because of this, client/server applications can integrate with either MSDE or SQL Server without changes to the application. MSDE does not include administrative tools or reference material, which can make administering MSDE more difficult than SQL Server. Although MSDE is functionally equivalent to SQL Server, MSDE is not as feature-rich as SQL Server. Products like Microsoft Visio®, Microsoft SharePoint® Team Services, and Microsoft Project Central include MSDE on their product distribution as MSDE can be freely redistributed.

MSDE includes an Open Data Base Connectivity (ODBC) driver that enables ODBC compliant applications and databases like Siebel Systems to interface with the MSDE database. The AutoDiscovery and Layout (AD&L) solution from Microsoft Visio Enterprise Network Tools uses ActiveX® Data Objects (ADO) to connect to the MSDE and SQL Server 2000.

For more information on MSDE, see the Recommended Reading section of this article.

Products Integrating with MSDE

MSDE is integrated with these Microsoft applications:

• Microsoft Visio 2000 Enterprise Edition AutoDiscovery & Layout (AD&L) solution

• AD&L solution from Microsoft Visio Enterprise Network Tools 2002

• Microsoft SharePoint Team Services (a Microsoft FrontPage® Server Extensions 2002 companion product)

• Microsoft Project Central (a Microsoft Project 2000 companion product)

• Microsoft Application Center

The following products ship MSDE on their product CD and can use MSDE as a database:

• Microsoft Access

• Microsoft Office 2000

• Microsoft Visual Studio® 6.0

This section describes issues that may occur when Visio, Visio Enterprise Network Tools, Siebel Systems, SharePoint Team Services, and Microsoft Project Central products interoperate with MSDE 1.0 or MSDE 2000. Also described is an issue encountered if you attempt to install MSDE via Microsoft Terminal Services. The Recommended Reading section contains pointers to documents discussing interoperability between Microsoft Access and MSDE.

AutoDiscovery and Layout

Visio Enterprise 2000 AD&L and the AD&L solution from Visio Enterprise Network Tools 2002 use MSDE to store network information discovered during the AutoDiscovery process. Visio Enterprise installs MSDE when AutoDiscovery and Layout is installed if neither MSDE nor SQL Server reside on your system. However, AD&L will use SQL Server by default if SQL Server is installed.

Visio Enterprise 2000 AD&L requires a minimum of MSDE 1.0 or SQL Server 7.0. MSDE 2000 or SQL Server 2000 is recommended for the AD&L solution from Visio Enterprise Network Tools.

This section addresses the following topics:

• Localized versions and MSDE/SQL Sort ID, Locale ID, and Collation properties

• MSDE and the SQL Server system administrator (sa) password

• AD&L fails to run when Siebel Systems installs MSDE 1.0

Localized Versions and MSDE/SQL Properties

International users can install either an English or a localized version (Japanese, for example) of MSDE or SQL Server for use with Visio 2000 AD&L and the AD&L solution from Visio Enterprise Network Tools. If MSDE 1.0 or SQL Server 7.0 is installed as a localized version, the database cannot later be reconfigured to act as a different localized version or as an English version. This is because MSDE and SQL Server treat the localized properties of Sort ID and Locale ID (MSDE and SQL Server 7.0) as global properties across all database installations on a single server.

The Sort ID and Locale ID properties of MSDE 1.0 and SQL Server 7.0 are set at product installation and cannot be changed. Sort ID and Locale ID define the code page for text stored in the database and how the data is sorted. The code page is a means of providing support for character sets and keyboard layouts used in different countries, and define special sorting routines for different languages. These parameters must be set appropriate to the locale (for example, English, Japanese) in order for AD&L to properly store and retrieve text data from the database. Under MSDE 1.0, the Japanese localized version defaults to Sort ID, Code Page, and Locale ID values that specify a Japanese code page for the entire database.

MSDE and SQL Server 2000 installations default to a Collation setting that is based on the local setting for the Windows implementation. For example, if you install MSDE 2000 on a Japanese localized Windows installation, the Collation property is configured by default for the Japanese code page setting. However, if you install MSDE 2000 on an English Windows implementation, the Collation setting is configured for the English code page setting.

With MSDE and SQL Server 2000, you can have multiple installations of a database on the same system and each instance can have a separate Collation setting because the Collation parameter is now specific to a single database installation. This means you can have a different Collation value for each instance, and you can move a database initially configured to work with a localized version of MSDE to an English MSDE/SQL 2000 version. It is also possible to have multiple Collation settings in one database.

MSDE and the SQL Server System Administrator Password

An interoperability problem exists with MSDE 1.0 and Visio 2000 AD&L when you set the SQL Server system administrator account (called sa) password from the default blank value to any other value. This problem is fixed in Visio 2000 Enterprise Edition SR1 release of AD&L.

The sa account password is initially blank when you install SQL Server or MSDE on your system. If AD&L cannot connect to the database server with the credentials stored when Visio 2000 or 2002 was initially installed, you are prompted to choose the authentication method to use to connect to MSDE or SQL Server. You can choose either Microsoft Windows Authentication Mode or Mixed Mode (Microsoft Windows Authentication and SQL Server Authentication). When you select Windows Authentication, Visio uses the credentials of the current user account. If you select Mixed Mode (or SQL Server Authentication), Visio prompts you to enter the SQL Server system administrator account name (sa) and the password assigned to the sa account. Visio attempts to connect to the database using Windows Authentication Mode if possible, and if unsuccessful, connects via SQL Server Authentication using the credentials you provided for the sa account.

The code performing the authentication function between Visio 2000 and MSDE 1.0 was initially written to assume that a user had not changed the default sa password (blank). If the user changed the sa password to a value other than blank, Visio failed to connect to MSDE.

Microsoft Project Central was the first product to encounter this problem because Microsoft Project Central prompts the user to change the SQL sa password during installation.

AD&L Fails when Siebel Systems Installs MSDE 1.0

If Visio 2000 Enterprise Edition AD&L and Siebel Systems reside on the same system, and Siebel Systems installed MSDE 1.0, AD&L can fail to function properly.

Under MSDE 1.0 and SQL Server 7.0, configuration parameters are global across all database installations. Siebel Systems configures MSDE for case-sensitive operation when Siebel installs MSDE 1.0; AD&L expects the database to be configured as case-insensitive. The presence of case sensitivity caused AD&L to fail when performing a specific database reference (for example, the column IfIndex of the PhysicalInterfaceTable) when storing and retrieving data from MSDE.

If Visio 2000 installs MSDE, both MSDE and Siebel Systems function properly. You can obtain a fix to this problem through Microsoft Product Support Services (PSS). This problem does not exist on Visio 2002 with MSDE or SQL Server 2000.

Microsoft SharePoint Team Services

SharePoint Team Services is a superset of the Microsoft FrontPage Server Extensions 2002 product. SharePoint includes the features available with the FrontPage Server Extensions, plus new workgroup features for Web publishing and team communication, enabling administrators to create, author, and administer Web sites for teams and projects.

SharePoint can use either MSDE 1.0 or SQL Server 7.0 (or greater), and prompts you to install MSDE if SQL Server is not already installed on the local machine. You can elect to use a remote database server if you prefer not to install either SQL or MSDE on your local machine (see SharePoint Setup instructions for more detail on this feature).

Mixed Mode Authentication

SharePoint requires Mixed Mode authentication. When SharePoint installs MSDE on a system, the install program randomly generates a password for the SQL Server sa account. This password is stored in the Windows registry and can be changed at any time via the SharePoint HTML Admin interface. If you install SharePoint Team Services on a Web server that is running SQL Server 2000, you must configure SQL Server 2000 to use SQL Server Authentication (Mixed Mode, for example). If SQL Server 2000 is using Windows Authentication Mode, you will see the following error message when extending Web sites with SharePoint Team Services "Login failed for user <username>. Reason: Not associated with a trusted SQL Server connection. (Error code: 18452)". This error is documented in the SharePoint Team Services Readme file that ships on the product CD.

As described in the MSDE and the SQL Server system administrator (sa) password section of this article, Visio 2000 AD&L expects the sa password value to be blank. If Visio and SharePoint reside on the same system, install either Visio 2000 Enterprise Edition SR-1 or Visio Professional 2002 and MSDE or SQL Server 2000 to resolve this interoperability issue.

Strong Authentication

If you install SharePoint Team Services on a Web server configured to require strong passwords, you will see the following message during the MSDE installation: "The password does not meet the policy requirements. Check the minimum password length, complexity, and history." You can ignore this message and continue through the setup process. This error is documented in the SharePoint Team Services Readme file.

SharePoint version 2 will support either MSDE 2000 or SQL Server 2000.

SharePoint Team Services Uninstalls MSDE 1.0

MSDE frequently ships on a product's CD and is typically installed by the first product to use MSDE on a machine. Visio, Siebel Systems, Microsoft Project Central, and SharePoint Team Services are examples of products that can install MSDE; other later product installations can use this same MSDE instance. Products can also remove MSDE from a system when you uninstall an application that uses MSDE.

SharePoint can use an MSDE instance installed by another product, for example, by Visio 2000. However, if you remove SharePoint using the Add/Remove Programs utility in the Control Panel, and SharePoint installed MSDE, SharePoint will automatically uninstall MSDE, even if other products are integrated with MSDE.

Microsoft Project Central

Microsoft Project Central Server installs and requires MSDE 1.0. If you are installing Microsoft Project Central Server on a system that already has MSDE 2000 installed, you either need to remove MSDE 2000 before you begin or complete the steps listed below for installing Microsoft Project Central without removing MSDE. During installation, if Microsoft Project Central determines that MSDE already exists your system, Microsoft Project fails to create the database it needs.

To install Microsoft Project Central without removing MSDE 2000

First run the SQL Server scripts provided on the Microsoft Project Central Web site, https://www.microsoft.com/office/visio/prodinfo/default.mspx to create and populate the MSDE database. Next, use these steps to install Microsoft Project Central with the Customize option:

1. Choose the MSDE or SQL Server database option.

2. Enter your machine name in the Server Name field.

3. Enter the same username and password that you used when you ran the SQL Server scripts.

4. Enter Project_CentralDB in the Database name field.

5. Click Test Connection and then click Install.

To remove MSDE 2000 before installing Microsoft Project Central

Follow the instructions found on the Microsoft Project Central Web site, https://www.microsoft.com/office/visio/prodinfo/default.mspx (for ease, these steps are included in this article's Appendix A). When you complete removing MSDE 2000, install Microsoft Project Central using the Install Now option.

Windows 2000 Terminal Services

Installation of MSDE, SQL Server 7.0, or any of the SQL 7.0 service packs from a remote Terminal Services Client onto a Windows 2000 server that is running Terminal Server Service, fails regardless of the client's operating system (Windows 95, Windows 98, Windows NT 4.0 Workstation, Windows NT 4.0 Server, or Windows 2000).

This problem is described in more detail in the Knowledge Base article PRB: Setup on Windows 2000 Fails Through Terminal Client.

Planning: AD&L Solution from Visio Enterprise Network Tools, MSDE, and SQL Server

AD&L has built-in features for backing up and restoring MSDE and SQL Server databases. This is unlike other applications that typically rely on a database administrative program like SQL Enterprise Manager for performing all administrative tasks. Consider the following requirements before you install the AD&L solution from Visio Enterprise Network Tools:

• AD&L creates two databases the first time AD&L is loaded in Visio 2000 or Visio Enterprise Network Tools 2002 with the predefined names of ADLClassmap and ADL. The AD&L operation fails if either of these names already exists in MSDE at the time AD&L is installed. AD&L must be able to create the ADLClassmap and ADL databases in MSDE.

  The ADLClassmap database is created first and contains AD&L internal data; no user data is stored in the ADLClassmap database. The ADL database is created next and is the default database name assigned by AD&L to the first database created for storing network information discovered during an AutoDiscovery process. You can create additional databases in AD&L but you must adhere to the standard Microsoft Transact-SQL identifier naming rules enforced by MSDE and SQL Server, and described in the following articles

  • MSDN, Platform SDK documentation, Data Services, SQL Server, Transact-SQL Reference Create Database. https://msdn.microsoft.com/library/default.asp?URL=/library/psdk/sql/ts_create_1up1.htm

  • MSDN, Platform SDK documentation, Data Services, SQL Server, Accessing and Changing Relational Data, Using Identifiers. https://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_03_6e9e.asp

• Databases created with MSDE 1.0 or SQL Server 7.0 will work with MSDE and SQL Server 2000. However, MSDE 2000 and SQL Server 2000 databases are not backward compatible with MSDE 1.0 and SQL Server 7.0.

• The AD&L solution from Visio Enterprise Network Tools and SQL 2000 can be compute intensive, consuming a significant amount of memory and CPU during the AutoDiscovery process. An administrator should plan for proper system configurations per the Visio 2002 system guidelines. Keep this in mind if you are planning to install MSDE and AD&L from Visio Enterprise Network Tools on a server already containing other resource intensive applications.

Recommended Reading

• Microsoft Visio Product Site

• Microsoft Visio Developer Center on MSDN

• Microsoft Visio Technology Center on TechNet

• For developer information on deploying MSDE with your Microsoft Access projects (ADP), consult Microsoft Knowledge Base article 240293 - MOD2000: How to Deploy an Access Data Project That Includes the Microsoft Data Engine

• MSDN topic – About Microsoft Data Engine https://msdn.microsoft.com/library/default.asp?url=/library/en-us/off2000/html/acconaboutmsde.asp

• MSDN topic - Introduction to Microsoft Access 2000 Projects and the MSDE https://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acaccessprojects.asp

• Microsoft Knowledge Base article PRB: Setup on Windows 2000 Fails Through Terminal Client

• Microsoft Knowledge Base article 264637 - Visio 2000: SQL Password Prevents AutoDiscovery and Layout Solution from Loading

• Microsoft Knowledge Base article 286014 - Visio2000: Error Message: "An Error 1060 - The Specified Service Does Not Exist as an Installed Service..." When You Perform an AutoDiscovery and Layout

• Microsoft Knowledge Base article 263858 - Visio2000: AutoDiscovery and Layout Solution Does Not Detect a Running MSDE If the User Account Is Not an Administrator

• Microsoft Knowledge Base article 263859 - Visio2000: Error Message: The Solution Failed to Load. Please Make Sure MSDE is Properly Installed and Is Running

• Microsoft Knowledge Base article 264400 - Visio2000: What Is Fixed in the Service Release 1 (SR-1) Update for Visio 2000 (All Editions)

• Microsoft Knowledge Base article 264680 - Visio2000: "The Solution Failed to Load, Please Make Sure MSDE Is Properly Installed" Error When You Open AutoDiscovery and Layout Solution

• Microsoft Knowledge Base article 282234 - Visio2000: How To Completely Remove Visio 2000 from Microsoft Windows

Appendix A

The following instructions are an extract from a larger document found on the Microsoft Project Central Web site, and describe the steps necessary to create the MSDE database needed for Microsoft Project Central. You may need to follow these steps if MSDE is installed on your system prior to your installing Microsoft Project Central Server. During installation, if Microsoft Project Central determines that MSDE already exists your system, Microsoft Project fails to create its database.

https://www.microsoft.com/office/visio/prodinfo/default.mspx

Creating SQL, MSDE, or Oracle Database and Tables

To create a SQL Server, MSDE, or Oracle database and tables, complete the appropriate steps in the procedures below. For Microsoft SQL Server and MSDE, you can either choose a default installation of the database, or you can create your own database. For Oracle, you cannot create a default installation.

Note: Mixed Mode Authentication is required in order for the following database commands to work.

To create a default database and table installation for a SQL Server or MSDE

1. On the database server computer, go to the MS-DOS Prompt.

2. Change directories to the CD-ROM drive, and on the Microsoft Project CD, go to the folder \Pjcentrl\Isapi\1033 (or the appropriate folder for your language).

3. Type the following commands to create a database and tables in the database and to insert default records for the tables:

   osql -U <account name> -P <password> -i Crttable.sql 
   osql -U <account name> -P <password> -d <db name> -i Insdefsq.sql 
   osql -U <account name> -P <password> -d <db name> -i Insdefsi.sql 
   

For example, if the account name is *sa*, the password is ProjectPW, the commands would be:

<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">
osql -U sa -P ProjectPW -i Crttable.sql 
osql -U sa -P ProjectPW -d Project_CentralDB -i Insdefsq.sql 
osql -U sa -P ProjectPW -d Project_CentralDB -i Insdefsi.sql 

**Note:** The parameter letters U, P, d, and i are case sensitive.

If you experience errors when you run the scripts from the command prompt, check the access permissions of the user login in SQL Enterprise Manager. The user must have sufficient access permissions to create databases and tables, and to insert records in the tables.

About Visimation

Visimation is a Microsoft Certified Partner who specializes in Microsoft Visio consulting, custom development, training, and services.

https://www.visimation.com/