21 Nov 2014

Sqsh / FreeTDS with SQL2012 using the instance argument


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 !!

8 comments:

Xinjing Hu said...

Thank you so much !!! Really save my day!

Stale Idland said...

Thanks a lot , now I can use my sqsh and tsql.

Fish_Kungfu said...

Thank you very much! You saved my bacon and my sanity!

Robins Tharakan said...

Ha :) !
Am glad this could help you all saving some time...!

hmikael said...

i'm havving issue

$ tsql -S 192.168.1.213 -U sa -P PasS123
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

Robins Tharakan said...

Hi hmikael,

Have you made *BOTH* changes to the freetds file?

That is, you need to remove *port* and add *instance* argument. Doing only one would not resolve the error.

-
all the best

hmikael said...

hi Robin thank for your feedback, for some reason my port is not 1433 i find with this command that my port is 61304. and once i put a right port my problem was solved.

$ tsql -LH IP_MSSQL_DB
----------------- OUTPUT
ServerName MYSERVERNAME
InstanceName SQLEXPRESS
IsClustered No
Version 11.0.2100.60
tcp 61304
np \\MYSERVERNAME\pipe\MSSQL$SQLEXPRESS\sql\query

Robins Tharakan said...

Thanks hmikael for confirming what actually worked.

Am sure this feedback would save more people's day wasted in trying an undocumented feature.