Connecting from SQL Server using Linked Server

Querona can be used in MS SQL Server as a Linked Server target, using Microsoft OLE DB Driver for SQL Server. No other provider nor driver is supported. The legacy Microsoft OLE DB Provider for SQL Server (SQLOLEDB) and SQL Server Native Client OLE DB provider (SQLNCLI) remain deprecated and it is not recommended to use either for new development work according to the https://docs.microsoft.com/en-us/sql/connect/oledb/oledb-driver-for-sql-server?view=sql-server-ver15.

As a prerequisite, enable the option “Allow inprocess” for MSOLEDBSQL provider. In case the initialization of the driver fails with the message “Access denied” or “Unexpected error”, please configure the appropriate DCOM permissions for MSDAINITIALIIZE described in the following Microsoft Knowledge base article https://support.microsoft.com/en-us/help/2647989/error-message-when-you-try-to-create-an-instance-of-an-ole-db-provider.

Cause

This issue can occur if the security settings for the MSDAINITIALIIZE DCOM class are incorrect.

Resolution

To resolve this issue in Windows Vista and later versions of Windows, follow these steps, and start with step 1. If you are running an earlier version of Windows, start with step 7:

  1. Click Start, type regedit in the Search programs and files box, press Enter, and then click Yes.

2. Locate the following subkey: HKEY_LOCAL_MACHINESOFTWAREClassesAppID{2206CDB0-19C1-11D1-89E0-00C04FD7A829}

  1. Right-click {2206CDB0-19C1-11D1-89E0-00C04FD7A829}, click Permissions, and then click Advanced.

  2. On the Owner tab, under Change owner to, click Administrators, or click the user account that you want, and then click Apply.

  3. On the Permissions tab, grant the Administrators group or the user account Full Control permissions, and then click OK two times.

  4. Exit Registry Editor.

  5. Click Start, type Dcomcnfg in the Search programs and files box, and then press Enter.

  6. Expand Component Services, expand Computers, expand My Computer, and then click DCOM Config.

  7. Right-click MSDAINITIALIZE, and then click Properties.

  8. On the Security tab, under Launch and Activation Permissions, click Customize, and then click Edit.

  9. Add the Administrators group or the user account that you want.

Note

If you are running Windows Vista or a later version of Windows, add the user or group that you specified in step 4.

  1. Click to select the Administrators group or the user account

13. Under Permissions for SYSTEM, click to select the following check boxes under Allow: * Local Launch * Remote Launch * Local Activation * Remote Activation

Note

If you connect to SQL Server by using a SQL Server account, you must grant these permissions to the account under which SQL Server is running.

  1. Click OK.

  2. On the Security tab, under Access Permissions, click Customize, and then click Edit.

16. Click the Administrators group or user account, and then click to select the following check boxes under Allow: * Local Access * Remote Access

Note

  1. Click OK.

  2. On the Security tab, under Configuration Permissions, click Customize, and then click Edit.

  3. Click the Administrator group or the user account, and then click to select the Full Control check box under Allow.

  4. Click OK two times.

  5. Exit Component Services.

More information

In Windows Vista and later versions, the TrustedInstaller system account owns the MSDAINITIALIZE DCOM class. The TrustedInstaller system account is the only account that has write permissions on the MSDAINITIALIZE class. Therefore, in Windows Vista and later versions, you must perform steps 1 through 6 in the “Resolution” section before you edit the properties of the MSDAINITIALIZE class.

When creating a linked server instance you must set “Catalog” property to an existing database. When querying linked server, databases other than the one specified in the “Catalog” options won’t be available.