This section describes how to configure and create a linked SQL Server instance.

Configure the Provider Options

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 inprocessoption 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.

Configure SQL Gateway

Configuring remote access to an ODBC data source in the SQL Gateway administration tool consists of the following steps:

1. Configure the proxying service on the Servicestab.
2. Configure database users on the Users tab.
3. Configure TLS/SSL on the Other tab.
4. Access services hosted behind a firewall by configuring Tunneling.

Create a Linked Server from the UI

To create a linked server from the UI in SQL Server Management Studio:

1. Open SQL Server Management Studio and connect to an instance of SQL Server.
2. Expand the Server Objects node, right-click Linked Servers, and select New Linked Server. The New Linked Server dialog is displayed.
3. 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 hostname and port of the service. If you are connecting through Tunneling then provide the mapped IP/Port of the tunnel. 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 Workday Sys".
4. 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: eter 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:

1. Call sp_addlinkedserver to create the linked server:
   

   EXEC sp_addlinkedserver @server='Workday', 
   @provider='SQLNCLI', 
   @datasrc='<Machine IP address>,1434', 
   @catalog='CData Workday Sys'; 
   GO 

2. 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='Workday', 
   @rmtuser='admin', 
   @rmtpassword='test', 
   @useself='FALSE', 
   @locallogin=NULL; 
   GO