Appendix B: Configure Remote SQL

Applies To: Windows Server 2003, Windows Server 2003 R2, Windows Server 2003 with SP1, Windows Server 2003 with SP2, Windows Server 2008, Windows Server Update Services

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 SQL Server 2005 on the back-end server.

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

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

Remote SQL limitations

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

• You must not be running Terminal Server on the computer that will be the front end server of a remote SQL installation.

• You must use at least Microsoft SQL Server 2005 Service Pack 1 (available on the Microsoft Download Center (https://go.microsoft.com/fwlink/?LinkId=66143) for database software on the back-end computer if that computer is running Windows Server 2003, and SQL Server 2005 Service Pack 2 if it is running Windows Server® 2008.

• Both the front-end and the back-end computers must be joined to an Active Directory domain, otherwise, if they are in different domains, you must establish cross-domain trust between the domains before running WSUS setup.

• If you already have WSUS 2.0 installed in a remote SQL configuration and wish to upgrade to WSUS 3.0, you should 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. Then you should install SQL Server 2005 Service Pack 1 and upgrade the existing database. Finally, you should install WSUS 3.0 on the front-end computer.

Database requirements

WSUS 3.0 requires SQL Server 2005 with Service Pack 1 on Windows Serve 2003 and SQL Server 2005 Service Pack 2 on Windows Server 2008. 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 earlier in this guide.

Step 1: Install SQL Server 2005 Service Pack 1 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 2005, 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

You should make sure that the person who is going to install WSUS 3.0 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 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. Check that the person who is going to install WSUS 3.0 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 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 Server Setup.