This is a corner-case advise to anyone looking for a solution as to why sqsh / tsql / freetds combination is working perfectly in one SQL2012 instance but unable to login to a newly configured SQL2012 instance, the details for which just came in.
For starters, Sqsh / FreeTDS is pretty helpful if you're dumping-off of MSSQL Server to any CSV format (to, lets say dump to a PostgreSQL instance).
Symptoms
- Sqsh / Tsql / FreeTDS is perfectly configured
- The setup logs in to another SQLServer perfectly well
- All this when you are able to login to both servers via SSMS from the same box
- So it isn't a password issue
- Neither is it a firewall issue
- The new SQL Server keeps giving following error messages:
Error Messages
"Open Client Message"Layer 0, Origin 0, Severity 78, Number 41
Unable to connect: Adaptive Server is unavailable or does not exist
OR
# /opt/freetds/bin/tsql -S "Q4DEV2" -U 'PostgreSQL' -P 'Password@123'
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Error 20009 (severity 9):
Unable to connect: Adaptive Server is unavailable or does not exist
OS error 111, "Connection refused"
There was a problem connecting to the server
Login Details (as given by MSSQL Server Admin)
OLD_SERVER: q3.thatguyfromdelhi.com (this works via sqsh / tsql)
Login:PostgreSQL
Port: 1433 (default)
Password:Password@123
NEW_SERVER: q4.thatguyfromdelhi.com\SQL2012 (this isn't working via sqsh / tsql)
Login:PostgreSQL
Port:9999 (non-default)
Password:Password@123
Solution
# cat /opt/freetds/etc/freetds.conf
[Q3DEV1]
host = q3.thatguyfromdelhi.com
port = 1433
tds version = 7.0
charset = UTF-8
[Q4DEV2]
host = q4.thatguyfromdelhi.com
instance = SQL2012
tds version = 7.0
tcp = 9999 # required only if port is not default (i.e. port other than 1433)
charset = UTF-8
The *MAGIC* to the mix is the 'instance' keyword in the configuration. As things turn out, MSSQL Server does not just identify an Instance via a Port number (like how all sane Server configurations do) they also allow a named instance, which does not work with just a PORT number.
In fact, you may have noticed that the the Port Number is missing in the Q4DEV2 configuration, and that is because PORT is mutually exclusive with INSTANCE keyword. And yes, to confirm the obvious, value of the Instance parameter should be the text after the backslash (\) in the SQLServerName field, that the MSSQL Server Admin provided earlier.
Alternatively, if you do have the new MSSQL Server running on a non-default port (lets assume port 9999) then use the 'tcp' argument as shown above.
A few useful hours wasted at my end... hope this saves someone else's !!