SQL Gateway Overview - Dataverse PowerApps
The SQL Gateway is an installed application that allows you to expose ODBC data sources as if they were MySQL or SQL Server databases. This article provides a detailed overview of the SQL Gateway, its functionality, and its features.
Contents
What Does the SQL Gateway Do?
The SQL Gateway allows remote access to ODBC data sources, based on the MySQL or SQL Server (TDS) protocols. Using the simple point-and-click administration tool, you can configure interfaces for any ODBC driver, native or 3rd party, free or paid. Since the SQL Gateway is installed directly on your server, you can easily provide local and remote access to the interface.
MySQL Remoting
You can leverage MySQL remoting to access your ODBC data sources in tools, languages, and platforms that natively support connectivity to MySQL databases, like Google Apps Script, PHP, Jaspersoft, Crystal Reports, and many more. With SQL Gateway, you can work with any of your ODBC data sources as if they were a MySQL database.
SQL Server (TDS) Remoting
With TDS remoting, you get a simpler way to configure access to your ODBC data source as a linked server in SQL Server. By using the native SQL Server client to communicate with the data source, you get better performance and familiar, SQL-92 access to your data. And you get access to your data directly through the ODBC drivers, a trusted, high-performing technology.
Configuring the SQL Gateway
After installing the SQL Gateway on the machine where your DSN is installed, follow the steps below to start remoting services to proxy MySQL and SQL Server commands to an ODBC driver.
Creating Remoting Services
The SQL Gateway connects through System DSNs for ODBC drivers, so you will need Administrator access to create new remoting services. The steps to create a new service are below.
- If you wish to run the services as Windows services, select the Run as a Windows Service box on the Status tab.
- Click the Services tab, click Add, and name the service.
- Select the database type: SQL Server or MySQL.
- Select a system DSN to connect to in the Data Source menu. Refer to the help documentation for your data source to specify authentication and other values in a DSN.
- To secure client connections, enable the SSL option. You will also need to configure a digital certificate on the Other tab.
- Select the local port the service will run on.
- Select "Expose on Gateway" if you are using the Gateway feature to connect across the firewall through reverse SSH tunneling.
- Choose a startup option and configure any logging.
Creating Users
Navigate to the Users tab and click Add to create and configure a new user. Apply permissions for each service created. Or, select the Administrator option to allow the user to have full access to all services.
![Adding a New User.](sqlgateway-overview-3.png)
Connecting Through an SSH Tunnel
If you wish to use reverse SSH tunneling to connect to the SQL Gateway, navigate to the SSH Tunnel tab and configure the SQL Gateway to connect to your hosted SSH server.
![Configuring the SSH Tunnel.](sqlgateway-overview-4.png)
Starting the Remoting Services
Once the services, users, and Gateway are configured, you can start your remoting services. Navigate to the Services tab, select a service, and click Start.
![Starting the Remoting Services.](sqlgateway-overview-5.png)
With the services running, you are ready to connect to your ODBC data sources through MySQL or TDS remoting. Simply configure your tool or application to point to the server running the app (or the SSH server if you are using the Gateway) and you are ready to begin!
How Do I Get Started?
The SQL Gateway is currently available for machines running Windows. Download the SQL Gateway and create MySQL and SQL Server (TDS) remoting services for bidirectional access to live data from any ODBC data source. ODBC remoting gives you unparalleled freedom in where and how you work with data from your ODBC data sources!
As always, our world-class support team is here to help. If you have any questions about the SQL Gateway or the CData drivers please let us know.
More information about the SQL Gateway can be found in the following resources:
posted on 2021-09-23 11:13 lingdanglfw 阅读(136) 评论(0) 编辑 收藏 举报