connect function timeout not working with SQL Server using SSPI or documentation incorrect

[dkleehammer]

Brought over from code.google.com.

What steps will reproduce the problem?

1. Attempt to connect to incorrect server name or instance using following code:
   pyodbc.connect('DRIVER={SQL Server};SERVER=\servername\inst01;', timeout=10)

What is the expected output?
Exception to be raised in 'timeout' settings value (10 seconds in this case)

What do you see instead?
Default timeout reached and then pyodbc.Error raised

What version of the product are you using?
python 2.7 | pyodbc 3.0.5

On what operating system?
Windows 7 64bit | SQL Server 2008 R2

Please provide any additional information below.

I believe the problem may be an incorrect setting is being applied to the SQLSetConnectAttr function in connection.cpp (line 75). The setting being applied is SQL_ATTR_LOGIN_TIMEOUT; which the spec says, "The query timeout period expired before the connection to the data source completed. The timeout period is set through SQLSetConnectAttr, SQL_ATTR_LOGIN_TIMEOUT."

There is another setting that is for the data source connection timeout named "SQL_ATTR_CONNECTION_TIMEOUT"; which the spec says, "The connection timeout period expired before the data source responded to the request. The connection timeout period is set through SQLSetConnectAttr, SQL_ATTR_CONNECTION_TIMEOUT."

The documentation could be incorrect since the Connection page doesn't have any information on the timeout keyword. I may be using it incorrectly or using the wrong keyword argument, but the source shows the keyword being 'timeout'.

[jacobsvante]

Hi dkleehammer,

First of all, thanks for an awesome project.

I'm having this problem with both 3.0.6 and the latest beta (3.0.7-beta03). If I set an incorrect server name in the connect string it tries to connect forever even though I've added the timeout flag to the connect method. Keyboard interrupts don't work until maybe a minute in or so.

I'm on Mac OS X 10.8.2 (64-bit) and the database I'm trying to connect to is a SQL Server 2008 R2 x64 (10.50.1617.0).

I can see that you closed this ticket. Was this fixed?

[dkleehammer]

jmagnusson,

Actually, this project is ran by my brother, mkleehammer, but I'm sure he'll check this one. :D

I was incorrect about the keywords in the connection string. The timeout is soley used for query timeouts. We are investigating why the odbc connection timeout isn't being handled properly. We believe we are on the right track and will update pyodbc with the fix as quickly as possible.

dk

[ltoshea]

Did this ever get fixed?
Currently I have a firewall blocking outbound database connections. I've only just found this out after some debugging - unfortunately the timeout keyword seems to be being ignored and it hangs forever. On non firewalled host it works fine but I need me application to raise an error not just hang.

CONN_STRING = 'DSN={};DATABASE={};UID={};PWD={}'.format(SERVER,DATABASE,UID,PWD) conn = pyodbc.connect(CONN_STRING,timeout=15)

[ghost]

Is this issue fixed?

[ltoshea]

I don't believe so

On Wed, 6 Apr 2016, 09:13 Sreejith Kesavan, notifications@github.com
wrote:

Is this issue fixed?

—
You are receiving this because you commented.
Reply to this email directly or view it on GitHub
#11 (comment)

Sent from Android

[JavaScriptDude]

Just discovered this same issue on Windows Server 2019 using ODBC Driver 17 for SQL Server. I have a canary that is checking health of services using pyodbc and hit was hanging on this when port is not responding.

Work around is to do a port scan of 1433 before trying to connect.

Here is a helper for scanning ports:

# (bPortOpen, iErrno) = scanPort('<ip>', <port>)
def scanPort(host:str, port:int, timeout:int=4) -> tuple(bool, int):
    errno = 0 # see TCP socket error codes https://gist.github.com/gabrielfalcao/4216897
    sock = None
    try:
        sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
        sock.settimeout(timeout)
        errno = sock.connect_ex((host, port))
    except socket.timeout:
        errno=-1
    except socket.gaierror:
        errno=112
    except socket.error:
        errno=111
    try:
        sock.close()
    except:
        None
    return ((errno == 0), errno)