[SQL]AlwaysON SQL 登入帳號設定
在寫前一篇 AlwaysON 的容錯移轉之後，雖然可以正常的在兩個 Node 之間順利進行移轉，但是當我建立一個 SQL 登入帳號要配合所開發的系統去測試的時候，卻又引發另外的問題了。為了解決這個問題，透過一些網路上高手的說明，讓我更了解 SQL Server 帳號管理的一些細節，為了避免以後癡呆忘記，因此把相關細節做個整理，也順便謝謝那些高手所提供的寶貴資訊。
1. 首先我先用 SQL 指令建立一個登入帳號，可以用來登入所測試的 TEST 資料庫
1: USE [master]
2: GO
3: CREATE LOGIN [User1] WITH PASSWORD=N'123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
4: GO
5: USE [TEST]
6: GO
7: CREATE USER [User1] FOR LOGIN [User1]
8: ALTER ROLE [db_owner] ADD MEMBER [User1]
9: GO
2. 寫個一個測試的 VB Script , 用來連接 AlwaysON 的 Listen , 這裡都還沒有問題
1: Dim Cnxn
2: Dim Rs
3:
4: Set Cnxn = CreateObject("ADODB.Connection")
5: Set Rs = CreateObject("ADODB.Recordset")
6: Cnxn.ConnectionString = "Provider=SQLOLEDB.1;Password=123;Persist Security Info=True;User ID=User1;Initial Catalog=TEST;Data Source=10.40.40.213"
7:
8: Cnxn.Open
9: Rs.Open "select * from dbo.T01", Cnxn , 1 , 1
10:
11: Msgbox Rs.RecordCount
12:
13: Rs.Close
14: Cnxn.Close
4. 此時如果我到 AlwaysON_2 的環境上，想要透過原本在第 1 個步驟上面去建立照號的話，則會出現以下的錯誤訊息
訊息 15023，層級 16，狀態 1，行 1
使用者、群組或角色 'User1' 在目前的資料庫中已經存在。
5. 要解決這樣的帳號的問題，基本上會有三個方式：
- 使用 SQL Server 2012 的 Contained Databases 的方式
- 使用 Windows 驗證登入帳戶
- 建立具有相同 SID 和密碼的 SQL 驗證登入帳戶
因為目前我主要是配合所開發的系統使用 AlwaysON 的技術，而且會有跨資料庫存取的需求，因此就無法使用 Contained Databases 或者是 Windows 驗證的方式，透過一些網路上的高手的指點，微軟有一篇 kb 918992 的文件，就有類似的處理方式。
6. 如果覺得 KB 918992 上的 Script 過於複雜，我個人有個比較簡單的方法，或許可以更方便了解，如果不熟悉 SUSER_ID 和 LOGINPROPERTY 的用法，可以另外參考 MSDN 上面的介紹 ( SUSER_ID , LOGINPROPERTY )
1: select
2: SUSER_SID( 'User1' ) as SID,
3: LOGINPROPERTY('User1', 'PasswordHash' ) as Password
7. 接下來利用 Copy/Paste 將 Query 所取得的 SID 和 Password 的值複製出來，組成以下的 SQL 指令，將這個指令複製到 AlwaysON_2 的環境下執行
1: CREATE LOGIN [User1] WITH
2: PASSWORD = 0x0200D0FF44D2246537593CC9164F133CCAB1076CAD00AF7BB3DFB2153692FE34BCAB27D0A12EC287579B180E1DC8FC5D63D5B71D3E0884CD478CB2749E4C0B64970792F9905C HASHED,
3: SID = 0x37615452F0A999438C1B6AA0ED744A72,
4: DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
透過上述的方式，就可以將帳號在 AlwaysON 的環境上同步。如果對於 SQL Server 帳號管理有興趣的朋友，個人建議可以參考 SQL Server 2012 管理實戰 上面的第七章安全性管理，裡面有很詳細的說明。