WSUS MSDE Password and DB Schema

I performed a default installation of WSUS on a server (using the provided
MSDE db) but i was never prompted to set a password for the database.

Is there a way i can find out what the installer set the password to and
change the password?

Also does anyone know a program to view the backend of the wsus database or
a anywhere that i can find out its table structure?

Someone had suggested in this group to use SQL Manager Lite
<http://www.sqlmanager.net/products/mssql/manager> to look at and
manipulate the MSDE database.

I tried it and could not get it to connect to the MSSQL$WSUS database.
No one responded here on how to do that but maybe you'll be more fortunate.

I have successfully used MS Query on the server to view the db and run queries. You have to create the data source using servername\WSUS instead of just the server name and use the SQL driver.

This is because the WSUS database environment only uses Windows
Authentication, not SQL Server Authentication. A "database password" is only
required if the 'sa' account is to be used, which is only used when SQL
Server Authentication is enabled.

There is no password; thus no way to change a password. Any ADMIN enabled
account should be able to access the database.

The WSUS database 'SUSDB' does not have a published schema, and directly
accessing the database, even to simply query data is not supported. The
recommended methodology is to use the WSUS API and .NET programming tools.

Having said that, the /best/ way to access the WSUS database is to download
and install SQL 2005 Express Manager. The next best way, but not licensed
for this use, is to install SQL Server Enterprise Manager from the SQL
Server 2000 CDROM (Eval, Standard, Enterprise Editions).

The primitive way is to execute SQL commands using osql.exe which is already
installed on your WSUS server.

I believe that's because SQL 2005 Express Manager is trying to use TCP/IP by
default, and the WSUS (W)MSDE installation has networking disabled, thus no
TCP/IP, only Named Pipes. If so, the 'fix' would be to ensure SQL 2005
Express Manager is configured to use Named Pipes for communication with the
(W)MSDE instance.

That makes sense, thanks for the tip!

Thanks to all for the replies.

In the end I used MS SQL Server Management Studio Express and it works
perfectly.

Here are 2 scripts that i wrote, the email code, debug code belong to Steven
Gill's Auto approve as does all but one AND statement in the Notify Script.

If you look at his script there are alot of similarities between mine and
his, this is because i used his script as a template and then butchered the
code. :D

(^^ bit of over kill there but dnt want to be accused of stealing code and
not crediting the orig author)

-----------------------
Update Notify Script
-----------------------
-----------------------

''WSUS-Notify based on Auto Approve WSUS Eulas
''
''Usage and more info at:
''
''changelog
''V0.1a 19/06/05 - Some bugs fixed
''V0.1 11/06/05 - Steven Gill (gillsr at iee dot org)
''
''To use:
''Set up a scheduled job to run "cscript c:\autoapproveupdates.vbs" and run
with admin rights
''
''
'' Script rewritten by Ryan McLean (ryan1_00 at hotmail d0t com) 15/08/2006
in order to notify about update instead of approve
'' and to add ability to log.
''
'' -------------------------
'' Configuration
'' -------------------------

''Can use localhost if you have an smtp server locally

EmailDstName = "<Recipient-Address>"
EmailReplyToName = "<Sender-Address>"
EmailSrvName = "<smtp-server>"

serveradminurl = "http://<WsusServer>/WSUSAdmin"

WSUS_Con_STRING = "Provider=SQLOLEDB;Initial Catalog=SUSDB;Data
Source=<SERVER>\WSUS;Integrated Security=SSPI;Persist Security Info=False"

logging = true
Const LOG_FILE = "Script.log"
debugflag = false
strMsgBody = ""
strTitleList = ""


'' -------------------------
'' Constants
'' -------------------------
' Constants for File opening modes
Const forREAD = 1
Const forWRITE = 2
Const forAPPEND = 8

'' -------------------------
'' Identify if updates need to be approved
'' -------------------------

QueryString = "SELECT vwMinimalUpdate.UpdateID, tbProperty.RevisionID,
vwMinimalUpdate.RevisionNumber, tbProperty.EulaExplicitlyAccepted,
tbProperty.RequiresReacceptanceOfEula, vwMinimalUpdate.State,
vwUpdateLocalizedProperties.Title, vwMinimalUpdate.IsSuperseded FROM
vwMinimalUpdate INNER JOIN tbProperty ON vwMinimalUpdate.RevisionID =
tbProperty.RevisionID INNER JOIN vwUpdateLocalizedProperties ON
tbProperty.RevisionID = vwUpdateLocalizedProperties.RevisionID WHERE
(vwMinimalUpdate.EulaID IS NOT NULL) AND
(vwUpdateLocalizedProperties.ShortLanguage = 'en') AND (vwMinimalUpdate.State
<> 5)"




set rs = CreateObject("ADODB.Recordset")
rs.ActiveConnection = WSUS_Con_STRING
rs.Source = QueryString
rs.CursorType = 0
rs.CursorLocation = 2
rs.LockType = 3
rs.Open()

NumApproved = 0

While (not rs.eof)

Title = rs.fields.item("Title").value
RevisionID = rs.fields.item("RevisionID").value
UpdateID = rs.fields.item("UpdateID").value
RevisionNumber = rs.fields.item("RevisionNumber").value
EulaExplicitlyAccepted = rs.fields.item("EulaExplicitlyAccepted").value
State = rs.fields.item("State").value


strTitleList = strTitleList & Title & vbNewline
NumApproved = NumApproved +1


rs.MoveNext()
Wend

'' -------------------------
'' Send email if new patches are available
'' -------------------------

if (NumApproved > 0) then

if (NumApproved > 1) then strPlural="s"

strMsgBody = strMsgBody & NumApproved & " Update" & strPlural & " need to
be approved for Installation" & vbcrlf
strMsgBody = strMsgBody & vbcrlf & serveradminurl & vbcrlf
strMsgBody = strMsgBody & vbcrlf & strTitleList

' Create the JMail message Object
set msg = CreateOBject("JMail.Message")

msg.Logging = true
msg.silent = true
msg.From = EmailReplyToName
msg.AddRecipient(EmailDstName)
msg.Subject = "[WSUS Server] " & NumApproved & " Update" & strPlural & "
need to be approved for Installation"
msg.Body = strMsgBody

if (msg.Send(EmailSrvName)) then
LogIt("Email Successfully sent, " & NumApproved & " Update" & strPlural &
" need to be approved")
else
LogIt("Error sending email!")
Logit(msg.log)
end if

else
LogIt("No updates need to be approved for Installation")
End if

rs.close()


sub LogIt(logtxt)

strMsgBody = strMsgBody & now() & ": " & logtxt & vbcrlf & vbcrlf

if (logging) then
Set objFSO = CreateObject("Scripting.FileSystemObject")

IF objFSO.FileExists(LOG_FILE) Then
Set objFile = objFSO.OpenTextFile(LOG_FILE, forAPPEND)
Else
Set objFile = objFSO.CreateTextFile(LOG_FILE)
objFile.Close
Set objFile = objFSO.OpenTextFile(LOG_FILE, forWRITE)
End If

objFile.Writeline "---------------------"
objFile.Writeline now() & ": " & logtxt
objFile.Writeline "---------------------"
objFile.close
end if

if (debugflag) then
wscript.echo(now() & ": " & logtxt)
end if
end sub

------------------------------
------------------------------
Unassigned computers Script
------------------------------

''WSUS-Unassigned
''
'' by Ryan McLean 11/09/2006
''
''
'' Written in order to notify about unassigned computers
'' instead of approve updates and to add ability to log.
''
'' Inspired by autoapproveupdates.vbs script written by Steven Gill
'' A lot of the code from the above script was used for this one
''
''
''To use:
''Set up a scheduled job to run "cscript c:\WSUS-Unassigned.vbs" and run
with admin rights
''
''
'' -------------------------
'' Configuration
'' -------------------------

''Can use localhost if you have an smtp server locally

EmailDstName = "ithelp@accelrys.com"
EmailReplyToName = "ithelp@accelrys.com"
EmailSrvName = "camdmail01.accelrys.net"

serveradminurl = "http://wsus-cam/WSUSAdmin"

WSUS_Con_STRING = "Provider=SQLOLEDB;Initial Catalog=SUSDB;Data
Source=IT4-CAM\WSUS;Integrated Security=SSPI;Persist Security Info=False"

logging = true
Const LOG_FILE = "test.log"
debugflag = false
strMsgBody = ""
strTitleList = ""


'' -------------------------
'' Constants
'' -------------------------
' Constants for File opening modes
Const forREAD = 1
Const forWRITE = 2
Const forAPPEND = 8

'' -------------------------
'' Identify if updates need to be approved
'' -------------------------

QueryString = "SELECT tbComputerTarget.FullDomainName FROM tbComputerTarget
LEFT JOIN tbTargetInTargetGroup ON tbComputerTarget.TargetID =
tbTargetInTargetGroup.TargetID WHERE tbTargetInTargetGroup.TargetID IS NULL;"

set rs = CreateObject("ADODB.Recordset")
rs.ActiveConnection = WSUS_Con_STRING
rs.Source = QueryString
rs.CursorType = 0
rs.CursorLocation = 2
rs.LockType = 3
rs.Open()

unassigned = 0

While (not rs.eof)

FullDomainName = rs.fields.item("FullDomainName").value

strTitleList = strTitleList & FullDomainName & vbNewline
unassigned = unassigned+1

rs.MoveNext()
Wend


'' -------------------------
'' Send email if new patches are available
'' -------------------------

if (unassigned > 0) then

if (unassigned > 1) then strPlural="s"

strMsgBody = strMsgBody & unassigned & " Computer" & strPlural & " need to
be assigned groups" & vbcrlf
strMsgBody = strMsgBody & vbcrlf & serveradminurl & vbcrlf
strMsgBody = strMsgBody & vbcrlf & strTitleList

' Create the JMail message Object
set msg = CreateOBject("JMail.Message")

msg.Logging = true
msg.silent = true
msg.From = EmailReplyToName
msg.AddRecipient(EmailDstName)
msg.Subject = "[WSUS Server] " & unassigned & " Computer" & strPlural & "
need to be assigned a group"
msg.Body = strMsgBody

if (msg.Send(EmailSrvName)) then
LogIt("Email Successfully sent, " & unassigned & " Computer" & strPlural &
" need to be assigned a group")
else
LogIt("Error sending email!")
Logit(msg.log)
end if

else
LogIt("No unassigned Computers found")
End if

rs.close()


sub LogIt(logtxt)

strMsgBody = strMsgBody & now() & ": " & logtxt & vbcrlf & vbcrlf

if (logging) then
Set objFSO = CreateObject("Scripting.FileSystemObject")

IF objFSO.FileExists(LOG_FILE) Then
Set objFile = objFSO.OpenTextFile(LOG_FILE, forAPPEND)
Else
Set objFile = objFSO.CreateTextFile(LOG_FILE)
objFile.Close
Set objFile = objFSO.OpenTextFile(LOG_FILE, forWRITE)
End If

objFile.Writeline "---------------------"
objFile.Writeline now() & ": " & logtxt
objFile.Writeline "---------------------"
objFile.close
end if

if (debugflag) then
wscript.echo(now() & ": " & logtxt)
end if
end sub


-------------------
END
-------------------

[techguru1]

To connect to SUSDB where WSUS 3.0 is install locally ( LOCALHOST ) and uses the Windows Internal Database ( SQL 2005 Express ), use SQL Server Management Studio Express and reference the Named Pipe location exactly as shown below:

\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

To connect to the SUSDB using VB.NET, ensure that your connection for the WSUS Server points to the aforementioned Named Pipes location and that the database is "SUSDB".

You may find interest in a 3rd party tool named "WSUS Easy Reporter" from Swarmsoft.net. Note that, at the time of writing this, the product had a bug: the web.config file was missing an appSettings entry for WSUSDB. It is easily corrected by manually adding the entry as:

<add key="WSUSDB" value="SUSDB" />

As noted above, the WSUSServer value should be defined as:

<add key="WSUSServer" value="\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query" />

Best Regards,
Paul