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.

 lightning_connect

03. Setting up Salesforce Connect to access your endpoint

Quick Start: Lightning Connect Tutorial:  https://developer.salesforce.com/trailhead/project/quickstart-lightning-connect