I’m exploring using the Database Wizard from within the Data Mapper and have encountered an error. I’ve tried SQL and ODBC but I always get the same error.
From the DataMapper, make sure your ODBC connections have been defined with the ODBC 64-bit version (as opposed to Workflow, which requires ODBC 32-bit connections).
I tried the 64 bit version, but received the same results. When I switched to the plain SQL Server driver, I was able to connect up to a point. I have to also use Windows Authentication to actually pull data, but there could be an issue with my SQL user. I was using the SQL Server Native Client 11.0 because the customers SQL can’t use the SQL Server driver due to the version of SQL and I believe TLS.
Can you validate in any way that this Native Client DSN is configured properly? You could try using MS-Access to open a connection to the DB with that DSN.
I have 32 bit MS Access, but can connect using a 32 bit DSN created with the SQL Server Native Client 11.0 as a linked table. The 32bit and 64bit DSN’s are configured the same and both will connect successfully using he test button.
I tested it here on a local VM and didn’t run into any trouble. Here’s the step-by-step I followed:
ODBC test
This assumes you’ve already created and tested a 64-bit SQL Server DSN that uses the SQL Server Native Client 11.0 driver.
Now, in Connect:
- File | New | Data mapping Wizards | From Databases
- Database Type: ODBC Datasource
- ODBC Source: the name of your ODBC DSN (which should be listed in the dropdown)
- This ODBC source is MSSQL: ticked
- Authentication: whatever your SQL Server instance expects, usually Windows authentication
- At this stage you should be presented with a list of available tables, which means the database can be accessed properly.