Managing a Sql Server Express database over SSH

June 22 2010

This is an extension of Automated deploy to IIS7 using NAnt

Our staging environment resides on a virtual private server hosted by our VPS company .  We're deploying the application using a NAnt script over Cygwin and SSH.

I ran into a problem executing sqlcmd (and osql) against the Sql Server Express database, getting a network error.

osql -S .\sqlexpress -d umbraco -U umbracouser -P umbracouser

Being a developer, I don't really know the inner workings of Sql Server, but I remembered that Sql Browser and TCP/IP needed to be turned on, so I followed the basic steps to do that using Sql Server Configuration Manager

Enabling TCP/IP:
Tcpipenable 500X358

but found it still wasn't working. Investigated by calling:

netstat -an | find "1433"

which returned nothing.  So I had a play around with the TCP IP settings some more and set the IPAll entry to use port 1433.  Previously it was blank


I still got no joy so I tried using the explicit tcp connections and that didn't work

osql -S tcp:\sqlexpress,1433 -d umbraco -U umbracouser -P umbracouser

Frustrated I started randomly modifying the osql call (it seemed random, more of a calculated random), and found a solution.

osql -S  -d umbraco -U umbracouser -P umbracouser

With a solution working I started undoing the various things I had tried to get it to work and came up with the minimal solution

  1. Enable TCP/IP in Sql Server Configuration Manager for the Express instance
  2. Explicitly set the port to use in IPAll
  3. Restart the Sql Server Express Instance.
  4. Remove the named instance from the connection string (not sure why this works, but hey it does!)

Post a comment

comments powered by Disqus