I know this has been asked before, but I've tried every suggestion I could find online, and I'm still stumped.
I have a python (3.7) script which uses pymssql (2.1.4) to talk to an Azure SQL Server. This works fine on my local macOS machine. The trouble comes when I try to deploy it to an EC2 machine running Amazon Linux 2. When I try to connect I get:
self.conn = pymssql.connect(server=DBHelper.server, user=DBHelper.user, password=DBHelper.password, database=DBHelper.db)
File "src/pymssql.pyx", line 642, in pymssql.connect
pymssql.OperationalError: (20002, b'DB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (myservername.database.windows.net:1433)\n')
Here is what the FreeTDS log says:
net.c:226:Connecting to 40.121.158.30 port 1433 (TDS version 7.1)
net.c:252:tds_open_socket: connect(2) returned "Operation now in progress"
net.c:372:tds_open_socket() succeeded
packet.c:742:Sending packet
0000 12 01 00 34 00 00 00 00-00 00 15 00 06 01 00 1b |...4.... ........|
0010 00 01 02 00 1c 00 0c 03-00 28 00 04 ff 08 00 01 |........ .(......|
0020 55 00 00 02 4d 53 53 51-4c 53 65 72 76 65 72 00 |U...MSSQ LServer.|
0030 a8 19 00 00 - |....|
packet.c:640:Received packet
0000 04 01 00 25 00 00 01 00-00 00 15 00 06 01 00 1b |...%.... ........|
0010 00 01 02 00 1c 00 01 03-00 1d 00 00 ff 0c 00 07 |........ ........|
0020 6c 00 00 03 00 - |l....|
login.c:1216:detected flag 3
login.c:534:login packet rejected
query.c:3797:tds_disconnect()
The thing is, I can log in and run queries using the command line tool tsql just fine. It's just from python that it won't connect. I'm using the same credentials.
Any suggestions would be much appreciated.
I gave up and switched to pyodbc, and that seems to be working. Apparently, pymssql is no longer supported, so it's better to use pyodbc anyway.