Appendix B: Configure Remote SQL

WSUS offers limited support for running database software on a computer that is separate from the computer where the rest of WSUS is installed. This section offers step-by-step instructions for how to install WSUS in this configuration.

Setting up WSUS for remote SQL is a three-step process:

1. Install and configure Microsoft SQL Server 2008 Standard or Enterprise Edition or Microsoft SQL Server 2005 SP3 or later versions on the back-end server.

2. Confirm that the administrator who is going to install WSUS 3.0 SP2 also has permissions on SQL Server.

3. Install WSUS 3.0 SP2 on the front-end computer, and configure it to use the database on the back-end computer.

Remote SQL Limitations and Requirements

WSUS 3.0 SP2 supports running a compatible version of SQL Server software on a computer that is separate from the computer on which the WSUS 3.0 SP2 application is running. The following requirements apply to a remote SQL installation.

• You cannot use a server configured as a domain controller for the back end of the remote SQL pair.

• You cannot run Terminal Services on the computer that will be the front-end server of a remote SQL installation.

• Both the front-end computer and the back-end computer must be joined to an Active Directory domain. If the front end and back end computers are in different domains, establish a cross-domain trust between the domains before running WSUS Setup.

• The front-end computer, the back-end computer, and the downstream server(s) must have identical system times. If the system clocks are not synchronized between all of the computers, rollups will fail and no computer status information will be relayed to the upstream server.

• If you already have WSUS 2.0 installed in a remote SQL configuration and want to upgrade to WSUS 3.0 SP2, do the following before you install WSUS on the front end computer:

  1. Uninstall WSUS 2.0 (using Add or Remove Programs in Control Panel) on the back-end computer while ensuring that the existing database remains intact.

  2. Install SQL Server 2008 Standard or Enterprise Edition or SQL Server 2005 SP3 or later and upgrade the existing database.

Database requirements

WSUS 3.0 SP2 requires SQL Server 2008 Standard or Enterprise Edition or SQL Server 2005 SP3 or later. If you use the full version of SQL Server, the SQL Server administrator should first verify that the nested triggers option on SQL Server is turned on. Do this before setting up the WSUS database.

You cannot use SQL authentication. WSUS supports only Windows authentication. WSUS Setup creates a database named SUSDB. For more information about what is stored in the WSUS database or how it functions, see Choose the Database Used for WSUS 3.0 SP2 earlier in this guide.

Step 1: Install SQL Server 2005 Service Pack 3 or later or SQL Server 2008 Standard or Enterprise Edition on the back-end computer

Install a SQL Server 2005 database on the back-end computer and enable remote connections. You may use a named instance or the default instance for the WSUS database.

Set Up Remote SQL Connections

1. Click Start, point at All Programs, point at SQL Server, point at Configuration Tools, and select SQL Server Surface Area Configuration.

2. Choose Surface Configuration for Services and Connections.

Enable Remote SQL Connections

1. In the left window, click the Remote Connections node.

2. Select Local and remote connections and then select Using TCP/IP only.

3. Click OK to save the settings.

If you plan to run the SQL Server service remotely under a domain account, you will need to register a service principal name (SPN) for this server. For more information about adding an SPN, please see How to make sure that you are using Kerberos authentication when you create a remote connection to an instance of SQL Server 2005 (https://go.microsoft.com/fwlink/?LinkId=85942).

Step 2: Check administrative permissions on SQL Server

Confirm that the person who is going to install WSUS 3.0 SP2 on the front-end computer has administrative permissions on SQL Server.

To ensure administrative permissions on SQL Server

1. Start SQL Server Management Studio (click Start, click Run, and then type sqlwb).

2. Connect to the SQL Engine on the server where SQL Server 2005 or SQL Server 2008 was installed in Step 1.

3. Select the Security node and then select Logins.

4. The right pane will show a list of the accounts that have database access. Confirm that the person who is going to install WSUS 3.0 SP2 on the front-end computer has an account in this list.

5. If the account does not exist, then right-click the Logins node, select New Login, and add the account.

6. Set up this account for the roles needed to set up the WSUS 3.0 SP2 database. The roles are either dbcreator plus diskadmin, or sysadmin. Accounts belonging to the local Administrators group have the sysadmin role by default.

Step 3: Install WSUS on the front-end computer

Now install WSUS on the front-end computer. This server will need access to the Internet or to another WSUS server to obtain updates. You need to prepare this computer with all the prerequisites for a normal WSUS installation, except for database software.

Run WSUS Setup from the command line, using the **SQLINSTANCE_NAME=**servername\instancename command-line option, where servername is the name of the remote computer, and instancename is the name of the SQL Server instance that you will use for WSUS. This option installs WSUS as the front end of a remote SQL pair and installs the database setup portion of the WSUS setup process on the remote machine.

To install WSUS on the front-end computer

1. At the command prompt, navigate to the folder containing the WSUS Setup program, and type:

   WSUSSetup.exe SQLINSTANCE_NAME= servername\instancename

2. You will see the Welcome page of the installation wizard. Continue installing WSUS as in the procedure given in Run WSUS 3.0 SP2 Server Setup.