Setting up Salesforce Connect is a three step process. Here are the three steps.
01. Create OData Endpoints
Alternative 1: Generating your own OData endpoint
Step 1. Expose SQL Server Database tables as OData endpoint:
Here’s a quick note from Microsoft on how to configure an OData endpoint for MS SQL Server:
Creating OData Endpoint for MS Sql Server: http://msdn.microsoft.com/en-us/library/cc668810%28v=vs.110%29.aspx
Alternative 2: Using a middle-ware to generate OData endpoint
On the other hand if you have an middleware connector, you may be able to eliminate step 1 and possibly step 2. Connectors like Jitterbit, Progress, Mulesoft, Informatica and many others, can expose your data source as OData endpoint. This endpoint can be then used by Salesforce to pull data from your data source.
02. Allow Salesforce to access your endpoint through your inbound firewall
Your network administrator would be able to help you in this.
The following diagram illustrates how Salesforce Lightning Connect communicates with MS SQL Server database.
03. Setting up Salesforce Connect to access your endpoint
Quick Start: Lightning Connect Tutorial: https://developer.salesforce.com/trailhead/project/quickstart-lightning-connect