netnerds.net

17Jan/0712

T-SQL: Parse Top Level Domain from URL

I keep track of all hits to my website RealCajunRecipes.com in a SQL table called hitcounter which has columns for the user's IP, browser, referring URL and the date. Recently, I saw a surge in traffic and wanted to know which domains were sending the traffic our way. After getting tired of issuing ad-hoc queries that included WHERE clauses like "where referer like '%google%'", I created a SQL Server user-defined function (UDF) to extract the domain from the referring URL.

parseurl.sql

CREATE      FUNCTION [dbo].[parseURL]  (@strURL varchar(1000))
RETURNS varchar(1000)
AS
BEGIN
IF CHARINDEX('http://',@strURL) > 0 OR CHARINDEX('https://',@strURL) > 0
-- Ghetto-tastic
SELECT @strURL = REPLACE(@strURL,'https://','')
SELECT @strURL = REPLACE(@strURL,'http://','')
SELECT @strURL = REPLACE(@strURL,'www','')
-- Remove everything after "/" if one exists
IF CHARINDEX('/',@strURL) > 0 (SELECT @strURL = LEFT(@strURL,CHARINDEX('/',@strURL)-1))

-- Optional: Remove subdomains but differentiate between www.google.com and www.google.com.au
IF (LEN(@strURL)-LEN(REPLACE(@strURL,'.','')))/LEN('.') < 3 -- if there are less than 3 periods
SELECT @strURL = PARSENAME(@strURL,2) + '.' + PARSENAME(@strURL,1)
ELSE -- It's likely a google.co.uk, or google.com.au
SELECT @strURL = PARSENAME(@strURL,3) + '.' + PARSENAME(@strURL,2) + '.' + PARSENAME(@strURL,1)
RETURN @strURL
END

This script does the following:
1. Checks to see if the string is an URL
    (example: str = http://www.search.google.com.au/?q=netnerds)
2. Removes http, https and www (str = search.google.com.au/?q=netnerds)
3. Removes everything after the slash (str = search.google.com.au)
4. Removes excessive subdomains (str = google.com.au)

The script isn't perfect; I saw things like mysearch.myway.com get by but it's good enough for general use. If you'd like to see the entire domain, just remove the 4 line chunk marked "Optional."

To call this using SQL, modify this sample script to suite your environment:

SELECT COUNT(*) as theCount, dbo.parseURL(referer) as referer FROM hitcounter
WHERE referer IS NOT NULL
GROUP BY dbo.parsedomain(referer)
ORDER BY thecount DESC

Your results should look something like this

11831 google.com
10542 yahoo.com
9101 msn.com
746 google.ca
624 google.co.uk

Note: NULLs aren't parsed and thsu won't kill this function..they'll just show up as NULL.

Posted by: Chrissy   Filed under: SQL Server 12 Comments
16Jan/070

OWA: Expired Password Causes Execute Access Forbidden

Recently, a user trying to login to OWA encountered the following error:

HTTP 403.1 Forbidden: Execute Access Forbidden
You have attempted to execute a CGI, ISAPI, or other executable program from a directory that does not allow programs to be executed.

Another network administrator noticed that the URL was strange too. The user had been directed to:

https://owa.mydomain.com /iisadmpwd/aexp.htr?https:// owa.mydomain.com/exchange/USA/

A quick Googling showed mentions of an expired password but the user was able to login to the domain so we were a bit baffled. As it turns out, we're in the middle of a migration and the user's account on the old domain which still hosts OWA/Exchange was expired but his password on the new domain account was still valid. The user was also not prompted to change his password in OWA because we did not enable that feature. So if you run into something similar, ensure that the user's account does not have "User Must Change Password At Next Logon" checked.

Posted by: Chrissy   Filed under: Exchange, IIS, Security No Comments
13Jan/078

The Things In My Swiss Army Backpack

I've got two 10 page papers to write by Wednesday so I figure there's no better time to write a post about the things you can find in my Swiss Synergy backpack. This backpack is rather large but its size often comes in handy (grocery store trips and school finals come to mind). I used to value micro sized bags but living the portable life I do in San Francisco requires something a little bigger. So, to follow the format of my Software I Use Daily post...

Electronics

  • The Sony Ericsson w600i
    My three cell phone requirements include: great responsiveness, small size and usable keys. I found all of these in a Sony w600i. I thought the phone was pretty cool when I first saw it but never expected the number of responses I've received when using it.

    This phone does just about everything; it's an alarm clock, a flashlight, a hand-held gaming device, a camera, an appointment reminder, a chick magnet, a radio, an mp3 player and the best way to see when my next train is coming (using the browser and nextbus.com). The interface is attractive and easy to use and I've only got a few small complaints about the phone as a whole:

    1. If you use the USB cable, you are at risk of an irrepairable 'white screen of death.' It happened to me and I was without a phone for about 3 days (thankfully, Skype was still free at the time). Using bluetooth is a slower but safer method of file transfer.
    2. It can't be charged using the provided USB cable
    3. You need to use a sorta annoying adapter to plug into headphones.

    One of the things I love about the w600 is its ability to set any sized mp3 as a ringtone. Right now, when people call, my phone starts jammin the high quality Cajun beats. The reception is also top notch.

  • The Western Digital 120 GB Passport
    I bought this hard drive at buy.com for a measly $80! It's USB powered and pocket sized. The encryption software that came on it kinda sucked so I wiped it and used TrueCrypt to encrypt the entire thing. Awesomeness.
  • Mio GPS
    My BFF, Brandon, got me this GPS for Christmas! I used it on a daily basis while vacationing in Louisiana; I even used it on the flight back home. It's got a day and night mode and a huge "point of interest" database that even found my favorite restaurant, Hebert's Steakhouse in Kaplan, Louisiana. Here in San Francisco, I use the Mio's pedestrian mode to find nearby sushi places and walk to them. If only Muni bus routes were programmed in there too...
  • Bose TriPort Headphones
    Audiophiles often diss Bose but I compared these to a few others and they were incredibly crisp. I use them while on the bus/subway and on the airplane; they fit snugly over my ears and earplugs.
  • Microsoft Wireless USB Mouse
    I'm on my second one.. I used the first one so much but it broke it after spilling raspberry mocha all over it. Once my awesome girlfriend saw my distraughtness, she gave me hers!
  • Apple Macbook
    I won't be making this mistake again.. I knew buying an Apple Rev A product was risky but this just sucks. This laptop looks great and is fast as hell but it's recurring fan issues make me want to hasten next year's budgeted laptop purchase. I'm considering, strangly enough, purchasing a Fujitsu laptop. It's not the most popular brand, but I had one in 2003 and it was one of my favorite laptops.

Other Stuff

  • Mighty Leaf Vanilla Bean Black Tea
    A delicious source of on-the-go caffiene. Just add hot water.
  • Tide-to-Go
    This stuff seriously works! I've used it to remove coffee from my khakis and soy sauce from my prized puffy vest. Everyone always asks if it works when I bust it out and I always enthusiastically answer yes.
  • My Student ID
    I'm currently a senior at the University of San Francisco and I LOVE it. I'll be graduating in December with a BSc in Information Science. The program is geared towards working adults and so I only have to get my butt to class once a week (and some Saturdays). All of my classes are pre-set and each semester, my advisor contacts ME to tell me how far along I am with my degree and provides me with an audit. There's no guesswork involved and thus no question as to whether my application for graduation will have problems. Also, USF automatically registers me for class.. all I really do is pay tuition (a hefty amount), show up and do my homework.

    During the past year that I have been in school, I've also figured out how to properly explain Information Science to non-computer people. I tell them "Computer Engineering deals with computer hardware. Computer Science deals with computer software. Information Science deals with the data and information that utilizes computer hardware and software." Now to figure out how to simply explain the title "Database Administrator."

  • My Zipcar Zipcard
    When not using my Chevrolegs to walk around the city, I rent fun-to-drive cars by the hour or by the day at zipcar.com. Of all the car-share programs I evaluated, I liked this one the best. The rates are $8.50/hr or $65/day and that includes gas and insurance.
  • Huggies-Wipes-To-Go
    I once had a friend who always carried around a spoon and a blanket. I asked him why and he said "You'd be surprised how many times these things come in handy." Same thing goes for these wipes on the go. Not only is it a great TP replacement for the times that the stall you're in has just run out but it also effectively cleans the mystery stuff you just put your hand in while riding the bus.
  • Floss
    Popcorn. Enough said.
  • Persol Sunglasses.
    My lady companion got me these for Christmas. They're so sexy, they've earned me the nickname "Hollywood" at work.
  • Ear Plugs
    Great for blocking out crying children on planes and extreme volume sometimes found at dance clubs and movie theatres. Yep, I wear these things shamelessly at dance clubs. People laugh but I have the last laugh when I leave without diminished hearing! ;)
  • San Francisco Popout Map
    A fellow San Franciscan suggested I buy this pocket sized map of the city.. it's initial size is small but then it pops out to show the entire city. It even has some bus lines listed.. totally worth the 7 bucks.

Oh, and I can't fit them in my backpack, but if I could, I'd also carry around my web hosting provider, Simpli.biz. I initially chose Simpli because of their 100% Uptime Guarantee and I've stayed with them because their service is outstanding, they have great communication skills (and even keep a blog) and they refuse to outsource. If you are looking for colocated and dedicated hosting in the Bay Area, I highly recommend Simpli.

Posted by: Chrissy   Filed under: General 8 Comments
11Jan/070

SUX: The War in Iraq

In Dwight D. Eisenhower's Farewell Address to the Nation, he pleaded with the American people to "avoid the impulse to live only for today, plundering, for our own ease and convenience, the precious resources of tomorrow." He also acknowledged the existence and influence of a militaryindustrial complex on governmental policy noting that "we annually spend on military security more than the net income of all United States corporations." Eisenhower then advised that "only an alert and knowledgeable citizenry can compel the proper meshing of the huge industrial and military machinery of defense with our peaceful methods and goals, so that security and liberty may prosper together."

Fast forward less than 50 years later and America, with the support of a majority of its citizens, has invaded an oil-rich country for no less than 27 different reasons, none valid. To date, at least 60,426 lives have been lost and over $400,000,000,000 has been spent, much of it now in the pockets of profiteering corporations such as Halliburton, Bechtel and Exxon. What I don't understand is how we live in a country where school teachers are being forced to buy their students school supplies yet we can spend $400,000,000,000 on invading, destroying and unsuccessfully attempting to rebuild a country that is now a haven for terrorists, something which they find a reason to celebrate.

Four-hundred billion can put 31,259,768 Americans through their entire 4-year undergrad degree, including room and board, at a public university. That's even with the 28.4% increase in the cost of attending college since 2002, a figure primarily caused by reduced government funding. That's nearly TWICE the number of all students, including Internationals, that were enrolled at American unviersities in the year 2000. If we're talking strictly tuition and fees, the number of Americans that could potentially go to college for free jumps to 68,540,095. We can now only imagine the prosperity and access to education that money could have given us.

As for the loss of American soldiers, the Marine Corps Times put up a map of the hometowns of each of the 3018 American soldiers who have died in Iraq to date.

The southern-most red dot that you see in Southwest Louisiana is my hometown of Kaplan. Kaplan lost Toby Mallet, a friendly Cajun who died on April 9th, 2004 when he was just 26 years old. My homestate of Louisiana has lost an average of more than 1.5 people per 100,000.

In expressing my frustration to a high school friend, she said "Well, there's nothing we can do now, can we?" I think that what we can do is educate others in an effort to prevent the type of warmongering that was seen and heard so frequently in America circa early-2003. While most of the content on this blog is technical, I feel it's still a platform to help me do just that. Our government reps, including mine, Speaker Nancy Pelosi, must be made aware that their intial support of the Iraq War will not be forgotten. Please contact your Representative and your Senators to let them know how you feel the 400 billion could have been better spent.

Posted by: Chrissy   Filed under: General No Comments
11Jan/072

SWEET: Meebo.com's SSL Site Encrypts Chats Too.

If you haven't been to meebo.com, you probably haven't needed to chat from a location that restricts chat program installs ;) Meebo.com is super slick; with nothing more than a browser, you can access your favorite chat network. The unencrypted meebo.com site does encrypt your password but stops there. By accessing Meebo.com via HTTPS, it appears that your entire session will be encrypted.

To test this, I killed all of my network connections, opened up IE (Firefox extensions make too many calls) and connected to the secure meebo.com. After chatting for some time, only the following two entries appeared in netstat -n.

Active Connections

  Proto  Local Address          Foreign Address        State
  TCP    xx.xxx.x.x:3336        65.19.140.10:443       ESTABLISHED
  TCP    xx.xxx.x.x:3337        65.19.140.10:443       ESTABLISHED

There are only connections to the HTTP SSL port, 443, and no connections to regular HTTP on port 80. Oh, and speaking of secure, here is a GreaseMonkey script that ensures that Gmail uses a secure connection. I think, however, that there must be some kind of hidden frame that encrypts all Gmail connections, even when you initally connect via HTTP and not HTTPS. It just doesn't seem right that Google would send all that info unencrypted. Let me test...

OK, I don't know if this is any true indicator, but it seems that Gmail actually does not encrypt in its AJAX calls when you hit the page in plain ol HTTP.

Active Connections

  Proto  Local Address          Foreign Address        State
  TCP    xx.xxx.x.x:3590        216.239.63.189:80      ESTABLISHED
  TCP    xx.xxx.x.x:3592        216.239.63.189:80      ESTABLISHED
  TCP    xx.xxx.x.x:3605        216.239.63.83:80       ESTABLISHED
  TCP    xx.xxx.x.x:3606        216.239.63.83:80       ESTABLISHED

That's nuts. If you don't use Firefox or if you don't want to use the GreaseMonkey extension, just make sure you check your Google mail by hitting https://mail.google.com/mail directly. Note: Hitting https://gmail.com will redirect you to an unsecured address.

Posted by: Chrissy   Filed under: Security 2 Comments
9Jan/0714

VBScript: Enumerate All SQL Servers on a Domain

So if you have SQL Server installed locally, you're lucky enough to have access to SQLDeeMO. You can then easily enumerate SQL Servers with the following code. Note: If you do decide to use VBScript and SQLDeeMO, you will need to remove the "ee" in the script below. I didn't want to put in the actual object name so that people searching Google for sites that do not contain the phrase SQLDeeMO will still find my site.

SQLDeeMO VBScript Action

Set objSQLDMOApp = CreateObject("SQLDeeMO.Application")
    Set objSQLList = objSQLDeeMOApp.ListAvailableSQLServers()
    For i = 1 To objSQLList.Count
       MsgBox objSQLList.Item(i)
    Next

    Set objSQLList = Nothing
    Set objSQLDeeMOApp = Nothing

But if you don't have SQL Server installed locally.. here's a hack that grabs the name of all Windows Servers in AD and then checks their registry for instances of SQL Server.

Enumerate All SQL Servers and Instance Names on a Domain

'****************************************************************************
' This script created by Chrissy LeMaire (clemaire@gmail.com)
' Modifications by Gregory Jones (greg@fuzsh.com) and Radsky
' Website: http://netnerds.net/
'
' This script finds all SQL Servers and their instances that are members of an AD domain
' and running any Windows Server version
'
' Run this script with admin privs on any computer within a domain
'
' This script has been tested on Windows Server 2003 and Server 2008.
' The newest script REQUIRES SQL Native Client to get the Version.
'
' "What it does"
' 1. Gathers all machines in a domain that are running a Windows Server OS (NT, 2000, 2003, 2008, etc)
' 2. Pings them to see if they are available
' 3. If they do respond to pings, it checks their registry to see if they have the proper SQL keys
' 4. If the key does exist, it then enumerates the instances (including default)
' 5. And then it goes get the version and architecture
'
' NO WARRANTIES, USE THIS AT YOUR OWN RISK, etc.
'*****************************************************************************
'on error resume next

Set objAdRootDSE = GetObject("LDAP://RootDSE")
Set objRS = CreateObject("adodb.recordset")
set objFS = CreateObject("Scripting.FileSystemObject")
Set objOutputText = objFS.CreateTextFile("sqlServers.txt")

Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2")
Const HKEY_LOCAL_MACHINE = &H80000002
varConfigNC = objAdRootDSE.Get("defaultNamingContext")
strConnstring = "Provider=ADsDSOObject"
strWQL = "SELECT * FROM 'LDAP://" & varConfigNC & "' WHERE objectCategory= 'Computer' and OperatingSystem = 'Windows*Server*' "

objRS.Open strWQL, strConnstring
Do until objRS.eof
Set objServer = GetObject(objRS.Fields.Item(0))
strServerName = objServer.CN
Set colItems = objWMIService.ExecQuery("Select * from Win32_PingStatus Where Address = '" & objServer.DNSHostName & "'")
For Each objItem in colItems
If objItem.StatusCode = 0 Then 'The Computer is Pingable
Set objRegistry = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strServerName & "\root\default:StdRegProv")
strKeyPath = "SOFTWARE\Microsoft\Microsoft SQL Server"
strValueName = "InstalledInstances"
objRegistry.GetMultiStringValue HKEY_LOCAL_MACHINE,strKeyPath, strValueName,arrValues
If IsNull(arrValues) = 0 Then 'It's a SQL Server! Enumerate it's instances
For Each strValue In arrValues
if lcase(strValue) <> "mssqlserver" then strServerName = strServerName & "\" & strValue
strMsg = strServerName & vbtab & GetSQLServerVersion(strServerName)
objOutputText.WriteLine strMsg
Next
End If
Set objRegistry = Nothing
End If
Set objServer = Nothing
Next
objRS.movenext
Loop
objRS.close
objOutputText.close

Set objOutputText = nothing
Set objWMIService = Nothing
Set objRS = Nothing
Set objAdRootDSE = Nothing

Msgbox "Done!"
'####
Function GetSQLServerVersion(serverName)
on error resume next
strConn = "Driver={SQL Server};Server=" & serverName & ";Database=master"
strsql = "SELECT @@version"

set rs = createobject("adodb.recordset")
rs.Open strsql, strConn, 1, 1

if err.Number <> 0 then
GetSQLServerVersion = "Port blocked (Likely Desktop / Express)"
err.Clear
else
if not rs.eof and not rs.bof then
strVersion = rs(0).Value
if inStr(strVersion,vbLf) > 0 Then strVersion = left(strVersion,inStr(strVersion,vbLf))
GetSQLServerVersion = strVersion
else
GetSQLServerVersion = "Unknown Version"
end If
end if
rs.close
set rs = nothing
End Function '//Function GetSQLServerVersion(serverName)

If you are looking for the version of each SQL Server.. you can either query it with SELECT @@VERSION or it may be easily found in the registry. It's 10:47pm and I'm still at work so I'm heading out ;)

Posted by: Chrissy   Filed under: Active Directory, VBScript 14 Comments
9Jan/0718

VBScript: Use an LDAP Query to Find All Windows Servers on a Domain

Damn, the ADsDSOObject rocks! This script, which weighs in at less than 20 lines, finds all machines running any form of Windows Server on a given domain. Note that this script isn't useful in finding domain controllers, but rather any machine running Windows Server.

Awesome

'****************************************************************************
' This script created by Chrissy LeMaire (clemaire@gmail.com)
' Website: http://netnerds.net/
'
' This script finds all machines running Windows Server (NT, 2000, 2003) in AD
'
'Msgbox output provides server name and OS version.
'
' NO WARRANTIES, USE THIS AT YOUR OWN RISK, etc.
'*****************************************************************************

Set objAdRootDSE = GetObject("LDAP://RootDSE")
Set objRS = CreateObject("adodb.recordset")

  varConfigNC = objAdRootDSE.Get("defaultNamingContext")
  strConnstring = "Provider=ADsDSOObject"
  strWQL = "SELECT * FROM 'LDAP://" & varConfigNC & "' WHERE objectCategory= 'Computer' and OperatingSystem = 'Windows*Server*'"
  objRS.Open strWQL, strConnstring
    Do until objRS.eof
   Set objServer = GetObject(objRS.Fields.Item(0))
strServerName = objServer.CN
strOperatingSystem = objServer.OperatingSystem
MsgBox strServerName & " is running " & strOperatingSystem
objRS.movenext
   Set objServer = Nothing
    Loop
  objRS.close

Set objRS = Nothing
Set objAdRootDSE = Nothing

Also, I found this nice reference of Command One Liners while searching the web. Totally handy!

Posted by: Chrissy   Filed under: Active Directory, Networking, VBScript 18 Comments
9Jan/072

CHM: The Best E-Book Ever for Windows/SQL/Exchange/IIS Admins Now Half Off!

In one of my earlier posts "The Software I Use Daily," I wrote

The Penton Press Master CD is a must have for any Windows administrator. It is a CHM file which contains every article from Windows IT Pro, SQL Server Magazine, Exchange & Outlook Adminisrator, Windows Scripting Solutions, Windows IT Security and Windows Web Solutions dating back as far back as September 1995. As of December 2005, the file was a massive 404MB. That’s a ton of data but the chm format makes it easily searchable.

From now until January 31st, that CD (Sept 1995-Dec 2006) is priced at $29.95 instead of $59.95. Click here for the promotion.

Posted by: Chrissy   Filed under: General 2 Comments
9Jan/072

VBScript: Find All Exchange Servers in Active Directory

My friend Sharfa and I were exchanging some of our favorite code snippets and he showed me one for enumerating Exchange Servers in Active Directory. I dug the code but wanted to try to see if I could use my Recordset/ADsDSOObject skrills to shorten the code. The outcome isn't any shorter but it does get the version, so that's cool. Thanks, Sharfa, for pointing me towards the WMI Exchange_Server thing, too. :-D

Hit It

'****************************************************************************
' This script created by Chrissy LeMaire (clemaire@gmail.com)
' Website: http://netnerds.net/
'
' This script finds all Exchange Servers in AD. Includes Exchange Version.
'
' Run this script with admin privs on any computer within a domain.
'
' This script has only been tested on Windows Server 2003
'
' NO WARRANTIES, USE THIS AT YOUR OWN RISK, etc.
'*****************************************************************************

Set objAdRootDSE = GetObject("LDAP://RootDSE")
Set objRS = CreateObject("adodb.recordset")

varConfigNC = objAdRootDSE.Get("configurationNamingContext")

  strConnstring = "Provider=ADsDSOObject"
  strSQL = "SELECT * FROM 'LDAP://" & varConfigNC & "' WHERE objectCategory='msExchExchangeServer'"
  objRS.Open strSQL, strConnstring
    Do until objRS.eof
Set objServer = GetObject(objRS.Fields.Item(0))
   Call getExchangeInfo(objServer.CN)
Set objServer = Nothing
     objRS.movenext
    Loop
  objRS.close

Set objRS = Nothing
Set objAdRootDSE = Nothing

Sub getExchangeInfo(strServerName)
Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!" & strServerName & "\\ROOT\MicrosoftExchangeV2")
Set colItems = objWMIService.ExecQuery("Select * from Exchange_Server")

For Each objItem in colItems
MsgBox UCase(objItem.Name) & " (" & objItem.FQDN & ") is running Exchange " & objItem.ExchangeVersion
Next

Set colItems = Nothing
Set objWMIService = Nothing
End Sub
Posted by: Chrissy   Filed under: Active Directory, Exchange, VBScript 2 Comments
9Jan/0711

VBScript: Windows XP/IIS 5.1 DOES Support Denying Access by IP Addresses

In helping a visitor to troubleshoot running my IIS FTP ban script, I realized that while XP makes it appear as though it doesn't support banning users by IP address, it actually does provide that support; you just have to ban the IPs programatically.

Here, you can see that the IP address and domain name restrictions section is greyed out. However, you can use the following VBScript to enable and ban users in IIS' Default Web SIte. The first script listed does the following:

1. Ensures that AllowByDefault is set to true (which is the default anyway)
2. Bans a few example IP addresses
3. Confirms the addresses were successfully banned

Ban-a-rama

strComputer = "localhost"
arrBanTheseIPs = Array("10.0.0.200","42.42.42.42")

'Set Objects
Set objWebSite = GetObject("IIS://" & strComputer & "/W3SVC/1")
Set objIPRestrict = objWebSite.IPSecurity

objIPRestrict.GrantByDefault = True
objIPRestrict.IPDeny = arrBanTheseIPs
objWebSite.IPSecurity = objIPRestrict
objWebSite.SetInfo

WScript.Echo "The following IP addresses are now banned:"
arrDeniedIPs = objIPRestrict.IPDeny
for i = 0 to Ubound(arrDeniedIPs)
  WScript.Echo arrDeniedIPs(i)
next

'Kill Objects
Set objIPRestrict = Nothing
Set objWebSite = Nothing

To Delete All Previously Banned IPs, you would use the following code which overwrites all the IPs with one invalid IP.

Mass Unban

strComputer = "localhost"

'Set Objects
Set objWebSite = GetObject("IIS://" & strComputer & "/W3SVC/1")
Set objIPRestrict = objWebSite.IPSecurity

objIPRestrict.GrantByDefault = True
objIPRestrict.IPDeny = Array("0.0.0.0")
objWebSite.IPSecurity = objIPRestrict
objWebSite.SetInfo

'Kill Objects
Set objIPRestrict = Nothing
Set objWebSite = Nothing

If you find yourself needing to unban a single IP address, you can use the following code which gathers all the banned IPs except the one you want to delete and rebans them (IPDeny requires a full list each time you set it).

Unban One IP

strComputer = "localhost"

'Set Objects
Set objWebSite = GetObject("IIS://" & strComputer & "/W3SVC/1")
Set objIPRestrict = objWebSite.IPSecurity

strUnbanSingleIP = "10.0.0.200"
arrIPAddresses = objIPRestrict.IPDeny

For i = 0 to ubound(arrIPAddresses)
strClientIP = Left(arrIPAddresses(i),InStr(arrIPAddresses(i),",")-1)
  If strClientIP <> strUnbanSingleIP Then
   If Len(strStillBanned) = 0 Then
   strStillBanned = strClientIP
   Else
   strStillBanned = strStillBanned & "," & strClientIP
End If
  End If
Next

If Len(strStillBanned) = 0 Then strStillBanned = "0.0.0.0" 'just in case it was the only one
arrStillBannedIPs = split(strStillBanned,",")

objIPRestrict.IPDeny = arrStillBannedIPs
objWebSite.IPSecurity = objIPRestrict
objWebSite.SetInfo

'Kill Objects
Set objIPRestrict = Nothing
Set objWebSite = Nothing

If your script is successful, banned users will see the following message:

You are not authorized to view this page

HTTP 403.6 - Forbidden: IP address rejected

To show all of the current IPs which have been banned, run the following script

View Banned IPs

strComputer = "localhost"

'Set Objects
Set objWebSite = GetObject("IIS://" & strComputer & "/W3SVC/1")
Set objIPRestrict = objWebSite.IPSecurity

arrDeny = objWebSite.Get("IPSecurity").IPDeny
   For i = 0 to Ubound(arrDeny)
      strBannedIPs = strBannedIPs & arrDeny(i) & vbCrlf
   Next

   If len(strBannedIPs) > 0 Then
      msgbox "IP, Subnet: " & vbCrLF & strBannedIPs
   Else
      msgbox "No IPs have been banned."
   End if

'Kill Objects
Set objIPRestrict = Nothing
Set objWebSite = Nothing

While I haven't tested it, the same scripts should work if you want to deny all IPs except those explicitly listed. To do so, simply set objIPRestrict.GrantByDefault to False and replace the above mentions of IPDeny with IPGrant. Same goes for MSFTPSVC -- if you want to modify the FTP service settings, just change the above instances of "W3SVC" to "MSFTPSVC".

Posted by: Chrissy   Filed under: IIS, Security, VBScript 11 Comments