MSDE Tips and Tricks

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

Troubleshooting Tricks

LANGUAGES: SQL

TECHNOLOGIES: MSDE | osql

 

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.

 

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) by Using the Osql Utility": http://support.microsoft.com/default.aspx?scid=kb;EN-US;q325003

 

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

 

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

 

 

 

 

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish