2023年11月9日 星期四

使用TCP/IP連線SQL Server Express的方式

(1)啟用TCP：

     SQL Server Express預設沒有啟用TCP

     1. 開啟SQL Server Configuration Manager  > SQL Server 網路組態 > SQLEXPRES的通訊協定 > TCP/IP > 內容 >  IP位址 > 找到 IPAll > TCP通訊埠 (例如輸入1433)

     2. 重啟 SQL Server Express 服務


以下為截取自網路的SQL Express相關設定可做為參考

Step 1: Enable TCP/IP connections

use TCP/IP to connect to the SQL Server database, but SQL Server Express does not enable TCP support by default. To enable TCP/IP:

  1. In SQL Server Configuration Manager, expland the SQL Server Network Configuration > Protocols for SQLEXPRESS node.

  2. Right-click the TCP/IP item on the right; then select Properties.

  3. On the General tab, change Enabled to Yes.

  4. On the IP Addresses tab, under the IPAll node, clear the TCP Dynamic Ports box.

  5. In TCP Port, enter the port to listen on . For example, 1450. Remember this port, because it needs to be used in the connection string.

  6. Click OK.

  7. Restart the Microsoft SQL Server Express service using either the standard service control panel or the SQL Express tools.

Step 2: Enable SQL Server authentication

When require SQL Server authentication to be enabled on the instance of SQL Express. To do this:

  1. In SQL Server Management Studio Express tool, right-click the instance of SQL Express to configure; then select Properties.

  2. Select the Security section on the left.

  3. Change the Server Authentication to SQL Server and Windows Authentication mode.

  4. Restart the Microsoft SQL Server Express service using either the standard service control panel or the SQL Express tools.

Step 3: Create a database user

When require a user to connect to the database. To create this user:

  1. In SQL Server Management Studio Express tool, right-click the Security > Logins node; then select New Login.

  2. Enter the username (e.g.  web ).

  3. Change the Server Authentication to SQL Server and Windows Authentication mode.

  4. Enter the user’s password.

  5. Disable password expiration.

  6. Click OK.

  7. After creating database, assign this user db_owner permissions on the database, so that it can create the required database tables.

Step 4: Set statistics to auto update

  1. In SQL Server Management Studio Express, right-click the database; then select Properties.

    The Database Properties dialog is displayed.

  2. Scroll to the top of the Other options list.

  3. In Auto Update Statistics, select True.

  4. Click OK.

於