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

昨天又有同事在做資料庫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欄位應該是要一樣的,如下圖所示:


到這裡各位應該就可以理解為何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

發生例外時該用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來抛出原例外,以免看不到真正發生的原因. 

有關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這個帳號,如下圖:

打開IIS管理員,右鍵點選Default Web Site->管理網站->進階設定,可以看到我們要瀏灠的網站應用程式集區是DefaultAppPool,如下圖:

然後再展開應用程式集區,從這裏我們可以看到每一個應用程式集區的識別(Identity),如下圖:

到這裡我們可以了解,我們的Default Web Site是跑在DefaultAppPool這個應用程式集區之中,而DefaultAppPool這個集區的識別則是用了ApplicationPoolIdentity這個帳號,我們可以在DefaultAppPool應用程式集上按右鍵->進階設定,找到識別這個屬性,點選...後,可以讓我們去設定這個集區的執行身份,如果你不想用ApplicationPoolIdentity這個識別的話,在這裏也可以改回用NetworkService,這樣就跟IIS6一樣了,如下圖所示:

那為何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是你要設定的應用程式集區的名稱,輸入完後按檢查名稱,出現底線後再按確定,然後你就可以去設定修改或刪除等權限了.