/
Secure SQL Server Cluster

Secure SQL Server Cluster

Owned by Amit Poddar (Unlicensed)
Last updated: May 26, 2014

A dedicated three node SQL Server cluster has been created for hosting databases that contain regulated data, all databases hosted on this cluster will be encrypted and only encrypted connections will be allowed to this cluster. Clients are recommended to use server name validation when establishing SSL connection with the database. Here we detail ways of establishing secure connection using different clients. But first we need to import the public key certificate of the certification authority, Global Sign, that signed the server certificate, as a trusted authority.

a) Import Global Sign public key certificate as trusted certification authority.

  1. Download the global sign certificates packaged as PKCS7 package.
  2. Importing PKCS7 package into windows trust store.




























          

          

          

3. Importing Global Sign certificates into java keystore.

             keytool -importcert  -file GlobalSignIntermediate.cer -alias GlobalSignIntermediate -storepass storepass -keystore keystore

      keytool -importcert  -file GlobalSignRoot.cer -alias GlobalSignRoot -storepass storepass -keystore keystore

 

b) Connect using SQL Server Management Studio

 

c) Connect with Java using Microsoft's JDBC driver

import java.sql.*;
import com.microsoft.sqlserver.jdbc.*;

public class Main {
    public static void main(String[] args)
       throws SQLException {
        String connectionUrl =
                "jdbc:sqlserver://sqlseclab-lsnr1.its.yale.edu:1433;" +
                "databaseName=master;user=sa;password=userpassword;" +
                "encrypt=true;trustServerCertificate=false;" +
                "trustStore=/Users/mpoddar/IdeaProjects/keystore;" +
                "trustStorePassword=keystorepass";
        Connection connection = DriverManager.getConnection(connectionUrl);
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("select GetDate()");
        while (resultSet.next()) {
            System.out.println(resultSet.getString(1));
        }
    }
}

 

c) Connect with Java using JTDS JDBC driver

import java.sql.*;
import net.sourceforge.jtds.jdbc.*;

public class Main {
    public static void main(String[] args)
       throws SQLException {
        System.setProperty("javax.net.ssl.trustStore", "/Users/mpoddar/IdeaProjects/keystore" );
        System.setProperty("javax.net.ssl.trustStorePassword","keystorepassword");
        String connectionUrl =
                "jdbc:jtds:sqlserver://sqlseclab-lsnr1.its.yale.edu:1433/master;" +
                        "domain=yale;user=ap349;password=password;useNTLMv2=true;" +
                        "ssl=authenticate";
        Connection connection = DriverManager.getConnection(connectionUrl);
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("select GetDate()");
        while (resultSet.next()) {
            System.out.println(resultSet.getString(1));
        }
    }
}

 

d) Connect with C#

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;

namespace SecureSQL
{
    class Program
    {
        static void Main(string[] args)
        {
            using (SqlConnection connection = new SqlConnection())
            {
                connection.ConnectionString = "Server=sqlseclab-lsnr1.its.yale.edu;" + 
                                              "Initial Catalog=master;" +
                                              "Integrated Security=true;" +
                                              "Encrypt=True;TrustServerCertificate=False";
                connection.Open();
                using (SqlCommand command = connection.CreateCommand())
                {
                    command.CommandText = "select GetDate()";
                    command.CommandType = CommandType.Text;

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine("{0}", reader[0]);
                        }
                    }
                }

                Console.ReadLine();
            }
        }
    }
}


e) Connect with Linked server

USE [master]
GO

EXEC master.dbo.sp_addlinkedserver 
      @server = N'SECURE_LINKED_SERVER', 
	  @srvproduct=N'', 
	  @provider=N'SQLNCLI11', 
	  @datasrc=N'sqlseclab-lsnr1.its.yale.edu', 
	  @provstr=N'Encrypt=yes;Trust Server Certificate=False', 
	  @catalog=N'master'

EXEC master.dbo.sp_addlinkedsrvlogin 
      @rmtsrvname=N'SECURE_LINKED_SERVER',
	  @useself=N'False',
	  @locallogin=NULL,
	  @rmtuser=N'sa',
	  @rmtpassword='########'

GO

 

f) Connect with ODBC Data Source