pymssql uses default port if none is specified in connection url. Ignores the port in freetds.conf

[GoogleCodeExporter]

What steps will reproduce the problem?
1. conn = pymssql.connect(host='ELONMCLSQLC2S10_SQL10', user='usr', 
password='pwd',database='my_db')

What is the expected output? What do you see instead?
Expect no output, no errors, but connection succeeds and is usable.
Actual result is following error:
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/turnechr/py_tds_test/pymssql-2.0.0b1-dev-20111019/local/lib/python/pymssql-2.0.0b1_dev_20111019-py2.5-solaris-2.10-sun4u.egg/pymssql.pyx", line 549, in pymssql.connect (pymssql.c:7110)
    raise OperationalError(e[0])
pymssql.OperationalError: (20009, 'Net-Lib error during Connection refused')

What version of the product are you using? On what operating system?
--------------------------------------------------------------------
pymssql-2.0.0b1-dev-20111019
Python 2.5.4
Solaris 10
freetds v0.91

Please provide any additional information below.
-----------------------------------------------------

I created an env variable TDSDUMPCONFIG pointing to a file and checked contents 
of this after attempting to connect.
Although I passed pymssql no port - tds logs show it is searching freetds.conf 
for an entry with a default port ie "ELONMCLSQLC2S10_SQL10:1433". When this is 
not found - it then searches again for ELONMCLSQLC2S10_SQL10 which is found. 
However, the port number of 3010 from freetds.conf is ignored - and the default 
port of 1433 is retained which then fails to connect.

log.c:196:Starting log file for FreeTDS 0.91
        on 2011-11-23 15:20:50 with debug flags 0x4fff.
config.c:185:Getting connection information for [ELONMCLSQLC2S10_SQL10:1433].
config.c:189:Attempting to read conf files.
config.c:297:Found conf file '/home/turnechr/py_tds_test/freetds.conf' (from 
$FREETDSCONF)

-----
config.c:554:   Reached EOF
config.c:483:Looking for section ELONMCLSQLC2S10_SQL10.
-----
config.c:543:Got a match.
config.c:565:   host = 'xxxxxxx.uk.ml.com'
config.c:595:Found host entry elonmclsqlc2s10.uk.ml.com.
config.c:599:IP addr is xx.xx.x.xxx.
config.c:565:   port = '3010'
config.c:565:   tds version = '7.1'
config.c:788:Setting tds version to 7.1 (0x701) from $TDSVER.
config.c:540:   Found section xxxxxxx_sql2.
config.c:540:   Found section xxxxxx_sql2.
config.c:540:   Found section xxxxxx_sql10.
config.c:554:   Reached EOF
config.c:301:Success: [ELONMCLSQLC2S10_SQL10] defined in 
/home/turnechr/py_tds_test/freetds.conf.
config.c:224:Final connection parameters:
config.c:225:            server_name = ELONMCLSQLC2S10_SQL10:1433
config.c:226:       server_host_name = elonmclsqlc2s10.uk.ml.com
config.c:227:                ip_addr = xx.xx.x.xxx
config.c:228:          instance_name =
config.c:229:                   port = 1433
config.c:230:          major_version = 7
config.c:231:          minor_version = 1
config.c:232:             block_size = 512
config.c:233:               language = us_english
config.c:234:         server_charset =
config.c:235:        connect_timeout = 0
config.c:236:       client_host_name = xxxxxxx
config.c:237:         client_charset = ISO-8859-1
config.c:238:               app_name = pymssql
config.c:239:              user_name = ogredevconf
config.c:242:                library = DB-Library
config.c:243:              bulk_copy = 0

Please note that everything works if I use tsql:
tsql -S ELONMCLSQLC2S10_SQL10 -U usr

Work-Around
-------------------
If I set env variable TDSPORT to 3010 this works fine.
If I give pymssql a port in the connection string it also works
-However these are not ideal as I want tds to sort out the port number.

Grateful for any help.

Original issue reported on code.google.com by christop...@bankofamerica.com on 23 Nov 2011 at 3:41

[GoogleCodeExporter]

Although pymssql uses FreeTDS, I don't intend for it to be used in conjunction 
with entries int the freetds.conf file.  I want to abstract away FreeTDS as 
much as possible that python developers don't need to know anything about it.  
Therefore, you should use the port parameter on the connection:

conn = pymssql.connect(host='sql10.example.com', user='usr', 
password='pwd',database='my_db', port='3010')

> However these are not ideal as I want tds to sort out the port number.

Sorry, as explained above, thats pretty much the exact opposite of how I'd like 
to see pymssql work.  If you have a legitimate reason why freetds.conf is 
needed over setting the port in the connection, please explain your use case.

Original comment by rsyr...@gmail.com on 24 Nov 2011 at 4:13

• Changed state: WontFix

[GoogleCodeExporter]

Hi - thanks for the reply. My use-case is quite specific to my situation - 
where a web-service passes me a hostname and I then use that to connect to the 
db via freetds.conf. The port-number is not currently included in that 
web-service.

Based on your comment - I'll take the route of getting that web-service updated 
to pass me a port number.

Thanks for looking at this - great product...

Original comment by christop...@bankofamerica.com on 24 Nov 2011 at 10:24

[GoogleCodeExporter]

FYI, you can also add the port to the hostname:

conn = pymssql.connect(host='sql10.example.com:3010', user='usr', 
password='pwd',database='my_db')

Not sure if that helps your use-case, but figured I would mention it.

Original comment by rsyr...@gmail.com on 26 Nov 2011 at 3:09

[GoogleCodeExporter]

I may be missing something in Pymssql which is doing this already, but the 
approach implemented is difficult to use when the database you want to connect 
to has an instance name. In that case the port is given out dynamically by sql 
server. There is a service running by default on port 1434 that returns 
information about the instances. FreeTDS must be using it to allow you to 
specify the instance name in Freetds.conf. If we could have an "instance" 
parameter in pymssql and use it instead of the port it would be very helpful. 
It could be a passthrough to FreeTDS or separately implemented behavior in 
pymssql.

Original comment by larry.da...@gmail.com on 27 Jan 2012 at 11:07

[GoogleCodeExporter]

Its already supported:

connect(server=r'myserver\instance1', user=username, password=password)

Original comment by rsyr...@gmail.com on 30 Jan 2012 at 4:45