Service Accounts and Database Mirroring

Service Accounts (Configure Database Mirroring Security Wizard)
https://msdn.microsoft.com/en-us/library/ms189434.aspx

When using Windows Authentication, if the server instances use different accounts, specify the service accounts for SQL Server. These service accounts must all be domain accounts (in the same or trusted domains).

If all the server instances use the same domain account or use certificate-based authentication, leave the fields blank. Simply click Finish, and the wizard automatically configures the accounts based on the account of the current wizard.

------------------------------------------------------------------------------------------------

1.Both of Principal and Mirroring SQL service accounts can be domain accounts (in the same or trusted domains)
2.Both of Principal and Mirroring SQL service accounts can be local accounts.
3.If principal SQL Server service account is a domain account and mirroring SQL Server service account is a local account, you may encounter this warning and error:

如果你在服務帳戶頁籤,輸入SQL Server啟動帳戶,開始設定後會出現以下警告:

 

啟動鏡像則可能會出現以下錯誤:

The server network address "TCP://myserver.domain.domain.domain.com:5022" can not be reached or does not exist.
Check the network address name and that the ports for the local and remote endpoints are operational.
(Microsoft SQL Server, Error:1418)

 

From ERRORLOG
2013-09-08 00:27:18.13 Logon       Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(登入嘗試失敗). State 67.'.  [CLIENT: fe80::3478:8ea7:fceb:bae5%11]
2013-09-08 00:27:18.14 spid32s     Error: 1474, Severity: 16, State: 1.
2013-09-08 00:27:18.14 spid32s     Database mirroring connection error 5 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(登入嘗試失敗). State 67.' for 'TCP://MirrorServer:5022'.
2013-09-08 00:27:20.54 Logon       Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(登入嘗試失敗). State 67.'.  [CLIENT: fe80::3478:8ea7:fceb:bae5%11]
2013-09-08 00:27:21.03 Logon       Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(登入嘗試失敗). State 67.'.  [CLIENT: fe80::3478:8ea7:fceb:bae5%11]
2013-09-08 00:27:22.06 Logon       Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(登入嘗試失敗). State 67.'.  [CLIENT: fe80::3478:8ea7:fceb:bae5%11]
2013-09-08 00:27:24.06 Logon       Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(登入嘗試失敗). State 67.'.  [CLIENT: fe80::3478:8ea7:fceb:bae5%11]
2013-09-08 00:27:26.59 Logon       Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(登入嘗試失敗). State 67.'.  [CLIENT: fe80::3478:8ea7:fceb:bae5%11]
2013-09-08 00:27:29.09 Logon       Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(登入嘗試失敗). State 67.'.  [CLIENT: fe80::3478:8ea7:fceb:bae5%11]
2013-09-08 00:27:31.62 Logon       Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(登入嘗試失敗). State 67.'.  [CLIENT: fe80::3478:8ea7:fceb:bae5%11]
2013-09-08 00:27:34.11 Logon       Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(登入嘗試失敗). State 67.'.  [CLIENT: fe80::3478:8ea7:fceb:bae5%11]
2013-09-08 00:27:36.63 Logon       Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(登入嘗試失敗). State 67.'.  [CLIENT: fe80::3478:8ea7:fceb:bae5%11]
2013-09-08 00:27:38.02 spid30s     錯誤: 1443,嚴重性: 16,狀態: 2。
2013-09-08 00:27:38.02 spid30s     Database mirroring has been terminated for database 'MyDB'. This is an informational message only. No user action is required.

即使此時ping對方與telnet對方都沒有問題,還是可能會出現以上錯誤。

[RESOLUTION]
configure Database Mirroring with certificate or change both service account to local account.

Setting Up SQL Server 2008 R2 Database Mirroring with Certificate step by step in a Workgroup

Setting Up SQL Server 2008 R2 Database Mirroring in a Workgroup