Setup remote connection in SQL Server 2005

Overview:

SQL Server 2005 default 是不可以做 remote connection 的 (有點難以理解...), 而要設定SQL Server 為可以remote connection 也不是這麼容易的事. 經過一返Google以及試驗後, 終於成功以 SQL Server Authentication login 入 remote 既 DataBase.

Reference:

Enable remote connection to SQL Server 2005 Express

這個link講的是 2005 Express, 不過其實都是大同小異

Steps:

1. Enable SQL Server Authentication, 開一個 SQL Server Authentication Login Account (請參照上一個post)
2. 去 "All Programs" => "SQL Server 2005" => "Configuration Tools" => "SQL Server Surface Area Configuration"
3. 揀 "Surface Area Configuration for Services and Connections"
4. Expand "Server name" => "Database Engine" => "Remote Connections"
5. 揀 "Local and remote connections", 揀埋 "Using both TCP/IP and named pipes"
6. 0向左面 "SQL Server Browser", Expand 開揀 "Services", "Start type" 揀 Automatic, apply then start service.
7. 其實以上步驟已經configure好remote connection, 不過有可能SQL Server 無 enable到 named pipes 同 TCP/IP. 咁就要跟以下步驟黎enable 返
8. 去 "All Programs" => "SQL Server 2005" => "Configuration Tools" => "SQL Server Configuration Manager"
9. Expand "SQL Server 2005 Network Configuration", 揀 "Protocols for xxxxx", xxxxx 係你 Server Instance 個名
10. 0向右邊enable 返Named Pipes 同埋 TCP/IP, 跟住 restart service