8

I for some reason can't seem to connect to any SQL Server that is outside of our network, yet have no problem if I'm within the network via VPN. My code is as such for local connection:

sql = pyodbc.connect(
    'DRIVER={FreeTDS};'
    'SERVER=192.168.1.xx\ROA;'
    'DATABASE=RentalDB;'
    'UID=xxxxxxx;'
    'PWD=xxxxxxx'
)

and the following is what I'm trying for remote:

sql = pyodbc.connect(
    'DRIVER={FreeTDS};'
    'SERVER=69.178.xx.xx/ROA;'
    'DATABASE=RentalDB;'
    'UID=xxxxxxxx;'
    'PWD=xxxxxxxx'
)

When trying to connect, I am thrown the following error after about 15 seconds or so:

pyodbc.Error: ('08001', '[08001] [unixODBC][FreeTDS][SQL Server]Unable to connect to data source (0) (SQLDriverConnect)')

I'm able to successfully connect via SQL Server Management Studio, and we have a C# developer that has no problem with establishing a connection, but for some reason, I can't. I am running Python 2.7, Debian 7, FreeTDS 0.91, and PYODBC 3.0.7. The SQL Server version is 2012, I think. Any ideas?

EDIT: On the remote connection, i have tried the following for the 'SERVER=' string:

69.178.xx.xx/ROA
69.178.xx.xx\ROA
mssql://69.178.xx.xx/ROA
mssql://69.178.xx.xx\ROA
69.178.xx.xx:1433/ROA
69.178.xx.xx:1433\ROA
mssql://69.178.xx.xx:1433/ROA
mssql://69.178.xx.xx:1433\ROA

and all of them return the same error.

EDIT 2: Fixed the port number on the things above.

crookedleaf
  • 2,118
  • 4
  • 16
  • 38
  • 1) please confirm you are using the actual server IP instead of 69.178.xx.xx. 2) the default ms-sql port is 1433 not 1443, 3) please confirm if you are running nonstandard port. 4) Confirm that ROAMBA is the correct server instance name. 5) Confirm you can actually connect to the server port -- i.e., no firewall problems telnet 69.178.xx.xx 1433 should not give a message similar to Connecting To 69.178.xx.xx...Could not open connection to the host, on port 1433: if so, you are being blocked. – Gary Walker Jul 20 '14 at 21:21
  • @GaryWalker whoops, the 1443 thing was just me typing it once on here and then copying and pasting the rest. i am in fact using 1433. the server ip is correct, and i am able to telnet into it remotely and there don't appear to be any firewall issues. ROAMBA is the correct server instance name and runs fine when using it on the local connection string. – crookedleaf Jul 20 '14 at 21:28
  • OK, you've hit the most common problems. Check to sure your server is allowing remote connections search "enable remote connections sql2008" or such like. You don't identify sql server version, please add that. Make sure your server is configured to allow non-windows authentication search "change sql server authentification mode" you want mixed mode. – Gary Walker Jul 20 '14 at 23:17
  • Also, try connecting via the sql command line tool. E.g. sqlcmd -s localhost -U sa -P password -- could be an application issue. If this works, the problem is likey you app is not using the connection string you thought or you need to whitelist your application on the sql server – Gary Walker Jul 20 '14 at 23:29
  • If you have changed the named instance port number to 1433 (normally used for default instance), try omitting the instance name from the connection string and specify the IP address alone. – Dan Guzman Jul 21 '14 at 00:35
  • Dan Guzman, you reminded me that port 1433 could be open, but a different port for the instance server might be blocked. Need to check that the correct port can still be accessed. – Gary Walker Jul 21 '14 at 05:05
  • @GaryWalker SQL Server 2012 (which is mentioned in my post), and i double checked it does allow remote connections, ports forwarded and open, firewall allowing connection, and non-windows authentication. I tried removing the instance name, and am getting the same errors trying all the different combinations of the server address. when connecting via the TSQL command, i can connect fine. the one thing i am still trying to piece in is that our C# developer has no problems connecting outside of our network, and hasn't had to do anything different to has app other than change the server address. – crookedleaf Jul 21 '14 at 17:04
  • Sorry I overlooked the 2012 in your post. If the external program is using the same app and the TSQL command worked to the remote server. I can only think of 1 possibility, you are not using the connection string you think you are. Print or otherwise output the connection string using the same variable etc. you are using to open the connection. – Gary Walker Jul 21 '14 at 17:14
  • @GaryWalker i'm starting to think it actually is something specific with this server. we have another server running at a different address, and putting in that server address i can connect fine. hmm. i'm going to have to have out administrator look into the settings, because everything i am seeing looks like it's setup correct. – crookedleaf Jul 21 '14 at 17:49
  • Do share your eventual resolution, none of the normal stuff seems to be the problem. – Gary Walker Jul 21 '14 at 18:06
  • @GaryWalker will do. it's something application specific to python since i can connect via tsql. our network guys are looking into the issue and as soon as they figure out, i'll post it here. – crookedleaf Jul 21 '14 at 18:35
  • One last thing, How about adding a TDCSVER=8.0 to your tds configuration. Various ways to do this here http://www.freetds.org/userguide/envvar.htm – Gary Walker Jul 21 '14 at 18:43
  • @GaryWalker unfortunately i've already done this as well. i went through a slew of problems when i first installed pyodbc and freetds, and tried to connect to our sql server. doing that was the only way i was able to finally establish a connection. – crookedleaf Jul 21 '14 at 18:56

1 Answers1

6

I have finally figured out what the answer is (by accident while trying to also troubleshoot connecting to a SQL Server hosted on Azure). The PYODBC connection string, if connecting outside of the network the SQL Server is on, requires a port number, rather than assuming the default like other SQL packages use. Simply adding 'PORT=1433;' to the connection string got the connection to work. PYODBC seems to be picky about when it does and doesn't want to use the default port for connections.

crookedleaf
  • 2,118
  • 4
  • 16
  • 38