This section describes how to configure and create a linked SQL Server instance.
Linked Server Benefits
You can use SQL Linked Server to connect to the CData SQL Gateway. SQL Server treats the CData SQL Gateway as a linked SQL Server instance, so you can write full SQL queries to query your HPCC data without losing performance as would happen connecting to an ODBC data source directly. The connection can be made locally or to the Gateway located on a separate machine.
Configure the Linked Server
Configuring remote access to an ODBC data source in the SQL Gateway administration tool consists of the following steps:
- Configure the proxying service on the Services tab.
- Configure database users on the Users tab.
- Configure TLS/SSL on the Other tab.
- Access services hosted behind a firewall by configuring the Tunneling.
Create a Linked Server from the UI
To create a linked server from the UI in SQL Server Management Studio:
- Open SQL Server Management Studio and connect to an instance of SQL Server.
- Expand the Server Objects node, right-click Linked Servers, and click New Linked Server. The New Linked Server dialog is displayed.
- In the General section, select Other Data Source and enter the following information after naming the linked server:
- Provider: Select the SQL Server Native Client Provider that corresponds to your version of SQL Server. For example, SQL Server Native Client 11.0.
- Data Source: Enter the host and port the service is running on, separated by a comma. For example, "localhost,1434". If you are connecting off-network through the SSH Tunnel enter the host and port of your SSH host. Note that a value of "localhost" in the Data Source input refers to the machine where SQL Server is running, so be careful when creating a linked server in Management Studio when not running on the same machine as SQL Server.
- Catalog: Enter the system DSN as the catalog. For example, "CData HPCC Sys".
- In the Security section, select Be Made Using this Security Context and enter the username and password of a user you authorized on the SSH Tunnel tab in the SQL Gateway administration console.
Create a Linked Server Programmatically
In addition to using the SQL Server Management Studio UI to create a linked server, you can use stored procedures. The following inputs are required to create the linked server:
- Server: The linked server name.
- Provider: Enter "SQLNCLI", for the SQL Server Native Client Provider.
- Datasrc: The host and port the service is running on, separated by a comma. Note that a value of "localhost" in the datasrc input refers to the machine where SQL Server is running, so be careful when creating a linked server in Management Studio when not running on the same machine as SQL Server.
- Catalog: Enter the system DSN configured for the service.
- Srvproduct: Enter the product name of the data source; this can be an arbitrary value, such as "CData SQL Gateway" or an empty string.
To create the linked server and configure authentication to the SQL Gateway:
- Call sp_addlinkedserver to create the linked server:
EXEC sp_addlinkedserver @server='HPCC',
@srvproduct = '',
@datasrc='<Machine IP address>,1434',
@catalog='CData HPCC Sys';
- Call sp_addlinkedsrvlogin to allow SQL Server users to connect with the credentials of an authorized user of the service. Note that the credentials you use to connect to the service must map to a user you defined on the Users tab in the SQL Gateway administration tool.
EXEC sp_addlinkedsrvlogin @rmtsrvname='HPCC',
Connect from SQL Server Management Studio
SQL Server Management Studio uses the SQL Server Client OLE DB provider, which requires the ODBC driver to be used inprocess. You must enable the Allow inprocess option for the SQL Server Client Provider in Management Studio to query the linked server from SQL Server Management Studio. To do this, open the properties for the provider you are using under Server Objects > Linked Servers > Providers.