見鬼的MSDTC設定

今天要在設定好的連結伺服器(Linked Server)上執行一段包Transaction的SQL語法，發現單測SQL String被BEGIN TRAN 或是BEGIN DISTRIBUTED TRAN包起來後就會出問題，錯誤訊息大概是長的像下面這樣。

伺服器無法處理要求。 ---> 無法執行作業，因為連結伺服器 "DBNAME" 的 OLE DB 提供者 "SQLNCLI" 無法開始分散式交易。連結伺服器 "DBNAME" 的 OLE DB 提供者 "SQLNCLI" 傳回訊息 "協力電腦異動管理員已經停用了對遠端/網路異動的支援。"。

問過我家的DBA，他說看起來是網路問題，他不知道......於是又拜了Google大神求解答，看來看去，好像很多種可能，也很多種解答，只好一個一個試。

從為何我利用分散式跨資料庫交易還是失敗呢?這篇開始試，先跑了下面這串SQL，開始抓問題在哪。

SET XACT_ABORT ON
BEGIN DISTRIBUTED TRAN;
SELECT * FROM OPENDATASOURCE('SQLOLEDB','Data Source =DBNAME;User ID=USERNAME;Password=PWD').資料庫名稱.dbo.資料表名稱
COMMIT TRAN;

結果出現下面這個錯誤

訊息 15281，層級 16，狀態 1，行 1
SQL Server 已封鎖元件 'Ad Hoc Distributed Queries' 的 STATEMENT 'OpenRowset/OpenDatasource'
之存取，因為此元件已經由此伺服器的安全性組態關閉。系統管理員可以使用 sp_configure 來啟用
'Ad Hoc Distributed Queries' 的使用。如需有關啟用 'Ad Hoc Distributed Queries' 的詳細資訊
，請參閱《SQL Server 線上叢書》中的＜介面區組態＞(Surface Area Configuration)。

查了一下，這問題好解，有兩個方法:
1.執行下面這段SQL開啟Ad Hoc Distributed Queries就好

exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

2.用介面去開啟，[Microsoft SQL Server 2005]->[組態工具]->[SQL Server介面區組態]。



開出來後，選[介面的功能組態]。



然後在[特定遠端查詢]裡，把[啟用OPENROWSET和OPENDATASOURCE支援]打勾，然後確定。

PS:SQL Server 2008 介面區組態(Surface Area Configuration，SAC)：啟用 xp_cmdshell這篇有很詳細的介紹介面區組態，可以參考一下。


完成了之後，再去試剛剛的那段測試SQL String，結果錯誤訊息變成了下面這個

回覆訊息:連結伺服器"DBNAME"的OLE DB提供者"SQLNCLI"傳回訊息"沒有作用中的異動。"
訊息7391,層級16,狀態2,行2
無法執行作業,因為連結伺服器"DBNAME"的OLE DB提供者"SQLNCLI"無法開始分散式交易。

之後，再來試如何疑難排解 MS DTC 防火牆問題這篇裡說的，雖然微軟的文章寫了很多，不過我直接抓DTCPing下來測能不能通比較快。這個DTCPing使用時，在兩台要連的機器上都開起來，然後要呼叫的那台打上被呼叫的主機名稱，按下PING，就可以開始測試了。


第一次測在DTC Binding Test這段Fail，查了一下兩台的Log，發現下面這個錯誤訊息。

gethostbyname can not resolve SERVEERNAME

找不到HostName據我所知有兩個解法，一是DNS上面加，二是自己的Host上面加，我用第二個方法比較省事。

在%SystemRoot%\system32\drivers\etc\下面有個叫hosts的檔案，用文字編輯器打開後，加入你的IP跟HostName就好。例如209.85.171.100 www.google.com


加了Hosts後，再執行一次DTCPing(記得兩邊都要重開DTCPing)，發現可以通了，於是網路問題排除，再繼續找。來試如何啟用網路 DTC 存取，在 Windows Server 2003，看了一下，發現兩台機器都沒裝DTC，難怪不會跑啊Orz...於是照著上面的步驟一步一步裝。

這邊抄微軟的說明
1.按一下 [開始] ，指向 [控制台] 中，然後再按一下[新增或移除程式] 。
2.按一下 [新增 / 移除 Windows 元件 ]。
3.選取 [應用程式伺服器 ]，然後按一下 [詳細資料] 。 (就是Application Server)


4.選取 [ 啟用網路 DTC 存取 ，然後按一下 [ 確定 ]。


5.按一下 [下一步] 。
6.按一下 [完成] 。



兩台都裝好DTC後，測了一下還是沒過，繼續找。先來設定一下DTC。





1.[系統管理工具]->[元件服務]

2.[元件服務]->[電腦]->[我的電腦]->[內容]


3.[內容]->[MSDTC]->[安全性設定]


4.基本上這樣設應該就會過了。



(打完才發現微軟文章有寫，直接抄過來)
如果您執行 Windows Server 2003 Service Pack 1 (SP1)，您必須執行這些額外的步驟：

1.按一下 [ 開始 ，按一下 [執行] ，輸入 comexp.msc ，然後再按一下 [若要開啟 [元件服務的 [確定] 。展開 元件服務 ，展開 [ 電腦] ，以滑鼠右鍵按一下 [我的電腦 ，然後再按一下 [ 內容] 。

2.在 [ MSDTC ] 索引標籤上, 按一下 [ 交易設定 ] 下的 [ Secuity 的組態 ]按一下以選取 [ Secuity 設定 的] 下的 [ 網路 DTC 存取 ] 核取方塊並按一下以選取下列核取方塊，[ 交易管理員通訊 ] 下]：
允許輸入、允許輸出
Microsoft Cluster Server (MSCS) 的叢集上您無法選取 所需的相互驗證 。 因此，按一下以選取下列核取方塊的其中一個中：
需要傳入呼叫者驗證、不需要驗證

3.請確定 登入帳戶 NTAUTHORITY\NetworkService 到設定的。

4.按一下 [確定] 。 訊息方塊會說明 MS DTC 服務將會停止並重新啟動，以及所有依存的服務將也會停止，重新啟動。

5.請按一下 [是] 。




恩，兩台的DTC設完後，跑測試的SQLString還是不過zzzzz,於是又找了這篇分散式交易協調器 (DTC)來試。
1.[開始]->[執行]->打regedit.exe->[確認]
2.在\HKEY_LOCAL_MACHINE\Software\Microsoft\MSDTC裡找到TurnOffRpcSecurity這項。
3.如果是0的話，改成1。



兩台都設好了，我又測了一下，還是不能過啊啊啊啊啊啊啊.....





網路上找的到的招大多用過了，只好一步一步慢慢找，這個鬼MSDTC問題從頭到尾花了超過我十小時，最後我發現，在呼叫端的那台，[元件服務]->[電腦]->[我的電腦]->[內容]裡面，[使用本機協調器]不能打勾，要填上你要呼叫的主機名稱，設定完後，一帆風順，一切正常，只是我的進度嚴重的落後，要被廠商跟老闆砍劈了啊 -_-