WSUS 3 SP2 move database from SQL 2005 to 2008
- Hi,
I currently have my WSUS 3 SP2 installation on a SBS2003 Premium box using SQL 2005 as the database. I now want to migrate just the database to a new SQL Server 2008 (x64) box.
I tried stopping the services, detaching the database, copying it over and attaching it to the new SQL server (adding the NT AUTHORITY\NETWORK SERVICE login) and changing two registry entries on the existing WSUS box (SqlServerName and SqlInstanceIsRemote) but I now can't connect the MMC to the server.
It says the login for DOMAIN\SERVERNAME$ fails on the SQL server. I get event ID 421 'Connection to database failed.' on the existing server and event ID 18456 'Login failed for user DOMAIN\SERVERNAME$'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 192.168.0.1]'
I have tried restarting IIS, SQL and Update Services services but to no avail. Should it be possible to migrate the database this way, or do I need to run the WSUS setup program on the new backend SQL server to configure it correctly? If so, how is this done as it is Windows Server 2008 and I believe the setup is done as a role and not using the WSUSSetup.exe with the /b switch.
Any advice would be much appreciated.
TIA
Chris
Answers
Thanks for the reply.
I have added the machine account for my front-end server to SQL 2008, but when I try to open the WSUS MMC, it can't connect to the database with error:
Cannot connect to 'SERVER'. SQL may not be running on the server.
Any ideas why this doesn't work?
Was WSUS originally installed to SQL Server 2005 on this machine, or was it migrated from the Windows Internal Database?
If migrated, can you confirm that the wYukonInstalled value is set to -zero-.
Also, in addition to mapping the DOMAIN\MACHINENAME$ account to the SUSDB (which includes having to create the user account in the SUSDB), the Database User account should be assigned the Database Role membership of webService and the default 'dbo' schema. The SQL Login account should be assigned Default database = master and the Server Role public. Confirm that the SQL Login is mapped to the Database User account on the "User Mapping" page of the SQL Login Properties dialog. On the "Status" page, Login should be Enabled and Permission to connect to databse engine should be Grant.
Lawrence Garvin, M.S., MCITP:EA, MCDBA
Principal/CTO, Onsite Technology Solutions, Houston, Texas
Microsoft MVP - Software Distribution (2005-2009)
My MVP Profile: http://mvp.support.microsoft.com/profile/Lawrence.Garvin
My Blog: http://onsitechsolutions.spaces.live.com- Marked As Answer byTippers Tuesday, October 27, 2009 9:48 PM
All Replies
- The SQL Server 2008 (x64) system needs to be a domain member, and you need to create the front-end server's *machine* account in SQL Server -- that would be the DOMAIN\SERVERNAME$ login that's failing -- and map it to the DOMAIN\SERVERNAME$ user in the SUSDB database.
(The NT AUTHORITY\Network Service login is not required, and you probably should remove that from your SQL Logins. On a *LOCAL* database service this account is used because it can be used, but on a remote database server, you have to use the domain machine account for authentication.)
Lawrence Garvin, M.S., MCITP:EA, MCDBA
Principal/CTO, Onsite Technology Solutions, Houston, Texas
Microsoft MVP - Software Distribution (2005-2009)
My MVP Profile: http://mvp.support.microsoft.com/profile/Lawrence.Garvin
My Blog: http://onsitechsolutions.spaces.live.com - Thanks for the reply.
I have added the machine account for my front-end server to SQL 2008, but when I try to open the WSUS MMC, it can't connect to the database with error:
Cannot connect to 'SERVER'. SQL may not be running on the server.
Please verify that SQL is running and configured correctly on the server. Contact your network administrator if the problem persists.
In the Event Viewer Application log on the front-end server, I get event id 7032:
The WSUS administration console was unable to connect to the WSUS Server via the remote API.
Verify that the Update Services service, IIS and SQL are running on the server. If the problem persists, try restarting IIS, SQL, and the Update Services Service.
The WSUS administration console has encountered an unexpected error. This may be a transient error; try restarting the administration console. If this error persists,
Try removing the persisted preferences for the console by deleting the wsus file under %appdata%\Microsoft\MMC\.
System.IO.IOException -- The handshake failed due to an unexpected packet format.
Source
System
Stack Trace:
at System.Net.Security.SslState.StartReadFrame(Byte[] buffer, Int32 readBytes, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.StartSendBlob(Byte[] incoming, Int32 count, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.ForceAuthentication(Boolean receiveFirst, Byte[] buffer, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.ProcessAuthentication(LazyAsyncResult lazyResult)
at System.Net.TlsStream.CallProcessAuthentication(Object state)
at System.Threading.ExecutionContext.runTryCode(Object userData)
at System.Runtime.CompilerServices.RuntimeHelpers.ExecuteCodeWithGuaranteedCleanup(TryCode code, CleanupCode backoutCode, Object userData)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Net.TlsStream.ProcessAuthentication(LazyAsyncResult result)
at System.Net.TlsStream.Write(Byte[] buffer, Int32 offset, Int32 size)
at System.Net.PooledStream.Write(Byte[] buffer, Int32 offset, Int32 size)
at System.Net.ConnectStream.WriteHeaders(Boolean async)
** this exception was nested inside of the following exception **
System.Net.WebException -- The underlying connection was closed: An unexpected error occurred on a send.
Source
Microsoft.UpdateServices.Administration
Stack Trace:
at Microsoft.UpdateServices.Administration.AdminProxy.CreateUpdateServer(Object[] args)
at Microsoft.UpdateServices.Administration.AdminProxy.GetUpdateServer(String serverName, Boolean useSecureConnection, Int32 portNumber)
at Microsoft.UpdateServices.UI.AdminApiAccess.AdminApiTools.GetUpdateServer(String serverName, Boolean useSecureConnection, Int32 portNumber)
at Microsoft.UpdateServices.UI.SnapIn.Scope.ServerSummaryScopeNode.GetUpdateServer(PersistedServerSettings settings)
at Microsoft.UpdateServices.UI.SnapIn.Scope.ServerSummaryScopeNode.ConnectToServer()
at Microsoft.UpdateServices.UI.SnapIn.Scope.ServerSummaryScopeNode.get_ServerTools()
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Any ideas why this doesn't work? Thanks for the reply.
I have added the machine account for my front-end server to SQL 2008, but when I try to open the WSUS MMC, it can't connect to the database with error:
Cannot connect to 'SERVER'. SQL may not be running on the server.
Any ideas why this doesn't work?
Was WSUS originally installed to SQL Server 2005 on this machine, or was it migrated from the Windows Internal Database?
If migrated, can you confirm that the wYukonInstalled value is set to -zero-.
Also, in addition to mapping the DOMAIN\MACHINENAME$ account to the SUSDB (which includes having to create the user account in the SUSDB), the Database User account should be assigned the Database Role membership of webService and the default 'dbo' schema. The SQL Login account should be assigned Default database = master and the Server Role public. Confirm that the SQL Login is mapped to the Database User account on the "User Mapping" page of the SQL Login Properties dialog. On the "Status" page, Login should be Enabled and Permission to connect to databse engine should be Grant.
Lawrence Garvin, M.S., MCITP:EA, MCDBA
Principal/CTO, Onsite Technology Solutions, Houston, Texas
Microsoft MVP - Software Distribution (2005-2009)
My MVP Profile: http://mvp.support.microsoft.com/profile/Lawrence.Garvin
My Blog: http://onsitechsolutions.spaces.live.com- Marked As Answer byTippers Tuesday, October 27, 2009 9:48 PM
- Hi Lawrence,
Thanks for your help. I have just got it fixed and was about to post back that adding the Database Role membership for webService did the trick. I now have the database moved and the MMC connected.
Thanks again,
Chris
