This needs a bit of a long explanation so please bear with me.
The issue is related to Sage,MSSQL,Coldfusion,odbc so feel free to move this to the appropriate category if this isn't the correct one.

we are trying to access (read) sage line 50 and using the data in Coldfusion for output.

We first created a 32bit odbc connection using the Sage Line 50 drivers.
We then tried to access the connection using Coldfusion by creating a datasource via the coldfusion Administrator.

This wasn't possible since we are on a 64bit server and Coldfusion could only list the 64bit odbc dsn not the 32bit ones. We tried tricking coldfusion by using various methods we saw online (the solutions were mostly about Access odbc connections but we tried applying the to Sage) without any luck.

So after a lot of thought we decided to try and create a linked server "connection" using mssql thus instead of creating an odbc datasource in Coldfusion we would now create a mssql one and using that one to access the data.

This actually worked and we could retrieve data in sql management studio.

But the issue was not fixed 100%. We can only access data if and only if we are logged into sql management studio using a Windows Authentication user not any other user (i.e SA).

This causes an issues since Coldfusion datasources don't accept windows authentication when being set up.

Can anyone offer a solution to this or any other possible option to achieve what we are trying to do?