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. To enable this, from SSMS, navigate to Object Explorer > Server Objects > Linked Servers > Providers, right-click "SQLOLEDB", and select Properties > General > Provider options: > Allow inprocess. Confirm the change by clicking OK.

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 Services tab.
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 SSH Tunnel.

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:
   • Linked server: Name the linked server.
   • Provider: Select Microsoft OLE DB Driver for SQL Server or the latest Microsoft ODBC Driver for SQL Server.
   • Data Source: Enter the hostname and port of the service, separated by a comma. If you are connecting through Tunneling, provide the mapped IP/Port of the tunnel. Note that a value of "localhost" refers to the machine where SQL Server is running, so be aware of that when your SQL Gateway service and your SQL Server instance are running on different machines.
   • Catalog: Enter the system DSN as the catalog. For example, "CData ZohoCreator 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 Users 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 MSOLEDBSQL for the Microsoft OLE DB Driver for SQL Server or the latest Microsoft ODBC Driver for SQL Server.
• Datasrc: The host and port the service is running on, separated by a comma. Note that a value of "localhost" refers to the machine where SQL Server is running, so be aware of that when your SQL Gateway service and your SQL Server instance are running on different machines.
• Catalog: Enter the system DSN configured for the service. For example, "CData ZohoCreator Sys".
• 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='ZohoCreator', 
   @provider='MSOLEDBSQL', 
   @datasrc='<Machine IP address>,1434', 
   @catalog='CData ZohoCreator 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='ZohoCreator', 
   @rmtuser='admin', 
   @rmtpassword='test', 
   @useself='FALSE', 
   @locallogin=NULL; 
   GO