Description
Linked SQL Server definition in SQL Studio cannot connect to ServiceNow instance using ODBC.
- The error observed in SQL studio was:
Cannot Initialize the data source object OLE DB provider "MSDASQL" for Linked Server "SERVIENOW". OLEDB provider "MSDASQL" for Linked Server "SERVIENOW" returned message "[SN][ODBC ServiceNow Driver][OpenAccess SDK SQL Engine]Base table:OA_TYPES not found.[10129]". (Microsoft SQL Server Error:7303)
- ISQL queries against the instance using ODBC work without a problem.
Release or Environment
Client uses Microsoft SQL Server Management Studio connected via ServiceNow ODBC driver to the instance database.
Cause
Linked Server is set to run "in-process" with Microsoft SQL Server.
Resolution
Uncheck the allow in-process flag in your linked server options for OLE DB Provider.
Follow step number 7 and 8 from the documentation below for additional details:
Configure SQL Server
https://docs.servicenow.com/bundle/madrid-application-development/page/integrate/odbc-driver/task/t_ConfiguringSQLServer.html
Additional Information
The allow in-process flag force to run the ODBC provider in-process (within the same process as SQL Server).
This setting is not recommended because any issues with the provider can affect the SQL Server process, which in turn could result in crashing SQL server.