search

pymssql / pymssql

Official home for the pymssql source code.
https://pymssql.readthedocs.io/en/latest/
GNU Lesser General Public License v2.1
841 stars 311 forks source link

connection to sql server freeze #842

Open cipector opened 1 year ago

cipector commented 1 year ago

Describe the bug When using pymssql version 2.2.8 it freeze on connection, with no error, no timeout, just hang there forever.

To Reproduce database_connection = f"mssql+pymssql://{USERNAME}:{PASSWORD}@{SERVER}:{PORT}/{DATABASE}" engine = create_engine(database_connection) connection = engine.connect()

Expected behavior connect to sql server

Current Behavior freeze on connection line

Context (Environment)

Additional context Tried to unninstall pymssql 2.2.8 and use version 2.2.7 and it solve the problem

termim commented 1 year ago

Could you try to set environment TDSDUMP=tdslog.txt and try to compare what 2.2.7 and 2.2.8 do during connection?

uchukamen commented 1 year ago

I had the same issue. Thank you for the information. I tried to unninstall pymssql 2.2.8 and use version 2.2.7 and it solve the problem also.

Context (Environment)

OS : windows 11 64bit python : 3.10.6 with venv

0sumrich commented 1 year ago

Describe the bug When using pymssql version 2.2.8 it freeze on connection, with no error, no timeout, just hang there forever.

To Reproduce database_connection = f"mssql+pymssql://{USERNAME}:{PASSWORD}@{SERVER}:{PORT}/{DATABASE}" engine = create_engine(database_connection) connection = engine.connect()

Expected behavior connect to sql server

Current Behavior freeze on connection line

Context (Environment)

  • OS : windows 10 64bit
  • python : 3.10.11
  • sqlalchemy : 2.0.3 - 2.0.20 (both)
  • pandas : 1.5.3 - 2.0.3 (both)

Additional context Tried to unninstall pymssql 2.2.8 and use version 2.2.7 and it solve the problem

I have the same issue here

brucegibbins commented 1 year ago

Hi. Colleague has the same issue with exactly the same symptom and solution. No notification, no timeout, simply hangs and reverting to v2.2.7 resolves the issue

Doxxxxxx commented 1 year ago

Hi. Same issue here with Microsoft SQL Server 2012 and pymssql 2.2.8. Python : 3.10.8

version 2.2.7 works ok.

bchen290 commented 1 year ago

I have ran into this issue too and for me it had to do with the wrong OpenSSL version being used. I installed pymssql which pulled in OpenSSL 3 which seems to be incompatible with OpenSSL 1.1.1. If I pin OpenSSL to 1.1.1 then I am able to use pymssql normally.

Ofloo commented 1 year ago

installed using pip python 3.12 on windows 10 pymssql 2.2.9 same issue, trying to isntall 2.2.7 but that has compile errors !!

sudheer82 commented 1 year ago

I have faced the same problem on a windows 10 PC with pymssql 2.2.10; rolling back to 2.2.7 resolved the issue.

ktm5j commented 1 year ago

Also experiencing this issue with version >= 2.2.8 on Windows. Rolling back to 2.2.7 fixed the issue.

This was really confusing me for a while, since pymssql 2.2.8+ on Windows is able to connect to my test database, but not to our production database. My test database is running the MSSQL docker image on a Linux server. Our production database is a proper MSSQL install running on Windows Server.

Hope this information helps!

Anandir commented 10 months ago

In my case, I've to downgrade pymssql to version 2.2.7 AND downgrade openssl to 1.1.1 as @bchen290 suggested . I hope this can help furthermore!

Best regards.

Krupniok commented 10 months ago

In my opinion, this is a Windows problem. I encountered the problem a few days ago after I had to reinstall the Python environment on a new Windows 11 machine. Since then I've been researching and testing. Today I installed WSL on the new computer and changed the environment in PyCharm to WSL. It works perfectly with the current Python version 3.12.1 and pymssql Version 2.2.11. As a countercheck, I have upgraded the pymssql module to the current version on my old Windows 10 computer, on which Python 3.10 is installed, and I have the same problems there, i.e. it freezes completely, without error messages, etc. So the problem seems to be the combination of Windows and the current version of the pymssql module. The big question remains - how to solve the problem?

termim commented 10 months ago

What MSSQL server version?

Set environment TDSDUMP=tdslog.txt in both cases and do compare what is different.

Krupniok commented 10 months ago

@termim MSSQL 2014 (12.0.60240)

WSL environment (work like it should):

net.c:1357:instance port is 1433
net.c:391:Connecting with protocol version 7.4
net.c:318:Connecting to 000.000.000.000 port 1433
net.c:340:tds_setup_socket: connect(2) returned "Operation now in progress"
net.c:318:Connecting to 0000:0000:000::0000:000 port 1433
net.c:340:tds_setup_socket: connect(2) returned "Network is unreachable"
net.c:528:tds_open_socket() succeeded
packet.c:852:Sending packet
0000 12 01 00 38 00 00 00 00-00 00 1a 00 06 01 00 20 |...8.... ....... |
0010 00 01 02 00 21 00 0a 03-00 2b 00 04 04 00 2f 00 |....!... .+..../.|
0020 01 ff 09 00 00 00 00 00-00 47 4f 4e 49 50 50 52 |........ .DBNAME|
0030 4f 44 00 3a 04 00 00 00-                        |.:....|

packet.c:410:Received packet
0000 04 01 00 2b 00 00 01 00-00 00 1a 00 06 01 00 20 |...+.... ....... |
0010 00 01 02 00 21 00 01 03-00 22 00 00 04 00 22 00 |....!... ."....".|
0020 01 ff 0c 00 18 23 00 00-00 00 00                |.....#.. ...|

login.c:1387:detected crypt flag 0
tls.c:1020:setting default openssl cipher to:HIGH:!SSLv2:!aNULL:-DH
tls.c:147:in tds_push_func_login
tls.c:117:in tds_pull_func_login
packet.c:852:Sending packet
0000 12 01 01 49 00 00 00 00-16 03 01 01 3c 01 00 01 |...I.... ....<...|
0010 38 03 03 27 b7 68 f4 4b-d7 43 7b 52 23 86 63 29 |8..'.h.K .C{R#.c)|
0020 87 e5 c7 24 9f b3 17 26-b8 bb 33 32 f8 07 25 8e |...$...& ..32..%.|
0030 86 34 c8 20 07 35 a9 90-8a ff 2f cd 9d 30 e3 a2 |.4. .5.. ../..0..|
0040 11 bf a4 3b c0 57 b7 89-94 4e f7 06 a1 48 a7 16 |...;.W.. .N...H..|
....
....
....
packet.c:410:Received packet
0000 12 01 03 80 00 00 00 00-16 03 03 03 73 02 00 00 |........ ....s...|
0010 51 03 03 65 b0 be fd 72-8c a5 08 81 dc 44 7c 86 |Q..e...r .....D|.|
0020 fd 56 0e ca 10 f3 e5 4b-39 70 ae 46 b0 0a 48 1d |.V.....K 9p.F..H.|
0030 d4 e1 ee 20 3a 36 00 00-92 53 fb 77 03 a4 06 7c |... :6.. .S.w...||
0040 aa c5 db 26 46 8b 6a 4d-40 2e 12 a3 04 80 7e 1b |...&F.jM @.....~.|
0050 a0 af fe 7e c0 28 00 00-09 00 17 00 00 ff 01 00 |...~.(.. ........|
....
....
....

Windows environment (freezes):

net.c:1357:instance port is 1433
net.c:391:Connecting with protocol version 7.4
net.c:318:Connecting to 000.000.000.000 port 1433
"
net.c:528:tds_open_socket() succeeded
packet.c:852:Sending packet
0000 12 01 00 38 00 00 00 00-00 00 1a 00 06 01 00 20 |...8.... ....... |
0010 00 01 02 00 21 00 0a 03-00 2b 00 04 04 00 2f 00 |....!... .+..../.|
0020 01 ff 09 00 00 00 00 00-00 47 4f 4e 49 50 50 52 |.�    ..... .DBNAME|
0030 4f 44 00 44 64 00 00 00-                        |.Dd...|

packet.c:410:Received packet
0000 04 01 00 2b 00 00 01 00-00 00 1a 00 06 01 00 20 |...+.... ....... |
0010 00 01 02 00 21 00 01 03-00 22 00 00 04 00 22 00 |....!... ."....".|
0020 01 ff 0c 00 18 23 00 00-00 00 00                |.�...#.. ...|

login.c:1387:detected crypt flag 0
tls.c:1020:setting default openssl cipher to:HIGH:!SSLv2:!aNULL:-DH
tls.c:147:in tds_push_func_login
tls.c:117:in tds_pull_func_login
packet.c:852:Sending packet
0000 12 01 01 4b 00 00 00 00-16 03 01 01 3e 01 00 01 |...K.... ....>...|
0010 3a 03 03 57 75 6f 3d 02-23 e6 3e 50 df 4d 68 cb |:..Wuo=. #�>P�Mh�|
0020 0b e1 5d 7f 67 e5 ec 2d-d3 8a 5c e7 be e3 6e 6e |.�].g��- ӊ\��nn|
0030 09 ed 78 20 db 36 39 4d-37 99 ba 24 79 bc c8 81 |  �x �69M 7.�$y��.|
0040 dc 06 a6 9e 89 f5 17 bc-d1 47 76 6c ba 80 82 f9 |�.����.� �Gvl�.��|
0050 e8 c2 22 06 00 5c 13 02-13 03 13 01 c0 2c c0 30 |��"..\.. ....�,�0|
0060 cc a9 cc a8 c0 af c0 ad-c0 5d c0 61 c0 2b c0 2f |̨̩���� �]�a�+�/|
0070 c0 ae c0 ac c0 5c c0 60-c0 24 c0 28 c0 73 c0 77 |�����\�` �$�(�s�w|
0080 c0 23 c0 27 c0 72 c0 76-c0 0a c0 14 c0 09 c0 13 |�#�'�r�v �.�.�    �.|
0090 00 9d c0 a1 c0 9d c0 51-00 9c c0 a0 c0 9c c0 50 |..���.�Q .������P|
00a0 00 3d 00 c0 00 3c 00 ba-00 35 00 84 00 2f 00 41 |.=.�.<.� .5.�./.A|
00b0 00 ff 01 00 00 95 00 0b-00 04 03 00 01 02 00 0a |.�...�.. ........|
00c0 00 16 00 14 00 1d 00 17-00 1e 00 19 00 18 01 00 |........ ........|
00d0 01 01 01 02 01 03 01 04-00 23 00 00 00 16 00 00 |........ .#......|
00e0 00 17 00 00 00 0d 00 2a-00 28 04 03 05 03 06 03 |.......* .(......|
00f0 08 07 08 08 08 09 08 0a-08 0b 08 04 08 05 08 06 |..... .. ........|
0100 04 01 05 01 06 01 03 03-03 01 03 02 04 02 05 02 |........ ........|
0110 06 02 00 2b 00 05 04 03-04 03 03 00 2d 00 02 01 |...+.... ....-...|
0120 01 00 33 00 26 00 24 00-1d 00 20 5d 89 9d f4 b0 |..3.&.$. .. ]�.��|
0130 4a 11 31 69 37 6d 9f 1e-6e f9 5c 5e e3 53 5c 13 |J.1i7m�. n�\^�S\.|
0140 04 00 56 cd 3c fa 0d 83-2e d9 50                |..V�<�.� .�P|

I hope it helps to analyze this issue.

gordthompson commented 8 months ago

one possible solution here:

https://github.com/pymssql/pymssql/issues/860#issuecomment-1786163208

izcoser commented 7 months ago

Had to same issue too. Switched back to 2.2.7.

termim commented 7 months ago

Have you tried the solution above?

stolzitservices-Ben commented 3 months ago

Same problem here with 2.3.0 on windows 11. I tried @gordthompson solution (#860 ) and it works. 🙂👍