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
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:10.10.10.10\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 10.10.10.10 -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
- Enable TCP/IP in Sql Server Configuration Manager for the Express instance
- Explicitly set the port to use in IPAll
- Restart the Sql Server Express Instance.
- Remove the named instance from the connection string (not sure why this works, but hey it does!)