| When you upgrade from Office 2000 to 2003, your existing MSDE 1.0 installation remains intact, and your existing ADP, DAP, and linked Jet databases continue to connect to the local instance of MSDE 1.0 installed by Office 2000 or the remote server specified by the front-end application's ODBC or OLE DB/ADO connection string. When you open the Access 2003 version of NorthwindCS.adp, however, you receive the message shown in Figure 32.8. The Access 2000 version of the NorthwindCS database's Employees table stores bitmaps in the Photo field; the Access 2003 version has links to nine EmpIDx.bmp files in the ...\Office11\Samples folder. Figure 32.8. This message appears when you open Access 2003's NorthwindCS.adp sample application connected to the MSDE 1.0 version of the NorthwindCS sample database.  You can continue to use MSDE 1.0 with Access 2003, but doing so prevents you from taking advantage of the many new and important features of SQL Server 2000, such as full support for declarative referential integrity (DRI), linked servers, and extended properties that support Access 2002+ features such as lookup fields, subdatasheets, and input masks. Microsoft recommends that you remove MSDE 1.0 before installing the Desktop Edition. This recommendation applies only if none of the following three statements are true: You have a substantial investment in customizing SQL Server 7.0 features, such as publish/subscribe replication, database roles, and other management options. You have many individual server login and database user accounts to which you have granted specific database roles or individual database object permissions. You're using SQL Server authentication for MSDE 1.0 login and database user accounts. Office 2003 installs MSDE 2000 with integrated Windows (NTLM) authentication only.
If none of the preceding three conditions apply to you, skip to the "Removing MSDE 1.0 and Installing SQL Server 2000" section. Otherwise, choose one of the upgrade options described in the following two sections. Upgrading from MSDE 1.0 to SQL Server 2000  | The version of MSDE 2000 on the Office 2003 installation disk is SQL Server 2000 Service Pack (SP) 3. SP3 removes earlier versions' vulnerability to the infamous "Slammer" worm. MSDE 2000's new Setup.exe program doesn't open dialogs to let you choose installation options. Instead, you supply command line parameters to tell Setup.exe that you want to upgrade rather than replace MSDE 1.0. Upgrading an instance of MSDE 1.0 to SQL Server 2000 SP3 is very similar to that for installing a new version of MSDE 2000. |
 To review the installation process for a new MSDE 2000 instance, see "Running the MSDE Setup Program," p. 48.
Caution If you're running MSDE 1.0 under Windows XP or 2000+ and you've specified Windows integrated and SQL Server (mixed) security, you lose SQL Server security during the upgrade to MSDE 2000. You can change the security settings with SQL Server Enterprise Manager, if installed, to re-enable SQL Server security.
To upgrade MSDE 1.0 to MSDE 2000 SP3, do the following: Log on as an administrative user, and use SQL Server Service Manager to stop MSDE 1.0. Navigate to the \MSDE2000 folder of the Office System 2003 distribution CD-ROM or a network installation share. Double-click Msde2ks3.exe to open the License Agreement dialog, and click I Agree. In the Installation Folder dialog, accept the default location, C:\Sql2ksp3, to extract and copy the installation files to the folder. Click Yes to create the folder. Extracting the files takes about a minute. Choose Start, Run, type cmd in the Open text box, and click OK to open a Command window. Type cd \sql2ksp3\msde and press Enter. Tip If you want to learn more about SQL Server 2000 SP3, open Sp3readme.htm in your \Sql2ksp3\MSDE folder before proceeding. Type setup.exe upgrade=1 blanksapwd=1 and press Enter. The installation process takes a few minutes. Reboot your computer to restart SQL Server Service manager. Follow the instructions for resetting the blank sa password in the next section.
Note  | The following Microsoft Knowledge Base articles contain information on upgrading MSDE 1.0 to MSDE 2000: Q282017, "PRB: SQL Server 2000 Help File Contains Confusing Information About the Desktop Engine Setup"; Q290627, "ACC2002: Microsoft SQL Server 2000 Desktop Engine Is Not Installed by Office XP Setup;" and Q271887, "PRB: Desktop Engine Upgrade of MSDE 1.0 Fails." If you encounter a problem upgrading MSDE 1.0 to 2000, check article Q271887 for instructions to generate an installation log file. Q301413, "Configuring SQL Server 2000 Desktop Engine," has a link to download a white paper on MSDE 2000 configuration. The information in the articles, which were written for Access 2002, also applies to Access 2003. |
Setting or Resetting a System Administrator Password A empty (blank) sa password poses a serious security risk, because anyone with access to your computer can gain full administrative control over your databases. Access 2003 includes a feature that permits members of the local Administrators group to enable the sysadmin (sa) account with an empty password. You change the password in a second operation. Note Adding a password to the sa account is important, even if you install MSDE 2000 with Windows authentication only. A determined person with access to your computer might be able to change a Registry setting to enable SQL Server security.
To add the sa account for SQL Server security, do the following: Open an Access data project that connects to a server that doesn't have SQL Server security enabled. You must run Access 2003 on the machine that hosts the SQL Server instance to change the sa password. Choose View, Server Properties to open the Server Properties dialog. Mark the Enable System Administrator (SA) User Name check box (see Figure 32.9), and click OK to close the dialog. (If the sa account exists, the check box is disabled. In this case click Cancel.) Figure 32.9. Enable SQL Server security for the sa account in Access 2003's Server Properties dialog.  Acknowledge the message that recommends changing the system administrator password. After a few seconds, the Logon dialog opens. Accept the default sa login ID and empty password, and click OK to log in with SQL Server security.
Adding the sa account and logging in to MSDE 2000 changes the selected security option of the DataLink Properties dialog from Windows NT to SQL Server security with sa as the username and an empty password. To set or reset the password, follow these steps: Choose Tools, Security, Set Login Password to open the Change Password dialog. If the sa password is empty, accept the default (empty) value in the Old Password text box. Otherwise, type the password you want to reset. Type and confirm the new password in the New Password and Verify text boxes (see Figure 32.10) and click OK. Figure 32.10. Use the Change Password dialog to add or change a password for the system administrator (sa) account.  If you used the Server Properties dialog to add the sa account, open the DataLink Properties dialog, select the Use Windows NT Integrated Security option, and click OK.
Changing an existing password affects all ADP that rely on SQL Server security. In this case, you must open each project's DataLink Properties dialog and change to the new password. Installing a Named Instance of SQL Server 2000 SQL Server 7.0 doesn't support named instances, so MSDE 1.0 remains the primary instance of SQL Server on your computer. You can create a side-by-side installation of the two versions, but doing so requires creating a named instance of MSDE 2000 on the machine. Connecting to a named instance of MSDE 2000 requires substituting the computer's NetBIOS name and server instance name, as in ServerName\InstanceName, wherever you ordinarily specify ServerName. Tip Access 2003's "Install and configure SQL Server 2000 Desktop Engine" online Help topic contains the following note: "Be sure to read the Readme.txt file in the \MSDE2000 folder for late-breaking information." Much of the information regarding MSDE in the Readme.txt file applies to the conventional installation from SQL Server CD-ROMs, not the "no options" installation of SP3 from the \MSDE2000 folder of the distribution CD-ROM.
To add a named instance of MSDE 2000 MSDE2K for this example to a computer with MSDE 1.0 installed, do the following: Perform steps 1 5 of the earlier "Upgrading from MSDE 1.0 to SQL Server 2000" section. Type setup.exe instancename=MSDE2K blanksapwd=1 and press Enter. The installation process takes a few minutes. You can substitute another short name for MSDE2K, if you want, but don't include spaces or punctuation symbols in the name. After installation completes, click OK to acknowledge the message, and reboot your computer. Verify installation of the new instance in Windows XP/2000+ by opening Control Panel's Administrative Tools\Services tool and scrolling to find MSSQL$MSDE2K (see Figure 32.11). Alternatively, right-click My Computer, choose Manage to open the Computer Management snap-in, expand the Services and Applications node, and click Services to display the list. Figure 32.11. Windows XP displays the new instance of MSDE 2000, which runs under the LocalSystem account by default. In the SQL Server service, a dollar sign ($) separates the server and instance name.  Double-click the MSSQL$MSDE2K item to open the MSSQL$MSDE2K Properties (Local Computer) dialog. Click the Logon tab, select the This Account option, and type the name of a local Administrators account (usually Administrator), the password, and the password confirmation (see Figure 32.12). Click OK to change the logon account for the instance, and acknowledge the two message boxes. (You don't need to stop and start the service before completing the remaining steps of this procedure.) Figure 32.12. Change the logon account for the new SQL Server 2000 instance to an account with membership in the local Administrators group.  Repeat step 4 for the SQLAgent$MSDE2K service, and close the Services tool. Test the new SQL Server 2000 instance by launching Access 2003, if necessary, and opening a project with a connection to the MSDE 1.0 database, such as the upgraded NorthwindCS project. Choose Tools, Database Utilities, Transfer Database to start the Transfer Database tool. In the first dialog, select the SQL Server instance (OAKLEAF-XP1\MSDE2K for this example) in the What SQL Server Would You Like... list and type the name for the new database in the text box (see Figure 32.13). Alternatively, clear the check box, and type your login ID and password in the two text boxes for SQL Server security. Figure 32.13. Test the new SQL Server 2000 instance by copying an SQL Server 7.0 database and its objects with the Transfer Database tool.  Click Next, and then click Finish to start the transfer process. Transferring the database creates a copy in the destination instance. In the current or a new Access project, choose File, Connection to open the Connection page of the Data Link Properties dialog. In the Select or Enter a Server Name list, select the new SQL Server 2000 instance (see Figure 32.14), and click OK. Figure 32.14. Installing a new instance adds the instance name to the Select or Enter a Server Name list. Select the new instance for testing with the Access project.  Verify that your current Access 2003 project behaves as expected with the new data source, and then return to the original server connection, if you want.
Any ADP you connect to databases in the new server instance can take advantage of Access 2003-specific extended properties, but you must set the property values manually in Table Design view in the da Vinci toolset. To review using the da Vinci table designer, see "Working with SQL Server Tables in the Project Designer," p. 810.
Removing MSDE 1.0 and Installing SQL Server 2000 If you've made a significant investment in customizing MSDE 1.0, the better choice is to retain your MSDE 1.0 instance and install a named instance of SQL Server 2000. If you decide to migrate your ADP to SQL Server 2000, do the following before removing MSDE: Back up your data (.mdf) and log (.ldf) files, plus at least master.mdf, mastlog.ldf, msdbdata.mdf, and msdblog.ldf. Removing MSDE 1.0 doesn't delete production .mdf and .ldf files, but it does delete the existing master, msdb, model, and tempdb database and log files. Loss of the model and tempdb databases isn't important. If you've added custom settings for MSDE 1.0 features, such as publish/subscribe replication, server roles, SQL Server logins, and the like, make sure you document them thoroughly. You must manually reestablish your server-wide MSDE 1.0 settings in SQL Server 2000.
Removing MSDE 1.0 isn't as simple as the "Install and configure SQL Server Desktop Engine" online Help topic suggests. Do the following to remove MSDE and all its components, including SQL Service Manager from your computer: Open SQL Server Service Manager and clear the Auto-start Service when OS Starts check box for MSSQLServer and SQLServerAgent. Delete the shortcut to Service Manager in your ...\Start Menu\Programs\Startup folder(s). The location of the Service Manager shortcut depends on your operating system. Use Search to search for service manager, and delete all Service Manager shortcuts you find. Reboot your computer and verify that the Service Manager icon no longer appears in the tray. Choose Start, Programs, MSDE, Uninstall MSDE to start the uninstall process. Click Yes to confirm you want to continue with removal, click OK when uninstallation completes, and reboot your computer. Perform a new installation of MSDE 2000 SP3 as described in Chapter 1's "Running the MSDE Setup Program" section. Reboot your computer when setup completes. For additional security, add and password-protect the sa account, as described in the earlier "Setting or Resetting a System Administrator Password" section.
Removing MSDE deletes the Programs, MSDE menu; MSDE 2000 doesn't add a menu. Other SQL Server 2000 editions add a Microsoft SQL Server menu to replace the MSDE menu. Reattaching and Upgrading MSDE 1.0 Databases ADP and ODBC-linked Jet front ends lose their connections to existing MSDE 1.0 databases when you migrate to SQL Server 2000, so you must reattach the databases to your front-end applications. When you attach the MSDE 1.0 .mdf file, SQL Server upgrades the database to the SQL Server 2000 version. This process is not reversible; the upgraded database files no longer are operable with MSDE 1.0. Access Data Projects To reattach and upgrade the MSDE 1.0 files for ADP, do this: Open the .adp file in Access 2003. The project displays "Disconnected" in the Database window's title bar. Choose File, Connection to open the Data Link Properties dialog. Accept (local) or select the server name in the Select or Enter a Server Name list. Select the Attach a Database File as a Database Name option, and type the database name for your project in the first text box.  Click the Browse button to open the Select SQL Server Database File dialog and navigate to the folder that holds the MSDE 1.0 .mdf file for the database. The default location of MSDE 1.0 .mdf and .ldf files is C:\MSSQL7\Data. Select the file and click Open to add the file name to the Using the Filename text box (see Figure 32.15).
Figure 32.15. Specify the database name and .mdf file to upgrade MSDE 1.0 databases to MSDE 2000 and attach them to ADP.  Click Test Connection to upgrade the attached file, which takes a few seconds or more, depending on the size of the database. Click OK to close the Data Link Properties dialog, and verify that your project works correctly with the upgraded database. Repeat steps 1 through 7 for each project having an upgraded database.
Note Attaching the files moves the .mdf and .ldf files from their original location normally \MSSQL7\Data to the standard location for SQL Server 2000 file \Program Files\Microsoft SQL Server\MSSQL\Data.
After you've attached the upgraded tables, they appear in the Select the Database on the Server List. If you maintained the original database name and security option, you don't need to modify the Data Link Properties entries for other front ends that connect to the upgraded database. Jet Front Ends Linked with ODBC ODBC DSNs that link Jet front ends to MSDE 1.0 databases fail after migrating to SQL Server 2000. If the Jet front ends share SQL Server 2000 databases you've upgraded for ADP, the upgrade process of the preceding section makes the DSNs operable. You must attach and upgrade other MSDE 1.0 databases to enable connectivity with existing ODBC DSNs. If you have SQL Server Enterprise Manager, you can attach and upgrade the MSDE 1.0 files by doing this: Open Enterprise Manager, and expand the SQL Server Group node to display the server list. Right-click the Databases node of the migrated server, and choose All Tasks, Attach Database to open the Attach Database - ServerName dialog. Click the browse button to open the Browse for Existing File - ServerName dialog, select the .mdf file to attach, and click OK. Accept or change the database name in the Attach As text box (see Figure 32.16). Figure 32.16. Use Enterprise Manager's Attach Database tool to reattach and upgrade MSDE 1.0 databases you connect to Jet front ends with ODBC.  Click OK to attach the database and click OK again to acknowledge the "success" message.
If you don't have SQL Server Enterprise Manager, you can attach and upgrade MSDE files by creating a temporary project typically Adp1.adp and performing the steps in the preceding "Access Data Projects" section for each database you need to upgrade. |
