Apply SSL between SQL sever & Application

Most of systems are not consider about security between database server and application.But it is a critical factor since most of the attacks are coming from inside the organization.
So in this article I try to explain how to enable SSL channel between SQL server and your application.

Main Steps:

Install IIS server.

Install certificate server.

Install SQL server.

Prepare Certificate for apply SSL on SQL server.

Prepare SSL channel.

NOTE - First 4 steps should be done in same machine that you are going to install SQL server.
In this document I will explain important points on these steps.

Help on installing certificate server.
Important Steps:

Click install windows components from add or remove programs.

Select certificate authority.

Create root certificate.Fill common name attribute with your machine name.

Prepare Certificate for apply SSL on SQL server.
Important Steps:
Create Request -

Go to http://machineName/certsrv

Select "Request a certificate", and click Next.

Select "Advanced Request", and click Next.

Select "Submit a certificate request to this CA using a form", and click Next.

Fill the form you should use your SQL server name as Name.

Select "Server Authentication Certificate" as Intended Purpose.

Select "Use local machine store" and click Submit.

Now you have request a certificate and your request is in pending state.

Issue Certificate -

Go to Run, type mmc.

Follow the following path

Console -> Add/Remove snap in -> Add -> select Certificate Authority -> Add -> Select Local Computer -> Finish -> Close -> OK -> Expand Certificate Authority -> Pending Requests.

Now you can see your request.

Right click on request and click issue.

Install Certificate -

Go to http://machineName/certsrv

Select "Check on a pending certificate" and click Next.

Select your certificate and click Next.

Click install certificate.

Now you are ready to prepare SSL channel between SQL server and your application.This can be basically done in two ways.

From server side

From client side

Apply SSL from server side.
If you apply SSL on this way, all the client connections will get encrypted.
Steps:

Go to Server Network Utility tool on server machine.

Select "Force protocol encryption", click Apply and OK.

Now restart your SQL server.

Apply SSL from client side.
To do this client should have root certificate of the certification authority that issue certificate to the SQL server.

From server machine export the root certificate as .p7b.
Export root certificate -

Open Internet Explorer and follow the following path:

Tools -> Internet Options -> Content -> Certificates -> Trusted root certificate authorities -> Select your root certificate -> Export -> Next -> Select .p7B format -> Select "Include all certificates in the certification path if possible" -> Select place to save your certificate -> Next -> Finish.

Import certificate to client machine -

Open Internet Explorer on client machine.

Follow the following path:

Tools -> Internet Options -> Content -> Certificates -> Trusted root certificate authorities -> Select your root certificate -> Import -> Next -> Browse to certificate location -> Next -> Accept the certificate.

Now you can configure SSL from client machine.
You can configure SSL from ODBC connection manually or programatically.
Configure SSL programatically.
Sample connection strings:
ODBC
"Driver=SQLServer;Server=Protect-Drive;UID=sa;PWD=123;Network=DBNETLIB.DLL;Encrypt=YES "
OLEDB
"Provider=sqloledb;Data Source=Protect-Drive;Initial Catalog=slt;User Id=sa;Password=123;use encryption for data=true"

Configure SSL manually through ODBC.

Select "Use strong encryption" when you configure ODBC connection.

Note - if you don't have "Use strong encryption" option when you configure ODBC connection.You may need to install SQL client tools on client machine.

Best wishers,
If you have any problem with configuring SSL on SQL server please let me know.