[ADO] Win8/8.1/2012/2012 R2 ConnectionString 預設回傳值將會移除密碼

話說,有隻舊程式遷移到 Windows 2012 後,會在SQL Server 資料庫發生登入錯誤,但是在 Win2k/XP/2013/Vista/2008/7/R2 時,則正常。

查了好久,才發現基於安全性理由,預設回傳值會把密碼移除,如果用物件傳回連線字串,再做下次連線,就會碰上此問題。

測試程式碼如下:

Private p_Count As Long
Private adoConn As ADODB.Connection

Private Sub cmdQuery_Click()
   If p_Count = 0 Then
      nowConnectionString = txtConnectionString.Text
   Else
      If adoConn.State <> adStateClosed Then
         adoConn.Close
         Do
            DoEvents
         Loop Until adoConn.State = adStateClosed
      End If

      nowConnectionString = adoConn.ConnectionString
   End If
   
   Dim nowConn As New ADODB.Connection
   nowConn.CursorLocation = adUseClient
   nowConn.ConnectionString = nowConnectionString
   
   p_Count = p_Count + 1
   
   txtLog.Text = txtLog.Text & vbNewLine & p_Count & " -> " & nowConnectionString
   
   Set adoConn = Nothing
   Set adoConn = nowConn
   Set nowConn = Nothing
   
   adoConn.Open
   
   Dim nowRS As New ADODB.Recordset
   
   nowRS.Open txtSQLQuery.Text, adoConn, adOpenStatic, adLockReadOnly
   
   nowRS.Close
End Sub

從上面可看到透過這句取得連線字串:

nowConnectionString = adoConn.ConnectionString

跑出來的結果會變成下圖:

回傳連線字串短少密碼

回傳連線字串短少密碼

如圖,即使密碼放在連線字串中間,也會被移除,因此用被移除的連線字串登入資料庫,就會發生此錯誤。

程式碼中若有此設計,應透過暫存變數來保存原始連線字串,避免被安全性阻擋。

參考文件:http://msdn.microsoft.com/en-us/library/windows/desktop/ms676709.aspx

For example, if the ADO Connection dynamic property Persist Security Info is set to True, password information is included in the connection string returned. Otherwise, if the underlying provider has set the property to False (for example with the SQLOLEDB provider), password information is omitted in the returned connection string.

最後,這個轉換程式碼為網頁展示用的還不錯,不過還是那句老話,wordpress 不支援~~~

http://www.manoli.net/csharpformat/

 

Categories: 工作點滴, 技術分享 | Tags: , , , | 發表留言

文章導覽

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

在 WordPress.com 建立免費網站或網誌.

%d 位部落客按了讚: