Skip to main content

Getting Started - SQL Connector

Cassie from Solver Support
  • Updated
Print Friendly and PDF

Overview

This article consists of two parts:

  1. Provides a brief summary on ways to connect Azure SQL (SaaS) and SQL on Azure to Solver Cloud
  2. Troubleshooting

For Azure SQL (SaaS)

Gateway

    • Gateway will work but it's recommended to use SQL Connector.
    • Note: A port will need to be opened on Azure SQL to allow for communication.

SQL Connector

    • Users can connect directly from Cloud as it is on the same named network. 
    • Server address should be in the format as shown on the below screenshot. 
    • Note: Currently we only support Active Directory and SQL authentication. Azure AD is not supported at the moment. 

Note: We pass off authentication credentials via the SQL connector. It's not different from if the SQL server is internal and the end user is trying to connect to this server from their local machines. The only difference is the SQL database in Azure can be publicly accessible, while on-premise is typically not the same case.

For SQL database hosted on Azure

Gateway

    • The user will need to configure a firewall rule or setup a VPN - an FQDN or IP address assuming they integrate it into their existing network and maintain a connection.
    • For more details on how to install the Gateway application and setup data connections, reference this user guide.

Troubleshooting

Azure SQL (SaaS) unable to login via SQL Connector

Currently we only support Active Directory and SQL authentication. Azure AD is not supported at the moment. 

Following the instructions in the above section, user finds that they are unable to create new integration in Cloud using the SQL connector. However, the same credential works when they access Azure SQL through SSMS on their local machine. 

After filling out the required fields, the user clicks the LOG IN button. 

The application returns a timestamp error. 

Checking the built-in Developer Tools in the web browser (see this KB for instructions on how to utilize dev tools to diagnose integration issues), the application returns a "Access is denied" error. 

Although Azure SQL is public, it is possible that the organization has a locked down Azure environment and has restricted IP access, which is why the user is able to connect via SSMS as the server is on an approved domain but not via Cloud integration, since the user is accessing Azure SQL through a different IP address.

Solver can provide a distinct IP address or an explicit IP list for organization to whitelist, however, Solver does change IP address on Azure without notice.

It is recommended that the organization configures their Azure environment to allow for inner Azure SQL communication to avoid random integration failures. This is a setting within the organization's Azure environment. We recommend referring to the Microsoft KB articles for more information on how to configure your Azure environment. 

Click on below links to access Microsoft documents:

Common Error Messages

Error Message Cause Solution
The service has encountered an error processing your request. Please try again. Error code 701. A severe error occurred on the current command. The results, if any, should be discarded. Warning: Null value is eliminated by an aggregate or other SET operation. This error message hints to insufficient memory/resource on the customer's Azure SQL side. 

Review the size of the data load and the database, as well as the queries being used for integrations to ensure resources are not being maximized during integrations.

Reduce the size of data load as needed.

    Attachments

Related articles

Comments

1 comment

Date Votes
  • Nicolai Pedersen
    • Edited

    Hi, we have a client that potentially wants to connect their Xero accounting application with Solver Cloud. Xero has an ODBC Driver available for application to connect to. Can the SQL connector in Solver cloud be used connect to Xero?

    Xero ODBC Driver
    https://www.cdata.com/drivers/xero/odbc/

    Alternatively Xero has an API, but since the client is already has the SQL Connector it would make sense to use that is possible.

    Alternative Xero API
    https://developer.xero.com/

    0

Please sign in to leave a comment.

Powered by Zendesk