MSDE Tips and Tricks

Use this free desktop version of SQL Server and work
around its lack of client and admin tools.

By Don Kiely

MSDE is one of the smartest things Microsoft ever created.
This free desktop version of SQL Server has nearly the full power of a product
costing thousands of dollars, and it's missing only a few features - which
really are necessary only for large enterprise applications. Oh sure,
performance is throttled after five concurrent connections and the maximum
database size is about 2 gigs, but neither limitation is an obstacle for many
ASP.NET apps that hit the database only lightly.

By releasing MSDE, Microsoft has helped many developers
move to industrial-strength databases. If you don't have SQL Server installed,
there's a good chance you do have
MSDE because it is installed with many Microsoft products. Tools such as the
ASP.NET Starter Kits install it as well.

One of the biggest problems with MSDE is it doesn't ship
with client and admin tools such as Enterprise Manager and Query Analyzer. Many
Microsoft developer tools, including Web Matrix and VS .NET, have the most
common tools built into their IDE. But that doesn't help if you are using
Notepad as your development tool of choice. And the IDE versions fall way short
of full-featured client and admin tools.

This problem has a variety of solutions. For example,
there are various tools available you can use to administer MSDE. ASP.NET
Enterprise Manager is an open source SQL Server and MSDE management tool you
can find at http://sourceforge.net/projects/asp-ent-man/.
Microsoft even has one, the Web Data Administrator. This is a free Web-based
MSDE management program written using C# and ASP.NET, and it includes source
code. Search the downloads section of MSDN for a copy.

Fortunately, MSDE does ship with one of the most powerful
tools in the SQL Server box: a utility called osql (in earlier versions of SQL
Server, it's called isql). With this tool, you can create and run stored
procedures, and you can access and change data. You can do just about anything
in osql that you can with T-SQL. The catch you were expecting is this is a
command-line tool that hearkens back to the dark days of DOS, with a dizzying
array of command-line options you have to get just right.

As an example of osql usage, one of the major problems
people have is allowing the default ASP.NET login, ASPNET, to access the
database. Here are the lines you need to run from the command line or a batch
file in response to the dreaded "SQL Server does not exist or access denied"
exception:

osql -E -S MachineName\InstanceName

-Q "sp_grantlogin
'MachineName\ASPNET'"

osql -E -S MachineName\InstanceName -d NameOfDb

-Q
"sp_grantdbaccess 'MachineName\ASPNET'"

osql -E -S MachineName\InstanceName -d NameOfDb

-Q
"sp_addrolemember 'db_owner', 'MachineName\ASPNET'"

Substitute your machine name and instance name (if
necessary), as well as the database name. Now ASPNET is a member of the
ownership role for the database. Be careful, though; in most applications
that's overkill, but you can fine-tune the permissions settings as needed.

As a result of the Slammer worm in February 2003, most
people now know that the sa login with a blank password is not just a sin
against yourself but is likely to take down the entire network. You can attempt
to log in to the sa account on MSDE with this line:

osql -U sa

(Use the -S switch if you need to log in to a named
instance.) You'll get a Password: prompt; just press Enter. If the response is
an osql prompt such as 1>, your sa password is blank. If it's not blank,
you'll get a message that the login for sa failed.

You also can change your sa password by following the same
steps above until you get the 1> prompt. Then enter this line:

sp_password @old = null, @new = 'newpwd', @loginame ='sa'

Substitute the current password for the @old setting if it
isn't null, as well as a complex password for the @new setting. And then sleep
well tonight.