Tag Archives: SQL

Forefront TMG IP Issue

Coding, ,

I typically have two issues that I run into with our Forefront TMG (aka ISA 2010) proxy server:

  1. I have certain blocks of IP addresses blocked on the Cisco firewall and occasionally a user will repeatedly hit a website that sits in one of those blocks.  I will then have to find out who is doing this as the firewall will always just report the address of the proxy server for the traffic.
  2. Occasionally a user will download malware and I’ll need to find out where they got it from so that the source of the traffic can go into the block list in point #1.

Either way I end up running a report on a system at the site which has SQL Server Management Studio installed on it with a query something like the below:

SELECT [ClientIP]
      ,[ClientUserName]
      ,[ClientAgent]
      ,[ClientAuthenticate]
      ,[logTime]
  FROM [ISALOG_20110317_WEB_000].[dbo].[WebProxyLog]
 WHERE [ISALOG_20110317_WEB_000].[dbo].[WebProxyLog].[DestHostIP] = ‘d3bd0e17-FFFF-0000-0000-000000000000’

You’ll note that the address on the bottom line is rather unintelligible since it uses a semi-IPv6 format used by TMG.  To work around this I had been doing hand translation using the calculator in Windows to figure out what the address I am looking for should be.  However, I grew tired of that and wrote a program that will handle the translation/conversion to and from a regular IPv4 address and Forefront TMG 2010 address (because of some apparent vague interest, I’ve packed a zip with just the executable (no code) here).

I want to note though, that since it was casually put together I didn’t code it to handle any exceptions.  The source code is included though, so if you want to code around some fat fingering then feel free.

Migrating from 32bit SharePoint Services

Server Support,

This is my definitive guide on migrating from 32 bit Windows SharePoint Services 3 to SharePoint Foundation 2010 (64 bit only, oof!).  It’s not quite as definitive as I would like as I barely had time to work this through once, let alone multiple times:

  1. I changed the user account that runs WSS3 from ‘NETWORK SERVICE’ to a domain account (though I’m unsure if this step is required).  I originally did this when trying to restore the 32bit WSS3 onto the 64bit box and then using 64bit WSS3 to try and upgrade it.  Don’t do that by the way.  Anyway it made working with the database easier on the whole anyway.
  2. Backed up the SharePoint content database, called STS_servername_1 in my case. (This server used the ‘Windows Internal Database’, an extra crippled version of the already crippled SQL express.  I had installed the SQL 2005 tools on there for a different project so I used the ‘SQL Server Configuration Manager’ to change the service argument to add “;-T7806” and I then used the SQL management studio to do the backup).
  3. Installed 64bit Sql Server 2008 express on the new 64 bit server (I named the main instance the same as the instance on the 32bit box, not sure if that matters)
  4. Installed SharePoint Foundation on the new 64 bit server (it automatically set itself up in the SQL instance I installed).
  5. Restored the content database to the new server (again, using the management studio; I know that it can be done through the command prompt but I like the visual feedback).
  6. Within the Central Administration site on the new server I went to ‘Manage Content Databases’, clicked on the preinstalled content database and removed it in the subsequent interface.  This step removes the content linked to ‘/’ so that the restored database can go there.
  7. Added the restored database to the farm using stsadm. (in our case: stsadm -o addcontentdb -databasename STS_servername_1 -databaseserver newserver-url http://newserver/)
  8. Upgraded the content database using the ‘upgrade-spcontentdatabase’ command within the Sharepoint PowerShell.
  9. The content should now show up when you pull up http://newserver/ , but to complete the upgrade (and keep the content database from showing as ‘mostly upgraded’ or something along those lines) I had to upgrade the view under ‘Site Settings’ within the Sharepoint site and then going to (I believe) ‘Visual Upgrade’.

Obviously our setup is fairly simple with one server driving the whole ‘stack’.  Our content is also fairly static (it didn’t matter that the database backup that I was working with was a day old).  Your results, of course, may vary (but will probably do a sight better than trying to put something together from the amalgamation of websites that only convey one piece of the puzzle!).

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?