Linked Server Connection Error via ODBC: OLE DB Provider "MSDASQL" for linked Server "<Servicennow DSN>"(Microsoft SQL Server Error 7399)

Issue

In some rare cases, (even when 'Allow inprocess' is unchecked) Linked SQL Server definition in Microsoft SQL Studio cannot connect to ServiceNow instance using ODBC.

While Expand the tables OR trying to run a sample query as below

 select * from OPENQUERY(SERVICENOW, 'select number, short_description from incident where number = ''<number>''') 

Error :

"The OLE DB provider MSDASQL for linked Server "<Servicennow DSN>" reported an error. Access Denied.

Cannot obtain the required interface (IID_IDBSchemaRowser") from OLE DB Provider "MSDASQL" for linked Server "<Servicennow DSN>"(Microsoft SQL Server Error 7399)

Resolution

In the Microsoft SQL Server studio Navigate to Server Objects > Linked Server > Providers and double-click MicrosoftOLE DB Provider for ODBC drivers.

Check 'Allow inprocess' ( Check the box) Save

Refer to : Microsoft Support link for Error 7399 and 7399

Test it again confirm if the query returns the result.