Connecting from SQL Server using Linked Server

Querona can be used as a Linked Server target in MS SQL Server via the Microsoft OLE DB Driver for SQL Server only.

Note

The legacy providers like Microsoft OLE DB Provider for SQL Server (SQLOLEDB) and SQL Server Native Client OLE DB provider (SQLNCLI) remain deprecated and are not recommended by Microsoft to use either for new development work, see the article OLEDB driver for SQL Server.

Prerequisites

The Microsoft OLE DB Driver for SQL Server must be installed on the SQL Server machine that will be connecting to Querona.

Note

The supported Microsoft OLE DB Driver for SQL Server can be downloaded from Microsoft download site.

Linked Server configuration

To create a Linked Server connection to Querona, please follow the steps described in the article Create linked servers in the SQL Server database engine. When asked for the data source provider, choose the Microsoft OLE DB Driver for SQL Server.

The following TSQL statement example, will create a linked server connection to Querona server named “querona”:

EXEC sp_addlinkedserver @server     =  'querona',
                        @provider   = N'MSOLEDBSQL',
                        @datasrc    =  'querona.mydomain.com',
                        @srvproduct = N'querona',
                        @catalog    =  'my_virtual_database_name'

Troubleshooting

You may receive the following error during the initialization of the driver:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSOLEDBSQL" for linked server "MYLINK" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "MSOLEDBSQL" for linked server "MYLINK"

The error is returned when running a query in SQL Server Management Studio using SQL Server Linked Server.

The following conditions are present:

  1. “Allow InProcess” is not checked for the provider

  2. SQL Server service is started as a Local Service

  3. The user is a regular user that is part of the Administrator’s group (either local user or domain user)

  4. User Account Control (UAC) is turned off

  5. SQL Server Management Studio is opened as the regular user using Windows Authentication

The cause of the issue is that the DCOM privileges are not configured correctly for an out of process provider.

Note

The “Allow InProcess” option checked for a provider means that SQL Server process will host the provider DLL and run the linked server inside SQL Server Process, therefore the permissions for SQL Server service account come into play while accessing DLLs or registry information related to the provider. The “Allow InProcess” option unchecked for a provider means that a DLLHost process will host the provider and run the linked server.

To resolve the issue permanently, please configure the appropriate DCOM permissions as described in the following Microsoft Knowledge Base (MSKB) article #2647989.

The simple workaround is to start the SQL Server Management Studio as Administrator, or to check the “Allow Inprocess” checkbox for the MSOLEDBSQL provider.