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 !!
10 comments:
Thank you so much !!! Really save my day!
Thanks a lot , now I can use my sqsh and tsql.
Thank you very much! You saved my bacon and my sanity!
Ha :) !
Am glad this could help you all saving some time...!
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
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
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
Thanks hmikael for confirming what actually worked.
Am sure this feedback would save more people's day wasted in trying an undocumented feature.
unfortunately for me it is not working... Do i need to specify the pipe path or it is enough what you indicated above?
also, this is only for servers 2012 and above? What about 2000?
Post a Comment