MSDE Tips and Tricks

Troubleshooting Tricks

LANGUAGES: SQL

TECHNOLOGIES:MSDE | osql

MSDE Tips and Tricks

Use this free desktop version of SQL Server and workaround 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 productcosting thousands of dollars, and it's missing only a few features - whichreally are necessary only for large enterprise applications. Oh sure,performance is throttled after five concurrent connections and the maximumdatabase size is about 2 gigs, but neither limitation is an obstacle for manyASP.NET apps that hit the database only lightly.

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

One of the biggest problems with MSDE is it doesn't shipwith client and admin tools such as Enterprise Manager and Query Analyzer. ManyMicrosoft developer tools, including Web Matrix and VS .NET, have the mostcommon tools built into their IDE. But that doesn't help if you are usingNotepad as your development tool of choice. And the IDE versions fall way shortof 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.NETEnterprise Manager is an open source SQL Server and MSDE management tool youcan find at http://sourceforge.net/projects/asp-ent-man/.Microsoft even has one, the Web Data Administrator. This is a free Web-basedMSDE management program written using C# and ASP.NET, and it includes sourcecode. Search the downloads section of MSDN for a copy.

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

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

osql -E -S MachineNameInstanceName

  -Q "sp_grantlogin'MachineNameASPNET'"

osql -E -S MachineNameInstanceName -d NameOfDb

  -Q"sp_grantdbaccess 'MachineNameASPNET'"

osql -E -S MachineNameInstanceName -d NameOfDb

  -Q"sp_addrolemember 'db_owner', 'MachineNameASPNET'"

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

As a result of the Slammer worm in February 2003, mostpeople now know that the sa login with a blank password is not just a sinagainst yourself but is likely to take down the entire network. You can attemptto 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 namedinstance.) You'll get a Password: prompt; just press Enter. If the response isan 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 samesteps 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 itisn't null, as well as a complex password for the @new setting. And then sleepwell tonight.

References

         Online version of the osql tool: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_osql_1wxl.asp

          "Manage the SQL Server Desktop Engine (MSDE 2000) byUsing the Osql Utility":http://support.microsoft.com/default.aspx?scid=kb;EN-US;q325003

          "Verify and Change the System Administrator Password byUsing MSDE":http://support.microsoft.com/default.aspx?scid=kb;en-us;Q322336

Don Kiely is senior technology consultant for InformationInsights, a business and technology consultancy in Fairbanks, Alaska. E-mailhim at mailto:[email protected].