function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
dmchengdmcheng 

Lightning Connect validation error with Microsoft Azure SQL DB

I'm testing Lightning Connect with a Microsoft Azure SQL db.  I'm getting an error when validating the external data source in Salesforce.  Here's the config:
external data source config

When I click Validate and Sync, I get this error:
The external system is unreachable. Make sure that the URL is correct in the external data source settings, and that the server's SSL certificate is valid for its domain name. Attempted to reach this URL: https://[myservername].database.windows.net:1433/$metadata

I have tried Anonymous authentication but I get the same error.

I've set up Azure firewall rules for the Salesforce IP addresses.
Best Answer chosen by dmcheng
DatadirectGuyDatadirectGuy
Are you familiar with this tutorial (https://developer.salesforce.com/blogs/developer-relations/2015/03/accessing-sql-server-azure-database-lightning-connect.html)? The author walks through exposing Azure data as OData - have you gone through similar steps? She ends up with a URI ending in .svc.

Your address looks like what you would use for traditional SQL connectivity. Port 1433 is where the SQL Server listener responds to requests for ODBC and JDBC and ADO.NET clients.  There is no OData service exposed there.

All Answers

DatadirectGuyDatadirectGuy
Have you tried to validate your connectivity using something like the Postman (https://chrome.google.com/webstore/detail/postman/fhbjgbiflinjbdggehcddcbncdddomop?hl=en) utility? It's built on Chrome and may help you see what's going on. 
DatadirectGuyDatadirectGuy
Are you familiar with this tutorial (https://developer.salesforce.com/blogs/developer-relations/2015/03/accessing-sql-server-azure-database-lightning-connect.html)? The author walks through exposing Azure data as OData - have you gone through similar steps? She ends up with a URI ending in .svc.

Your address looks like what you would use for traditional SQL connectivity. Port 1433 is where the SQL Server listener responds to requests for ODBC and JDBC and ADO.NET clients.  There is no OData service exposed there.
This was selected as the best answer
dmchengdmcheng
Thanks, I didn't realize that I had to build an Odata service on the Azure side.
DatadirectGuyDatadirectGuy
Glad to be of assistance - Of the traditional enterprise databases, newer versions of Sybase ASE appear to have the most straightfoward way of providing OData support (http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc31644.1600/doc/html/mas1396546016074.html). Others (Oracle, SQL Server, DB2, Informix, etc.) either require additional services or libraries to be installed or configured like that article describes or like Apache Olingo (https://olingo.apache.org/), or some sort of a gateway service similar to our DataDirect Cloud service to provide an OData "front end" on a data source. 

I think that the description of Lightning Connect as being able to connect to any OData data source sometimes sets an overly simplistic or optimistic expectation for those first investigating using Lightning Connect.