How do I manage / connect to SQL Server Express?
Updated: 6/20/2008 12:25:00 AM
Requests: 187353

a.k.a. "Where is Enterprise Manager?"
a.k.a. "When will Express Manager be released?"

Microsoft has released a trmmed-down version of Management Studio, called Management Studio Express. You can download it from Microsoft Downloads. If you're brave, feel free to download the recent CTP of the tool here, I just can't really tell you yet whether anything worthwhile has been added or, more importantly, if anything useful has become broken.

If you are affected by the limitations of this free tool, you can manage SQL Server Express with a host of other tools:

Allegedly, you can also manage Express Edition with Web Data Administrator, but only if you enable mixed authentication (I haven't tried this, and note also that it is not officially supported).

There is no longer any such thing as Enterprise Manager or Query Analyzer, so stop looking for them, and don't try to use existing installations to connect to SQL Server 2005 instances—it simply will not work, even if it seems to at first glance.

As for connecting to Express from application code, this should not be any different from connecting to a named instance of SQL Server 2000. Your connection string should look like this, assuming local machine and an instance name of SQLEXPRESS (you might need a different instance name, and you may have to use a machine name, rather than "." (which means local)).

ConnectionString = "Provider = SQLNCLI;" & _
    "Data Source = .\SQLEXPRESS;" & _
    "Initial Catalog = <db name>;" & _
    "User ID = <username>;" & _
    "Password = <password>;"

However, you might come across various errors, depending on your configuration and the tool you are using:

Sqlcmd: Error : Microsoft OLE DB Provider for SQL Server :
Client unable to establish connection.
 
Shared Memory Provider: The specified module could not be found.
 
Shared Memory Provider: The parameter is incorrect.
(Microsoft SQL Server, Error: 87)
 
Named pipes provider: The system cannot find the file specified
 
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
 
Shared Memory Provider: The system cannot find the file specified.

So in addition to the connection string above, some other steps you might have to perform:

  • Make sure that SQL Server Express is functioning properly:
     
    • Start / Run... / type "CMD" without the quotes and hit OK
    • Type "SQLCMD -S.\SQLEXPRESS" without the quotes and hit Enter
    • Verify that you<
© 2004-2014 Aaron Bertrand, All Rights Reserved. SQL Server 2005, of course, belongs to Microsoft.