Category Archives: Server Support

SQL Express Upgrade Error

Server Support,

I went to upgrade my old MSDE database to SQL 2005 Express on our McAfee EPO server and I was receiving a ‘-1’ error during the upgrade process with the detail of:

You selected Mixed Mode authentication, but did not provide a strong password. To continue you must provide a strong sa password.

I went through a bunch of different items, but this link detailed that the error is in fact caused by a bum installer of Microsoft’s design:

Copy C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\BPA\bin\BPAClient.dll to C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\BPA

This was one of those ‘no way’ errors that aggravated me to no end.  Why didn’t they fix this?

Power Detection

Server Support,

It’s rather rudimentary, but I do not get a proper notification when we lose power.  Part of this was being spoiled for my first several years that I worked here because there was always someone at work, either an off-shift supervisor or a security guard, who would call me to let me know if power had been lost.  Another part, though, was simply overcoming the technical hurdles of getting the APC UPS units to get a notification through to me via SNMP.  My workaround thus far was to rely on a particular error the key access system for the doors would send me when there was a power hiccup, but after the last brief outage I determined that relying on that fallback measure wasn’t very reliable and that I should set about getting the SNMP messages to work.

The first step was to set up the antiquated network management cards for the APC units.  This was rather simple and just entailed pointing their SNMP settings at the HP Systems Insight Manager setup that I use.  (As an aside, I should point out now that my older APC card had a limit of eight characters for the password but I didn’t notice it until I guessed my way back onto the unit).  I then had to compile the MIB files, but when I tried to import the CFG file into Insight I was getting errors over the lines that had a severity of SEVERE.  After  searching around I came across this post that clued me in and after changing all the ‘SEVERE’s to ‘WARNING’s the CFG successfully imported.  This wasn’t a big deal to me since I planned on setting Insight up to e-mail me WARNING messages as well anyway. (Just looking within Insight it would appear that the error is being generated because there is no ‘SEVERE’ setting within the package.  I would then theorize that swapping out all the ‘SEVERE’s for ‘CRITICAL’s would also work.)

I then wanted to set Insight up to send me an e-mail AND a cellular text message.  After looking around I’d found that I could text my T-Mobile phone by sending an e-mail to <phone number>@tmomail.net.  On older versions of Exchange I remember how to allow a specific system to anonymous relay, but I couldn’t find an equivalent setting in Exchange 2007.  I’d set up a separate connector specific to the Insight server, but it turns out that the relay settings need to be set within the Exchange Management Shell.  After doing that I was getting my text messages from the APC UPS units, but the only issue left to resolve is to figure out how to get the texts in a more consistent fashion since I would receive some texts right away, and others upwards of an hour after the event that generated it.  Long term I might have to rely on a pay-for service, although an even pricier out-of-band method that removes the e-mail server from the loop is even more appealing.

Exchange 2007 Mailbox Size Limit Alerts

Coding, Server Support, ,

I’d grown spoiled with my MOM pack for Exchange 2003 since I was able to tweak it to alert me (the admin) when a user’s mailbox was being issued size limit warnings.  Several times I was able to catch users stuck with items tucked away out of their view, or the storage of large files that’s better suited to the file server.  After moving to Exchange 2007 though Microsoft removed the event log entry that I used to trigger the MOM condition.  My fix was to create a script that would run on the same schedule as the one used to issue mailbox size warnings.  After combing a variety of pages I came up with the following imperfect, but sufficient script:

function send-email($SmtpServer,$From,$To,$subject,$Body){
$smtp = new-object system.net.mail.smtpClient($SmtpServer)
$mail = new-object System.Net.Mail.MailMessage
$mail.From = $From
$mail.To.Add($To)
$mail.Subject = $subject
$mail.Body = $Body
#$mail.IsBodyHtml = $true
$smtp.Send($mail)
}

get-mailboxstatistics|out-file mstat.txt
get-childitem mstat.txt|select-string -pattern “Issue”|out-file results.txt
$File=get-childitem “results.txt”
$FileContent=get-content “results.txt”
[string]$formattedLength = $file.Length
if($file.Length -gt 5){
send-email email.server.com “server@example.com” “admin@example.com” “Mailbox size alert” $FileContent
}

I apologize for the lack of comments, but what I’m doing is snagging the stats, searching for the word ‘Issue’ to see if a warning or some such was issued, then checking the resulting file size of the search and if it’s oversized (i.e. actually contains something) it’s emailed off to the desired contact.

(UPDATE: this e-mail size alert works for Exchange 2010 as well)

(UPDATE 12/6/2013: this e-mail size alert does not work for Exchange 2013.  I’m working to brew up a solution.)

Find the Mailbox

Server Support, ,

I was getting two errors in the log along these lines on my Exchange 2007 server every thirty minutes:

Unable to update Mailbox SD in the DS. Mailbox Guid: xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx. Error Code 0x80070005

I tried the items like this that instruct on how to find the troublesome mailbox (adfind, etc), but nothing would turn up.

At the same time I was looking up how to purge a disconnected mailbox* and I came across this page.  Putting two and two together, I put this line into the Exchange console:

Get-MailboxStatistics | where-object {$_.MailboxGuid -ne $null} | Select DisplayName,MailboxGUID

I was then able to hunt through the output to find the naughty GUIDs and reset the permissions.  Luckily our environment doesn’t have too many mailboxes so I was able to just eyeball it.  I’m sure that there’s a different way to craft the command so that it can just kick out the desired desired mailbox instead of a complete list, but it wasn’t worth the time to find that for our environment.

*Yet another item that was in the GUI of Exchange 2003 that you need obscure commands for in Exchange 2007.

First Difficult PowerShell

Coding, Server Support, ,

UPDATE (12/5/2013): Well it appears that I might be at the end of the line with the in/out board script.  Due to some backend changes, the EWSUtil.dll no longer works consistently.  Microsoft has some instructions on how to brew your own by extracting XML output from the service that can then be digested into calendar data, but no one at my company uses the in/out board enough to justify the monumental amount of time that would be required to re-code the connector.  If you have Exchange 2007/2010 then feel free to read on, but if you have Exchange 2013 this page may only be good as a reference.

UPDATE (5/20/2013): I had to make a few changes so that the free/busy calendar script would run properly on Windows Server 2012/Exchange 2013.  First, I changed the line(s) in the batch file to [C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -command “.’C:\inetpub\schedule\fb9to5v2.ps1′”].  Secondly I had to change the name of the Exchange server for the $casUrl in the script.  Lastly I had to add the line [Add-PSSnapin Microsoft.Exchange.Management.PowerShell.SnapIn] as the first line in all the script files that I call.

UPDATE (12/12/12): An update to PowerShell breaks the way that I have this set up, leading to an error of  “An attempt was made to load an assembly from a network location which would have caused the assembly to be sandboxed in previous versions of the .NET Framework. This release of the .NET Framework does not enable CAS policy by default, blah, blah blah“.  I did two things after which I got it to work again, but unfortunately I don’t know which one it was that made it work.  The first (and easiest) is to force PowerShell to run as an earlier version (in this case the command line to launch the script is [C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -version 2 -PSConsoleFile “C:\Program Files\Microsoft\Exchange Server\v14\bin\exshell.psc1” -command “.’C:\inetpub\schedule\fb9to5v2.ps1′”].  The other thing I did (which I think did the trick, but required PowerShell to clean itself out of memory perhaps as it didn’t work right away) was using a related tip here to add the LoadFromRemoteSources to the powershell.exe.config (I had to create the file).  Anyway…

I’ve had some experience with the Windows PowerShell, but it’s been limited to making obvious changes to scripts that have been exported from Scriptomatic.  Examples of this would include automatically adding an account into the local admins group account on every PC in a domain so that ‘downstream’ admin tasks will execute, and pulling the hard drive capacity and utilization from all the servers in an organization for virtualization sizing data.

That changed for me when I migrated my workplace to Exchange 2007.  Previously we used the old collaborative data objects to build an HTML In/Out schedule board.  Manipulating those simple constructs was fairly easy and a lot of coworkers had grown to rely on the web pages exported by the scripts.  Unfortunately CDOs don’t work with Exchange 2007, necessitating redoing the scripts in Windows PowerShell.

Fortunately Glen Scales had already taken the ball 90% of the way with his FreeBusy In/Out Board script (Note: to make any of this work you will need the EWSUtil.dll that Glen references).  Unfortunately, the last 10% proved to be daunting due to my lacking a high level understanding of PowerShell and (even worse) the general opacity of the programing language itself.  There were some minor HTML duties to attend to, which were easy enough, but the most aggravating aspect of Glen’s script was the fact that the names as exported were not in alphabetical order. I figured this had to be easy so I did something like this:

$mbHash = @{$mbHash1 | Sort-Object -property Value}

But then I discovered “no dice” as hash tables are one way with the key and cannot be sorted by value ‘subdata’.  How about resorting it using the old trusty nested ‘For’ loops?  Although this may be possible in some fashion, hash tables cannot be indexed (i.e. $array[1]=1, but $hashtable[1]=’something’ cannot be done), and since my method relied on a sorted array (as the hash table itself could not be sorted directly) and the hash table entries didn’t work right with the various string commands (since the destination would be cast to ‘hash’), and… well you get the picture.  I decided to ‘brute force’ the sorting when I found that the list order in the final table was reliant upon the $key variable later in the script (this had to be ‘discovered’ as there seems to be some Internet rule about NOT putting comments in PowerShell scripts).  My successful plan was to co-op the entries that Glen was feeding it with my own sorted list ($key1):

########### Code that alphabetizes the ‘key’ file used to build the In/Out data grid
$mbn = @()
$key1 = @()

#Grab the name list from the hash table and sort it
$mbn=$mbHash.values|sort-object

#write the list out to the hard drive and read it back in
#to convert the data to a regular text array
#This kludgey way was the only way I could convert $mbn to a string array. Is there a better way?  I sure hope!
$mbn|out-file mbn.txt
$mbnt=Get-Content mbn.txt

for($i=0; $i -lt $mbnt.Count; $i++){
#$hashidx will hold the unsorted e-mail address list. When the sorted name ($mbnt) equals the value of
# the e-mail key ($mbhash.$hashidx), then add the key (i.e. email address) to $key1 in the proper sequence.
foreach($hashidx in $mbHash.keys){
$a=$mbnt[$i]
$b=$mbhash.$hashidx.ToString()
if($a -eq $b){
$key1+=$hashidx
}
}
}
########### End of alphabetizing code

I then switched out Glen’s line that read:

foreach($key in $fbents.keys){

With:

foreach($key in $key1){

That was my big change, but I include the rest of the code that includes a number of changes, including a ‘key’ grid, logo functionality, and a date stamp:

[void][Reflection.Assembly]::LoadFile(“EWSUtil.dll”)
########### Pause function for troubleshooting
function Pause ($Message=”Press any key to continue…”){
Write-Host -NoNewLine $Message
$null = $Host.UI.RawUI.ReadKey(“NoEcho,IncludeKeyDown”)
Write-Host “”
}

$casUrl = https://mail.domain.com/ews/exchange.asmx
$mbHash = @{ }
########### Added ‘ResultSize unlimited to pull in Forest scope rather than just Domain
get-mailbox -ignoredefaultscope -ResultSize unlimited | foreach-object{
if ($mbHash.ContainsKey($_.WindowsEmailAddress.ToString()) -eq $false){
$mbHash.Add($_.WindowsEmailAddress.ToString(),$_.DisplayName)
}
}

$mbs = @()

$ewc = new-object EWSUtil.EWSConnection($mbMailboxEmail,$false,”USERNAME”,”PASSWORD”,”DOMAIN”,$casUrl)
$drDuration = new-object EWSUtil.EWS.Duration
########### Decreased start time to 06:30
$drDuration.StartTime = [DateTime]::Parse([DateTime]::Now.ToString(“yyyy-MM-dd 06:30”))
$drDuration.EndTime = [DateTime]::Parse([DateTime]::Now.ToString(“yyyy-MM-dd 17:00”))

$batchsize = 100
$bcount = 0

#Note: changed bresult to hash table to take advantage of hash table commands
$bresult = @{}

if ($mbHash.Count -ne 0){
foreach($key in $mbHash.keys){
if ($bcount -ne $batchsize){
$mbs += $key
$bcount++
}
else{
$bresult += $ewc.GetAvailiblity($mbs, $drDuration, 30)
$mbs = @()
$bcount = 0
$mbs += $key
$bcount++
}
}
}

$bresult += $ewc.GetAvailiblity($mbs, $drDuration, 30)
########### Dump system address from the tables
$mbHash.remove(“USERNAME@DOMAIN.com”)
$bresult.remove(“USERNAME@DOMAIN.com”)

$frow = $true

########### Begin build of HTML document
$fbdate=Get-Date
$fbBoard = $fbBoard + “<html>”
$fbBoard = $fbBoard + “<head>”
$fbBoard = $fbBoard + “<title>In & Out Board</title>”
$fbBoard = $fbBoard + “</head>”
$fbBoard = $fbBoard + “<body bgcolor=#FFFFFF text=#0000CC link=#0000CC vlink=#660099 alink=#0000FF>”

### Originally I had put in a shaded color background
#$fbBoard = $fbBoard + “<body bgcolor=#FFFFFF background=graphics/bg.gif text=#0000CC link=#0000CC vlink=#660099 alink=#0000FF>”

#### Below is the code I used to put company art on the page
#$fbBoard = $fbBoard + “<table width=37% border=0 cellspacing=0 align=center>”
#$fbBoard = $fbBoard + “<tr>”
#$fbBoard = $fbBoard + “<td width=31% height=58><img src=graphics/LOGO.gif width=100 height=75></td>”
#$fbBoard = $fbBoard + “<td width=39% height=58>”
#$fbBoard = $fbBoard + “<div align=center><img src=graphics/bg.jpg width=207 height=65></div>”
#$fbBoard = $fbBoard + “</td>”
#$fbBoard = $fbBoard + “</tr>”
#$fbBoard = $fbBoard + “</table>”
$fbBoard = $fbBoard + “<hr align=center width=80%>”
$fbBoard = $fbBoard + “<p align=center><i>Last updated on: ” + $fbdate + “</p>”

########### Code that alphabetizes the ‘key’ file used to build the
########### In/Out data grid
$mbn = @()
$key1 = @()

#Grab the name list from the hash table and sort it
$mbn=$mbHash.values|sort-object

#write the list out to the hard drive and read it back in
#to convert the data to a regular text array
$mbn|out-file mbn.txt
$mbnt=Get-Content mbn.txt

for($i=0; $i -lt $mbnt.Count; $i++){
#$hashidx will hold the unsorted e-mail address list. When the sorted name ($mbnt) equals the value of
# the e-mail key ($mbhash.$hashidx), then add the key (i.e. email address) to $key1 in the proper sequence.
foreach($hashidx in $mbHash.keys){
$a=$mbnt[$i]
$b=$mbhash.$hashidx.ToString()
if($a -eq $b){
$key1+=$hashidx
}
}
}

########### End of alphabetizing code

foreach($fbents in $bresult){
foreach($key in $key1){
if ($frow -eq $true){
$fbBoard = $fbBoard + “<table><tr bgcolor=`”#95aedc`”>” +”`r`n”
$fbBoard = $fbBoard + “<td align=`”center`” style=`”width=200;`” ><b>User</b></td>” +”`r`n”
for($stime = $drDuration.StartTime;$stime -lt $drDuration.EndTime;$stime = $stime.AddMinutes(30)){
$fbBoard = $fbBoard + “<td align=`”center`” style=`”width=50;`” ><b>” + $stime.ToString(“HH:mm”) + “</b></td>” +”`r`n”
}
$fbBoard = $fbBoard + “</tr>” + “`r`n”
$frow = $false
}
for($stime = $drDuration.StartTime;$stime -lt $drDuration.EndTime;$stime = $stime.AddMinutes(30)){
$valuehash = $fbents[$key]
if ($stime -eq $drDuration.StartTime){

#Note: added missing ‘<tr>’
$fbBoard = $fbBoard + “<tr><td bgcolor=`”#CFECEC`”><b>” + $mbHash[$valuehash[$stime.ToString(“HH:mm”)].MailboxEmailAddress.ToString()] + “</b></td>”  + “`r`n”
}
switch($valuehash[$stime.ToString(“HH:mm”)].FBStatus.ToString()){
“0” {$bgColour = “bgcolor=`”#C0C0C0`””}
“1” {$bgColour = “bgcolor=`”#52F3FF`””}
“2” {$bgColour = “bgcolor=`”#153E7E`””}
“3” {$bgColour = “bgcolor=`”#4E387E`””}
“4” {$bgColour = “bgcolor=`”#98AFC7`””}
“N/A” {$bgColour = “bgcolor=`”#98AFC7`””}
}
$title = “title=”
if ($valuehash[$stime.ToString(“HH:mm”)].FBSubject -ne $null){
if ($valuehash[$stime.ToString(“HH:mm”)].FBLocation -ne $null){
$title =  $title + “`”” + $valuehash[$stime.ToString(“HH:mm”)].FBSubject.ToString() + ” ” + $valuehash[$stime.ToString(“HH:mm”)].FBLocation.ToString() + “`” ”
}
else {
$title =  $title + “`”” + $valuehash[$stime.ToString(“HH:mm”)].FBSubject.ToString() + “`” ”
}
}
else {
if ($valuehash[$stime.ToString(“HH:mm”)].FBLocation -ne $null){
$title =  $title + “`”” + $valuehash[$stime.ToString(“HH:mm”)].FBLocation.ToString() + “`” ”
}
}
if($title -ne “title=”){
$fbBoard = $fbBoard + “<td ” + $bgColour + ” ” + $title + “></td>”  + “`r`n”
}
else{
$fbBoard = $fbBoard + “<td ” + $bgColour + “></td>”  + “`r`n”
}

}
$fbBoard = $fbBoard + “</tr>”  + “`r`n”
}
}
$fbBoard = $fbBoard + “</table>”  + ”  ”
$fbBoard = $fbBoard + “<br>”

##########  Key grid
$fbBoard = $fbBoard + “<center><table align=center cellpadding=’0′ cellspacing=’0′ cols=’2′ width=’80%’ bordercolor=’#FFFFFF’ border=’1′ bordercolorlight=’#FFFFFF’ bordercolordark=’#FFFFFF’>”
$fbBoard = $fbBoard + “<tr valign=’top’>”
$fbBoard = $fbBoard + “<td width=’2%’ bgcolor=’#52F3FF’>&nbsp</td>”
$fbBoard = $fbBoard + “<td align=’left’ width=’18%’ valign=’top’><font color=’#000000′ face=’Arial, Helvetica, sans-serif’>&nbsp;Tentative</font></th>”
$fbBoard = $fbBoard + “<td width=’2%’ bgcolor=’#C0C0C0′>&nbsp</td>”
$fbBoard = $fbBoard + “<td align=’left’ width=’18%’ valign=’top’><font color=’#000000′ face=’Arial, Helvetica, sans-serif’>&nbsp;Free</font></th>”
$fbBoard = $fbBoard + “<td width=’2%’ bgcolor=’#153E7E’>&nbsp</td>”
$fbBoard = $fbBoard + “<td align=’left’ width=’18%’ valign=’top’><font color=’#000000′ face=’Arial, Helvetica, sans-serif’>&nbsp;Busy</font></th>”
$fbBoard = $fbBoard + “<td width=’2%’ bgcolor=’#4E387E’>&nbsp</td>”
$fbBoard = $fbBoard + “<td align=’left’ width=’18%’ valign=’top’><font color=’#000000′ face=’Arial, Helvetica, sans-serif’>&nbsp;Out of Office</font></th>”
$fbBoard = $fbBoard + “<td width=’2%’ bgcolor=’#000000′>&nbsp</td>”
$fbBoard = $fbBoard + “<td align=’left’ width=’18%’ valign=’top’><font color=’#000000′ face=’Arial, Helvetica, sans-serif’>&nbsp;No Information</font></th>”
$fbBoard = $fbBoard + “</tr>”
$fbBoard = $fbBoard + “</table></center>”

$fbBoard = $fbBoard +  “<br>”
$fbBoard = $fbBoard +  “<table border=’0′ cellspacing=’10’ height=’12’ width=’100%’>”
$fbBoard = $fbBoard +  “<tr>”
$fbBoard = $fbBoard +  “<a name=inout></a><b><u>More…</u></b><br>Black blocks on the grid indicate that nothing has been scheduled within a couple weeks or so of today’s date. In order to view more detail or days in the future you will need to use the robust features from within Outlook. This can be done most easily by <a href=http://support.microsoft.com/kb/293162>adding a group Calendar</a> (first tip). ”
$fbBoard = $fbBoard +  “</tr>”
$fbBoard = $fbBoard +  “</table>”
$fbBoard = $fbBoard +  “</body>”
$fbBoard = $fbBoard +  “</html>”

$fbBoard | out-file “inout.htm”

To schedule this Exchange 2007 script I put the following command in a batch file and referenced it in the task scheduler:

C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -PSConsoleFile “C:\Program Files\Microsoft\Exchange Server\bin\exshell.psc1” -command “.’fb9to5v2.ps1′”

An example of the output from the full script is here.

Extra Credit! Later I also added code and changed two lines so that I could build multiple web pages showing info for future dates.  I’m sure that it’s possible to feed the days forward desired in as a command line variable, but I don’t have time to work on that at the moment.  The applicable code is:

########### Add a day
$a=[DateTime]::Now.AddDays(1)
[string]$aYear=$a.year
[string]$aDay=$a.day
[string]$aMonth=$a.month
$StartDate=$aYear + “-” + $aMonth  + “-” + $aDay + ” 06:30″
$EndDate=$aYear + “-” + $aMonth  + “-” + $aDay + ” 17:30″
# snag dates for web header (not shown)
[string]$WebDate=$aMonth + “/” + $aDay + “/” + $aYear
$WebDateDay=$a.dayofweek
$b=get-date
$WebDateNow=$b.ToShortDateString()

$ewc = new-object EWSUtil.EWSConnection($mbMailboxEmail,$false,”USERNAME”,”PASSWORD”,”DOMAIN”,$casUrl)
$drDuration = new-object EWSUtil.EWS.Duration
########### Decreased start time to 06:30, changed lines to process time strings
$drDuration.StartTime = [DateTime]::Parse($StartDate)
$drDuration.EndTime = [DateTime]::Parse($EndDate)

I’ll have to leave it to individuals to decide how to integrate the code.