Migrating the WSUS database from a Windows Internal Database instance to a SQL Server 2005 instance on a remote server

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MartinH - 23 Jul 2007 22:10 GMT

Hey,

I am planning to migrate the WSUS database from a Windows Internal
Database instance to a SQL Server 2005 instance on a remote server.

http://technet2.microsoft.com/windowsserver/en/library/b4852133-5ed3-48d7-8a95-e
7866e638c181033.mspx?mfr=true

The FE machine account should be listed as a login on the sql server
running on the BE machine. If it is not, it should be added. How do i
add a machine account to SQL server?

I suppose i would have to add DOMAIN\SERVERNAME to the securtity
logins but it wont let me.

Help is appreciated.

TIA Martin

Reply to this Message

Sue Hoegemeier - 24 Jul 2007 02:07 GMT

If you need to add a machine account, try adding the login
with SQL instead of the GUI:
CREATE LOGIN [YourDomain\ServerName$]
FROM WINDOWS
Keep in mind though that anything running under Network
Service on that other box has been granted a login so you
open up a security hole when doing this.

-Sue

>Hey,
>
[quoted text clipped - 13 lines]
>
>TIA Martin

Reply to this Message

MartinH - 25 Jul 2007 17:42 GMT

hi sue,

thx for your response. i tried adding the login with SQL.
it returns an error.

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '\'.

>If you need to add a machine account, try adding the login
>with SQL instead of the GUI:
[quoted text clipped - 23 lines]
>>
>>TIA Martin

Reply to this Message

Erland Sommarskog - 25 Jul 2007 23:35 GMT

> thx for your response. i tried adding the login with SQL.
> it returns an error.
>
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near '\'.

Could you please include the exact command that gave you the error?

If I am to guess, my guess is that failed to include the brackets,
and typed

CREATE LOGIN domain\server FROM WINDOWS

rather than

CREATE LOGIN [domain\server] FROM WINDOWS

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Reply to this Message

MartinH - 26 Jul 2007 06:53 GMT

Oops my bad indeed no brackets. now i do and i get this message. both
sql as the comuter i want added are in the same domain and i can ping
the computer from the sql computer.

Msg 15401, Level 16, State 1, Line 1
Windows NT user or group 'domain\server' not found. Check the name
again.

>> thx for your response. i tried adding the login with SQL.
>> it returns an error.
[quoted text clipped - 12 lines]
>
> CREATE LOGIN [domain\server] FROM WINDOWS

Reply to this Message

Erland Sommarskog - 27 Jul 2007 10:53 GMT

> Oops my bad indeed no brackets. now i do and i get this message. both
> sql as the comuter i want added are in the same domain and i can ping
[quoted text clipped - 3 lines]
> Windows NT user or group 'domain\server' not found. Check the name
> again.

Is there actually a user with this name? Sue's original post had a $ at
the end:

CREATE LOGIN [YourDomain\ServerName$] FROM WINDOWS

Signature

Reply to this Message

MartinH - 28 Jul 2007 16:00 GMT

yeah even with the $ added it doesnt find the box. i suppose sql is
looking in the local box and in AD but somehow cannot find a diff box
account listed in AD.

anyways i solved all my problems and its running like a charm now. we
very happy. thx for thinking with me here!

because wsus wouldnt uninstall i used the Windows Installer CleanUp
Utility to uninstall wsus. that worked. then i reinstalled wsus with
susdb running on a remote box. that did add the DOMAIN/COMPUTER$ to
the sql security. wsus still not running tho so i uninstalled wsus
leaving all data (dbase, logfiles, downloads). then i deleted any
accounts left by wsus. detached susdb from sql2005 and deleted the
databse. then i deleted all accounts associated to wsus i could find
on both sql and wsus boxes and i also removed any wsus related items
in IIS on the wsus box. i uninstalled the windows internal database on
the wsus box and removed the folders manually so now both boxes were
as clean as possible, now i finaly did a new install of wsus using the
downloadled files and creating a new database on sql2005. after
install it started syncing and redoing the wsus setup was a piece of
cake. it is running smooth now for 2 days. YEAY!

im posting this in te hope others have a use for it. i know how
frustrating it is to have a problem and not see the solution.

>> Oops my bad indeed no brackets. now i do and i get this message. both
>> sql as the comuter i want added are in the same domain and i can ping
[quoted text clipped - 8 lines]
>
> CREATE LOGIN [YourDomain\ServerName$] FROM WINDOWS

Reply to this Message