MSDE Configuration

MSDE configuration is a non trivial task unfortunately because Microsoft does not ship any tools to administer the server and because installation occurs essentially unattended without giving you installation options or prompts. Hence it generally is the developers responsibility to configure MSDE manually.

The rest of this topic describes how to manually configure MSDE.

Start by installing correctly!

Even if you are planning on using DbaMgr2k make sure that you install MSDE properly. By default MSDE installs with only Windows Security allowed. Windows auth is usually not appropriate for Web applications, so you have to override a few installation settings by manually running the setup program with a couple of flags:

SETUP SECURITYMODE=SQL  SAPWD=yourpassword

Alternately you can add the following into setup.ini before running the setup:

[Options]
SAPWD=yourpassword
SECURITYMODE=SQL

Note that setting security mode to SQL actually supports both SQL and Windows Auth. Once installation completes it does not automatically start the server, so you can do this either via the Services applet in Administrative Tools or by using the command line:

NET START MSSQLSERVER

Changing settings after MSDE is already installed

The Web Store Configuration Utility (which is accessible from the Program Menu Group) has an MSDE tab page that allows you configure MSDE security directly. It allows you to set the Authentication type (Windows Security or Mixed mode Sql Server & Windows Security). It also allows you to reset passwords for any Sql Server account.

In order to use this functionality you need to run the utility under and administrative account - the utility connects to MSDE or SQL Server via Windows Authentication. Once you bring up the utility and select the MSDE tab you should see something like this:

The current authentication mode will be indicated in the form and you can change it if necessary. Remember for Web operation you generally want to use Sql Server security.

To change the password of a user account, specify the name of the server (IP Address or Netbios or domain name) and then specify an existing password, followed by entering the new password twice for verification. Note that you can change a password only if you know the password to start with.

Manually changing settings

The configuration utility performs a couple of tasks behind the scenes. To change the Authentication mode you have to change a registry key:

HKLM\Software\Microsoft\MSSqlserver\MSSqlServer\LoginMode

and set this value to 2 (SQL Server & Windows) or 1 (Windows Authentication). Make sure to stop and restart the service after making this change:

NET STOP MSSQLSERVER
NET START MSSQLSERVER

To change the sa password, you can use the oSQL utility. Note you must be loggged on as an administrator to do this in the first place. Go to a Command prompt and type:

To log on via Windows Authentication:

oSQL -E

To log on via SQL Authentication

oSql -u sa

You will be prompted for a password. Once you're logged on you'll see a 1> prompt and you can type type:

sp_password @old = 'existingpass', @new = 'newpass',  @loginame ='sa' 
go

Press Ctrl-C to exit and you're done.

Administer your server

As mentioned previously MSDE does not ship with administration tools, so you cannot visually administer the server. You can of course use oSQL to do most things, but that's hardly an option for complex tasks.

Sql Server Client Tools
You can administer MSDE with the SQL Server client tools. If you have SQL Server or the client tools installed anywhere on your network you can connect to the MSDE server and administer it through those. This is the most efficient way to do this.

DbaMgr2k
I would highly recommend that you consider getting the free DbaMgr2k tool which is freen and provides a good chunk of the functionality that the SQL Enterprise manager provides for database administration. It allows table modification and simply querying as well as many common admin tasks.

Download DbaMgr2k

Microsoft Sql Server Web Data Administrator
Microsoft recently has also made available a Web based Data Administrator for SQL Server which also works with MSDE.

Download Sql Server Web Administrator

This tool requires .Net, but interestingly enough it can run without a Web Server - it can run using Cassini to essentially provide the offline functionality if IIS is not available! Kudos for putting this technology to good use.