Error [IM002] [Microsoft][ODBC Driver Manager] "Data source name not found and no default driver specified" and who do you trust?

(2021-Oct-31) A very short blog post, just a reminder to myself, but if you have ever tried to connect to a PostgreSQL database using ODBC interface (I know, it already sounds like a very interesting challenge :- ), then you might have experienced this error message: “ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.”

I admit that my online search for this error message quickly showed Microsoft and Stackoverflow resources, which still confused me a little and didn’t help to resolve my connectivity issue to PostgreSQL resource via ODBC:

• https://docs.microsoft.com/en-us/answers/questions/227987/34error-im002-microsoftodbc-driver-manager-data-so.html
• https://stackoverflow.com/questions/17115632/microsoftodbc-driver-manager-data-source-name-not-found-and-no-default-drive

Microsoft in its SSIS documentation provided a very good explanation on how to Connect to a PostgreSQL Data Source and even shared a Connection String sample with PostrgreSQL ODBC driver:

Driver={PostgreSQL ODBC Driver(UNICODE)};Server=<server>;Port=<port>;Database=<database>;UID=<user id>;PWD=<password>

My attempt to reuse this example still wasn’t working and I was still getting my “data source name not found” error message.

There was still one thing that puzzled and I wanted it to try. If I look at the list of 64- or 32-bit ODBC PostgreSQL connections I made, it didn’t align with what I had read in that Microsoft tech article.

My list of drivers was different, compared to this technical article and changing the names of those drivers to be conformed with the “PostgreSQL ODBC Driver(UNICODE)” was not in my power.

So then, I decided and changed my connection to this format, “Driver={PostgreSQL Unicode};Server=<server>;Port=<port>;Database=<database>;UID=<user id>;PWD=<password>”, just like in the list of drivers that I had; I  tested it both with 64- and 32-bit drivers. And, only then it worked and I was able to connect to my PostgreSQL instance. It’s doesn’t mean that this naming convention will work in all circumstances: ODBC driver may be modified with new versions or some other factors may alter a successful connection path to data. It's a changing world!

I still remember Elon Musk’s recommendations to make business requirements less dumb, because this may be dangerous if a smart person gave you the requirements and you might not question them enough.

Don’t get me wrong, I’m not blaming anyone. It just proved the fact that it’s OK to trust, but it’s worth testing and verifying things. Another lesson for me and not a new one :- )