Summary:
This article provides information on ways to connect Azure SQL (SaaS) and SQL on Azure to Solver Cloud as well as troubleshooting for common issues/errors.
Article Sections:
More Information:
For Azure SQL (SaaS)
Note: We pass off authentication credentials via the SQL connector. There is no difference if the SQL server is internal and the end user is trying to connect to this server from their local machine. The only difference is the SQL database in Azure can be publicly accessible, while on-premise typically is not.
- 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.
- Gateway will work but it's recommended to use SQL Connector.
- 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 currently supported.
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.
Troubleshooting
Issue: Azure SQL (SaaS) unable to login via SQL Connector
Symptoms: When a user follows the instructions in the above section, they find that they are unable to create a 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 LOGIN button.
The application returns a timestamp error.
Checking the built-in Developer Tools in the web browser, the application returns an error:
message: "Access is denied"
Causes: 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.
Resolution: 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 following Microsoft KB articles for more information on how to configure your Azure environment. Click on below links to access Microsoft documents:
- Azure SQL Connectivity Settings
- Azure SQL Database and Azure Synapse Analytics network access controls
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 suggests 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. |
Properties:
Cloud
Comments
1 comment
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/
Please sign in to leave a comment.