[Fortify]Critical: Insecure Transport: Database解決(啟用SQL加密連線)

2018-06-14

6033
0
Fortify
2018-06-18

為了避免SQL Server與AP伺服器傳輸時的內容被攔截竊取後容易解析，我們可以採用加密的傳輸通道，一來可以增加傳輸封包安全性，二來也避免源碼檢測工具打小報告。

以前都是在DB Server手動裝憑證，然後DB匯出，AP伺服器匯入再設定連線字串啟用加密，~~才能啟用加密連線(大誤)~~。最近和同事討論，意外發現，即使沒有額外處理憑證，單只要AP連線字串加上TrustServerCertificate=true;Encrypt=true，實際連線時，SQL會自動產生憑證，重要的TDS封包就被加密了，而且專案中的組態檔還可以通過源碼檢測的盤查。

*TDS 表格式資料封包(Tabular Data Stream)

來啟用SQL加密連線吧。

Encryption always occurs, but may use a self-signed server certificate.

測試環境準備

打算寫一隻小程式從資料庫撈出今年世界盃金靴獎的得主。

(1)建立測試用資料庫

CREATE DATABASE FIFAWorldCup2018
GO

USE FIFAWorldCup2018;
GO 

Create Table Player
(
    PlayerId int identity primary key clustered,
    name nvarchar(30),
    CountryName nvarchar(30) 
) 
insert into Player values('Sergio Ramos','Spain'),('Andrés Iniesta','Spain'),('Isco','Spain'),('Isco','Spain')

(2)打開Visual Studio，寫一小段測試方法

[TestMethod]
public void TestDbConnectionNotEncrypted()
{
    //SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
    //builder.DataSource = @"STANLEY14\SQL2017,14331";
    //builder.InitialCatalog = "FIFAWorldCup2018";
    //builder.IntegratedSecurity = true;
    //Console.WriteLine(GetGoldenBootPlayerName(builder.ConnectionString));
    Console.WriteLine(GetGoldenBootPlayerName(ConfigurationManager.ConnectionStrings["FortifyConnectionString"].ConnectionString));
    Assert.AreEqual("Sergio Ramos", GetGoldenBootPlayerName(ConfigurationManager.ConnectionStrings["FortifyConnectionString"].ConnectionString));
}

[TestMethod]
public void TestDbConnectionEncrypted()
{
    //SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
    //builder.DataSource = @"STANLEY14\SQL2017,14331";
    //builder.InitialCatalog = "FIFAWorldCup2018";
    //builder.IntegratedSecurity = true;
    //Console.WriteLine(GetGoldenBootPlayerName(builder.ConnectionString));
    //builder.IntegratedSecurity = true;
    //builder.Encrypt = true;
    //builder.TrustServerCertificate = true;
    Console.WriteLine(GetGoldenBootPlayerName(ConfigurationManager.ConnectionStrings["FortifyConnectionString"].ConnectionString));
    Assert.AreEqual("Sergio Ramos", GetGoldenBootPlayerName(ConfigurationManager.ConnectionStrings["FortifyConnectionString"].ConnectionString));
}

public string GetGoldenBootPlayerName(string connstr)
{
    using (SqlConnection conn = new SqlConnection(connstr))
    {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand("", conn))
        {
            cmd.CommandText = "Select Top 1 Name From player ";
            DataTable dt = new DataTable();
            using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
            {
                adp.Fill(dt);
            }
            if (dt != null && dt.Rows.Count == 1)
            {
                return dt.Rows[0][0].ToString();
            }
        }
    }
    return "";
}

未啟用連線加密前:

(1)打開app.config檔案，編輯如下:

<add name="FortifyConnectionString" connectionString="Data Source=STANLEY14\SQL2017,14331; IntegratedSecurity=true" providerName="System.Data.SqlClient" />

(2)執行源碼檢測，結果如下:

Critical: Insecure Transport: Database

(3)以管理員身份打開Microsoft Message Analyzer 解析SQL封包，然後啟動Session，可以Filter tcp.port=14331。

(4)執行TestDbConnectionNotEncrypted 測試方法，攔截到的封包

可以找到原始輸入的SQLText，很透明。

啟動連線加密後

(1)打開app.config檔案，編輯如下:

<add name="FortifyConnectionString" connectionString="Data Source=STANLEY14\SQL2017,14331; 
IntegratedSecurity=true; TrustServerCertificate=true;Encrypt=true " providerName="System.Data.SqlClient" />

(2)執行源碼檢測，結果如下: say goodbye(揮手)，Fortify issue…