為了避免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…
(3)執行TestDbConnectionEncrypted 測試方法,攔截到的封包
Records: [ApplicationData(Encrypted)]
小結
- 保護了資料傳輸,解了fortify issue。
- 沒想到從SQL Server 2008就提供了”連線使用加密而不需驗證”,學藝不精再添一筆。
- 書到用時方恨少,此恨綿綿無絕期。
- 再也不擔心客戶不採購憑證了,不過還是要建議客戶買,多驗證憑證更安全。
哈!沒想到今年金靴獎是西班牙隊長-帶刀後衛-Ramos,。
2018/06 試吃油飯的路上
參考
Connection String Syntax
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/connection-string-syntax
使用加密而不需驗證
https://docs.microsoft.com/zh-tw/previous-versions/sql/sql-server-2008/ms131691(v=sql.100)
Insecure Transport: Database
https://vulncat.fortify.com/zh-tw/detail?id=desc.semantic.dotnet.insecure_transport_database