WSUS: How to migrate your WSUS Windows Internal Database to SQL Server 2005 Express Edition
If your existing SUSDB is hosted by the Windows Internal Database version of SQL that is installed by WSUS 3.x you may have noticed that remote connections to the database don’t work. This is actually expected behavior because the Windows Internal Database is an application version of SQL Server 2005 which is a special, limited version of SQL Server 2005 Express Edition that does not support remote access.
To perform remote queries against the database, you will need to
1. Install a full version of SQL Server 2005 or install the standalone version of SQL Server 2005 Express Edition.
2. Migrate the SUSDB
3. Use SQL Server Management Studio Express to enable remote connections.
Sounds simple enough, right? Well let’s take a look at how exactly to do this. The general steps include:
1. Download and install Microsoft SQL Server 2005 Express Edition.
2. Install the SQL Server Management Studio Express tool.
3. Detach the SUSDB.
4. Attach the SUSDB to SQL Server 2005 Express
5. Configure SQL Server 2005 Express to allow remote connections.
6. Configure user/group access to the database. Now we’ll walk through each step in a little more detail.
1. Download and install Microsoft SQL Server 2005 Express Edition. You can download Microsoft SQL Server 2005 Express Edition from https://msdn2.microsoft.com/en-us/express/bb410792.aspx. During installation, when prompted for an instance name select "Default instance" then proceed through the installation wizard choosing the default settings.
2. Install the SQL Server Management Studio Express tool. This is available from https://msdn2.microsoft.com/en-us/express/bb410792.aspx.
3. Detach the SUSDB:
a. Stop the IIS Admin service and the Update Services service:
i. Click Start, point to Programs, point to Administrative Tools, and then click Services.
ii. Right-click IIS Admin Service, and then click Stop.
iii. Right-click Update Services, and then click Stop.
b. Open the Microsoft SQL Server Management Studio Express console (Start -> Programs -> Microsoft SQL Server 2005)
i. From the File menu, click "Connect Object Explorer" and use the following
settings:
· Server type: Database Engine
· Server name: .pipeMSSQL$MICROSOFT##SSEEsqlquery
· Authentication: Windows Authentication
ii. Click the "Connect" button
iii. Expand Databases under .pipeMSSQL$MICROSOFT##SSEEsqlquery
iv. Right click SUSDB, choose Tasks/Detach, enable the check boxes for Drop Connections, Update Statistics, and Keep Full Text Catalogs, then click OK.
4. Attach the SUSDB to SQL Server 2005 Express:
a. Open the Microsoft SQL Server Management Studio Express console (Start -> Programs -> Microsoft SQL Server 2005).
b. From the File menu, click "Connect Object Explorer" and use the following settings:
· Server type: Database Engine
· Server name: enter the actual name of your WSUS server here
· Authentication: Windows Authentication
c. Right click the Databases object, choose Attach, click Add in the Databases to attach area, browse to the location of your SUSDB.mdf (for example, c:wsusupdateservicedbfiles), select the SUSDB.mdf, when the "SUSDB" database details pane is populate, highlight the susdb_log.ldf log file, click Remove, then click OK to add the SUSDB.mdf.
d. Verify that NT AUTHORITYNETWORK SERVICE has login permissions to the SQL Server instance and to the WSUS database. If it does not, you will need to add it to both locations. This account should also be a member of the webService role on the WSUS database.
i. Verify permissions on the SQL Server instance. In SQL Server Management Studio, open the instance and select Security, then Logins. The NT AUTHORITYNETWORK SERVICE account should be listed as a login. If it is not, it should be added.
ii. Verify permissions on the database. Right-click the database, select Properties and then click Permissions. The NT AUTHORITYNETWORK SERVICE account should be listed as a login. If it is not, it should be added.
iii. Verify members of the webService role. Under the WSUS database, select Roles, then right-click webService and select Properties. The NT AUTHORITYNETWORK SERVICE account should be listed as a member of this role. If it is not, it should be added.
e. Edit the registry to point WSUS to the SQL instance that now holds SUSDB.
i. Click Start, click Run, type regedit, and then click OK.
ii. Find the following key:
HKLMSOFTWAREMicrosoftUpdateServicesServerSetupSqlServerName
In the Value box enter your WSUS server name and click OK.
f. Open Services and then start the IIS Admin service and Update Services service.
i. Click Start, point to Programs, point to Administrative Tools, and then click Services.
ii. Right-click IIS Admin Service, and then click Start.
iii. Right-click Update Services, and then click Start.
g. Verify that the database migration has been successful by opening the WSUS administrative console (click Start, click Administrative Tools, and then click Microsoft Windows Server Update Services 3.0).
5. Configure SQL Server 2005 Express to allow remote connections:
a. Open Start/Programs/Microsoft SQL Server 2005/Configuration Tools/SQL Server Surface Area Configuration. This tool is used to configure services, network protocols and components for SQL Server.
b. On starting the tool, select the Surface Area Configuration for Services and Connections option to open the configuration dialog box.
c. Configure the Remote Connection Options. The configuration dialog box is divided into two areas. The left side of the window provides navigation and the right side allows modification of the available settings. Select Remote Connections from the navigation area. You will see that the server is configured to allow local connections only.
To enable remote connections, simply select the radio button for Local and Remote Connections. You may then select which network protocols you wish the server to accept. Once set, every database in the SQL Server Express instance is accessible
remotely via the network.
d. Restart the Database Engine service.
6. Configure user/group access to the database:
After you have remote connections configured via the previous steps, you may also need to configure the permissions further on the database for individual users who are not local administrators on the WSUS server but who need remote query access.
The best option here is to add these users to the built-in, local WSUS Reporters Group then configure a login for the database for this group using the following steps:
· Open Microsoft SQL Server Management Studio Express.
· Expand the Security folder on SQL Server, right click on the logins folder, and select new login.
· Add the WSUS Reporters group from Active Directory into the login name if it is not present.
· Select the User Mappings and check the box next to the SUSDB database.
· Select the WSUS Reporters group's properties within SQL Server and add the role of db_datareader for the login on the database.
After these steps, the members of the WSUS Reporters group should be able to perform the desired remote queries.
Hope this helps,
Mike Johnson | WSUS Support Escalation Engineer