We have a client that is having a very strange issue with ODBC drivers when we try to setup a linked server in SQL. No matter what we try we get an error that seems to suggest that the login is failing but ONLY when we try to access it from SQL. Using ODBC Configuration and excel we can use the ODBC drivers just fine. (text of error is at the end of this email)
Some important things:
- Prophix is trying to do an integration with Sage100 and they called us in to assist with the ODBC and SQL Link
- Sage100 is using unified logins, we setup an ODBC driver (64 bit) The ODBC tests correctly in ODBC management using Unified, or Direct Sage100 Username/Password login.
- The ODBC also works in Microsoft Excel and we can pull data into a spreadsheet without any problems.
- The Error occurs when trying to add linked server to SQL. We Switched SQL service to Local System, and a specific account with Sage Access, no change in result. Sage and SQL are installed on different machines in the Same domain.
- Gave full rights to Sage directory to all users, no change in result.
- Tried setting up ODBC in SQL using both Unified, and Direct Sage100 Username/Password login, no success.
- System was previously upgraded from MAS90 4.5 to 2016, but this is something new we are trying so I have no idea if it would have worked prior to the upgrade or not.
MICROSOFT SQL SERVER MANAGEMENT STUDIO
The linked Server has been created but failed a connection test. Do you want to keep the linked server?
Additional information:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsof.SqlServer.ConnectionInfo)
Cannot initialize the data source object of the OLE DB provider “MSDASQL” for the linked server
“SAGE64SMS”
OLE DB provider “MSDASQL” FOR LINKED SERVER “SAGE64SMS” Returned message “[Microsoft][ODBC
Driver Manager] Driver’s SQLSetConnectAttr failed”.
OLE DB provider “MSDASQL” for linked server “SAGE64SMS” returned message “[Providex]ODBC
Driver][PVKIO]Logon failed”. (Microsoft SQL Server, Error:7303)
