Installing pymssql on Windows server

I need to run Python with the pymssql library to query SQL server and I spent a day installing and compiling stuff on Windows.

Installation of pymssql was very painful, until I developed this procedure:

install python
update pip
download OpenSSL from http://www.npcglib.org/~stathis/blog/precompiled-openssl/
add bin folder to Path environment variable
download FreeTDS from https://github.com/ramiro/freetds/releases
add lib folder to Path environment variable
pip install pymssql

I even created the .exe package, so I won't have to install all this on live machine. It worked OK. Executable also worked on some other Windows machine.

But, when I tried it on the server it didn't work for some reason. The error didn't make sense and I started to cry:

WindowsError: [Error -2146893795] Provider DLL failed to initialize correctly

Could not help myself with links like this one:

"Windows Error: provider DLL failed to initialize correctly" on import of cgi module in frozen wxpython app

I have hundreds of tabs opened in my browser and I think I've searched the entire Internet.

I decided I'd try the Python and pymssql installation on the server, which I wanted to avoid, but let's go. And of course my frustration didn't end here. After successful installation I still can't load pymssql:

C:\Users\Me>C:\Python27\python.exe
Python 2.7.11 (v2.7.11:6d1b6a68f775, Dec  5 2015, 20:40:30) [MSC v.1500 64 bit (
AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import time
>>> import sys
>>> import pymssql
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ImportError: DLL load failed: %1 is not a valid Win32 application.

Can anyone point out where I went wrong?

Solution

When setting up pymssql version 2.1.2 and later on Windows, be careful to ensure that:

You download the builds of FreeTDS and OpenSSL that correspond to the Python version (e.g., 2.7, 3.4, 3.5, ...) that you are using.
You download the build of FreeTDS with the same "bitness" of Python that you are using (32-bit or 64-bit).
When adding folders to the Windows PATH, you must

i. specify the folders in which the required DLL files reside, and

ii. choose the OpenSSL folder that corresponds to the "bitness" of Python that you are using: bin for 32-bit, or bin64 for 64-bit.

For more information, see the related question:

"ImportError: DLL load failed" when trying to import pymssql on Windows