I have had such a hard time setting up PyODBC that I have become convinced that it is impossible under my circumstances. My circumstances are:
- I have an AWS Glue job using Python Shell.
- It connects to my EC2 Instance using a connection of type “NETWORK”. This connection includes a VPC, subnet, and security group.
- The EC2 Instance is Windows server. It runs Microsoft SQL Server.
- According to Programs and Features, the EC2 Instances has the “Microsoft ODBC Driver 17 for SQL Server” driver installed.
- In Python, my connection string is of the form
f'mssql+pyodbc://{username}:{password}@{host}:{port}/{database}?driver=ODBC+Driver+17+for+SQL+Server'. If I switchpyodbcforpymssqland the driver parts for?charset=ut8, then it works without issue.
I have tried countless permutations of specifications for the ODBC driver and my attempts to debug with pyodbc.drivers() (returning ['PostgreSQL', 'MySQL']) have led me to conclude that AWS Glue simply isn’t allowed to see my EC2 Instances PyODBC driver. Errors such as
Can’t open lib ‘driver=ODBC Driver 17 for SQL Server’ : File not found (0) (SQLDriverConnect)”
are common, but others appear depending on how you type the string.
I am not here to ask how to fix this. I have given up and set my standards far lower. Instead, I am asking this: Do any of the circumstances listed above make it impossible to connect through ODBC? Links to very similar cases where successful connections have been achieved will also be accepted.