2011年7月31日 星期日

資料庫附加或還原後無法登入問題

昨天又有同事在做資料庫mdf附加至至SQL Server後,原來的使用者帳號會無法登入的情況,我想應該很多人都碰過這個問題(不管是用還原或附加的方式都一樣),於是就把發生的原因及解決方法整理一下給大家參考.

先簡單描述一下問題發生的原因,SQL Server的登入可分成login account及database user account, login account是代表這台SQL Server可以合法登入的使用者,但是每一個login account可以使用那些DB呢?這就要靠database user account的設定了,通常,我們在建完login account後,會在各資料庫中去建立user account,比如:

--建立SQL login account
CREATE LOGIN user1 WITH PASSWORD = '1qaz!QAZ'
GO

--在TestDB中為user1 login account建立database user account
Use TestDB
GO
CREATE USER user1 FOR LOGIN user1

這樣的意思就是在TestDB中建立一個user1的database user account, 而這個user account則是與user1這個login account發生關聯(這2個名稱實際上可以不同,不過為了方便管理,通常都設相同),而接下來就可以針對user1這個database user account去設定在TestDB中的權限,比如

EXEC sp_addrolemember 'db_datareader', 'user1'

意思就是把user1這個database user account加入db_datareader這個角色.

了解了login account與database user account的關係後,再來看為何資料庫attach或restore後,用user1帳號登入卻無法使用資料庫的問題,答案就是關聯, 先來看看login account存在master DB中的記錄:

SELECT * FROM master.dbo.syslogins where name='user1'

再來看user1在TestDB database中的記錄:

SELECT * FROM [TestDB].sys.sysusers where name='user1'

各位可以發現這2個系統資料表中都有一個SID的欄位,剛才講的關聯就是SID這個欄位,這2個table中的SID欄位應該是要一樣的,如下圖所示:
image

到這裡各位應該就可以理解為何attach或restore後會同樣的login account會無法使用資料庫的原因了,那是因為login account是我們在新SQL SERVER中建立的,而database user account是存在我們的DB中,當我們還原或附加DB時,這個SID當然不會與我們在新的SQL Server中所建的SID相同,所以當你在新的SQL SERVER用user1這個login account登入,進到TestDB時卻因為查不到這筆SID的記錄就被拒絕存取了.

那麼該如何解決呢?可能有人會想下Update直接把[TestDB].sys.sysusers user1的SID直接改成跟master.dbo.syslogins user1的SID一樣就行了,想法是對的,不過SQL2005之後為了怕有人亂搞SQL 的system table,所以預設把system table的直接Insert ,update ,delete權限都鎖掉了,所以不能這樣做.
當然你也可以把原資料庫的user1 Drop掉重新再下一次CREATE USER user1 FOR LOGIN user1指令,這樣就會重新對應了,不過通常這招也不一定會成功,因為要drop user得先把相依於user1的物件先砍掉才行,而物件本身如果又相依別的物件的話,就會發現要砍好幾個東西才能把database user砍掉,再加上建回去後所有的權限也要重設,所以也不是一個好解法.

最好的作法是,直接用ALTER USER將user1 user account與login account重新對應就好了,如下:
Use TestDB
GO
ALTER USER user1 WITH LOGIN = user1

下完後各位再去查一下master.dbo.syslogins 與[TestDB].sys.sysusers中的SID,就會發現已經一致了,如此user1就又可以登入存取TestDB了.

備註:也可以用sp_change_users_login 重新對應login account與database user account,不過SQL Server 2008以後建議以ALTER USER WITH LOGIN語法進行處理, sp_change_users_login只是為了保留舊版相容性所以現在還可以使用,未來版本可能就不能用了,請參考MSDN說明.

http://msdn.microsoft.com/zh-tw/library/ms174378.aspx

2011年7月27日 星期三

發生例外時該用Throw ex or Throw?

當我們在try block發生例外時,如果需要在catch block處理完例外後,要將原來的例外抛回呼叫端,大家是會用throw或是throw ex呢?雖然這2種語法皆可以把例外再抛回呼叫端,如果你在呼叫端攔到Exception時show ex.Message則都可以看到同樣的例外訊息,不過這2個語法還是有一點不一樣,以下列程式片段為例:
   1: using System;
   2:  
   3: namespace ConsoleApplication1
   4: {
   5:     class Program
   6:     {
   7:         static void Main()
   8:         {
   9:             try
  10:             {
  11:                 TestEx1();
  12:             }
  13:             catch (Exception ex)
  14:             {
  15:                 Console.WriteLine("TestEx1:" + ex.ToString());
  16:             }
  17:  
  18:             Console.Read();
  19:         }
  20:  
  21:         static void TestEx1()
  22:         {
  23:  
  24:             try
  25:             {
  26:                 int a = int.Parse("");
  27:             }
  28:             catch (Exception ex)
  29:             {
  30:  
  31:                 throw ;
  32:             }
  33:         }
  34:  
  35:     }
  36: }
如果你用throw ex,則在Main的ex.ToString中,所得到的內容如下:
TestEx1:System.FormatException: 輸入字串格式不正確。
於 ConsoleApplication1.Program.TestEx1() 於 C:\Users\Administrator\Desktop\ConsoleApplication3\ConsoleApplication3\Program.cs: 行 31
於 ConsoleApplication1.Program.Main(String[] args) 於 C:\Users\Administrator\Desktop\ConsoleApplication3\ConsoleApplication3\Program.cs: 行 11
堆疊最上層的行31是指throw ex那一行,而不是真正發生例外的int.Parse那一行;如果將程式的第31行throw ex改為throw,則ex.ToString則得到的例外內容如下:
TestEx1:System.FormatException: 輸入字串格式不正確。
於 System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
於 System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)
於 System.Int32.Parse(String s)   於 ConsoleApplication1.Program.TestEx1() 
於 C:\Users\Administrator\Desktop\ConsoleApplication3\ConsoleApplication3\Program.cs: 行 31
於 ConsoleApplication1.Program.Main(String[] args) 於 C:\Users\Administrator\Desktop\ConsoleApplication3\ConsoleApplication3\Program.cs: 行 11
有沒有發現例外發生的真正原因是從Int32.Parse一直到最底層StringToNumber(難怪int.Parse參數一定要傳string,原來是底層會先做字串轉數字動作),都有show出來.也就是當你用throw ex抛出例外時,.Net會清除原始的Stack再抛出例外,所以呼叫端看到的Stack是從throw ex那一行開始算,使用throw才能保留原始Stack的內容.
所以囉,最好還是使用throw而不要使用throw ex來抛出原例外,以免看不到真正發生的原因. 
 

2011年7月24日 星期日

有關IIS7.5 Application Pool Identity的設定

今天有同事在把ASP.NET應用程式從Windows 2003 Server移機到Windows Server 2008 R2,發現原來的檔案上傳功能都不能用了,從錯誤訊息看起來是權限問題,心裏大概有個底了,應該是IIS的Worker Process的帳號沒有存取該資料夾的權限,所以看了一下他們IIS的應用程式集區的設定,果然沒錯,用到了IIS7.5的AppliationPoolIdentity的帳號,大家應該都對IIS6時代的Network Service帳號不陌生,不過到了IIS7.5預設Worker Process的執行身份改為ApplicationPoolIdentity這個帳號,所以我們的ASP.NET應用程式如果有需要去存取後端資源時,就必須針對ApplicationPoolIdentity這個帳號去設定.
在Windows 2008 SP2之後及Windows 7的IIS 7.5中,當我們去執行預設的Web站台的首頁時,打開工作管理員,可以看到IIS的Worker Process(w3wp.exe)的執行身份,變成了DefaultAppPool這個帳號,如下圖:
image
打開IIS管理員,右鍵點選Default Web Site->管理網站->進階設定,可以看到我們要瀏灠的網站應用程式集區是DefaultAppPool,如下圖:
image
然後再展開應用程式集區,從這裏我們可以看到每一個應用程式集區的識別(Identity),如下圖:
image
到這裡我們可以了解,我們的Default Web Site是跑在DefaultAppPool這個應用程式集區之中,而DefaultAppPool這個集區的識別則是用了ApplicationPoolIdentity這個帳號,我們可以在DefaultAppPool應用程式集上按右鍵->進階設定,找到識別這個屬性,點選...後,可以讓我們去設定這個集區的執行身份,如果你不想用ApplicationPoolIdentity這個識別的話,在這裏也可以改回用NetworkService,這樣就跟IIS6一樣了,如下圖所示:
image
那為何IIS7.5要用ApplicationPoolIdentity來取代原來的NetworkService做為應用程式集區的執行身份呢?那是因為Windows很多服務也會用NetworkService這個帳號做為執行識別,如果我們因為這個應用程式集區需要去取存某個後端資源,比如採用Windows驗證的SQL Server,就把NetworkService加進了SQL 的Login Account中,那不就代表其他也用NetworkService的Windows服務也能進到我們的SQL Server?所以,為了讓系統更安全及兼顧設定上的方便性,Windows就新增了一個ApplicationPoolIdentity的識別專門用來作為IIS應用程式集區執行身份,需注意的是,即使我們為每個集區都設定ApplicationPoolIdentity作為識別,但Windows的IIS管理服務會為不同集區的建立不同的虛擬帳號(Virtual Account)來跟它對應,Virtual Account是Windows 2008 R2及Windows 7新增的一個專門用來做為服務識別的新帳號,詳細說明可以參考http://technet.microsoft.com/en-us/library/dd548356(WS.10).aspx.
回到一開始的問題,當採用ApplicationPoolIdentity的應用程式集區如果要存取後端資源,該如何去設定它的權限好讓我們的ASP.NET應用程式可以上傳檔案到某特定的資料夾呢?步驟如下:
1.開啟檔案總管,右鍵點選我們要設定權限的資料夾->內容,切換至安全性頁籤
2.按編輯,權限設定視窗出現後,接著按新增,在輸入物件名稱的文字方塊中,輸入IIS AppPool\DefaultAppPool,請注意DefaultAppPool是你要設定的應用程式集區的名稱,輸入完後按檢查名稱,出現底線後再按確定,然後你就可以去設定修改或刪除等權限了.
image

2010年3月9日 星期二

MSMQ接收遠端Private Queue的安全性問題

當我們使用FormatName:DIRECT=TCP:10.0.0.1\private$\queue1去存取遠端的Private Queue時,傳送是沒有問題,但是接收時會出現"存取訊息佇列系統"時遭到拒絕的錯誤,猜想可能是接收的權限不夠,所以試著把Queue的安全性中Anonymous Logon及Everyone都設為完整控制權,再試還是不行,上MSDN找了一下,找到有關MSMQ安全性的一篇文章(http://technet.microsoft.com/en-us/library/cc759412(WS.10).aspx),其中提到MSMQ 3.0 default會要求client與server之間建立加密通道,而加密通道只有在trust domain環境下才建的起來,所以解決方式是disable MSMQ要求建立加密通道來傳輸訊息的設定值,方法是在HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSMQ\Parameters\Security下新增一個名稱為NewRemoteReadServerAllowNoneSecurityClient的DWORD值,將值設成1,然後把Queue的Anonymous Logon的接收及查看訊息打勾,再重啟MSMQ服務,就可以正常讀取到遠端的Private Queue了.

2009年12月1日 星期二

如何移除XML序列化時的Namespace宣告

今天在寫Web Service時,對方要求Web Method回傳的結果是用XML字串,但我又不想用組字串的方式來組出回應的XML,雖然可以用XML DOM的方法在記憶體產生XML DOM後再吐出XML字串,但這個方法又太麻煩了些,所以就想到用XML序列化這個好物,於是寫了一個小Function來做XML序列化的動作.

2009年11月18日 星期三

SQL Server Management Studio 2008修改資料表無法儲存?

由於最近很多同事因為客戶升級到SQL Server 2008的關係,開始安裝使用SQL Server 2008 Management Studio,不約而同的跑來問我同樣的問題,為何之前SQL Server 2005的Management Studio可以修改資料表設計,到了2008反而不行?

2009年11月10日 星期二

VB.Net 區塊變數的陷阱

今天在幫同事Debug時,發現了一個之前自己也沒注意到的觀念上的錯誤