## Join The Fight Against Internet Threats

I’m very pleased to post that as of May 2013, SysadminSpot.com is taking the fight against Internet threats even further. We’re highly active participants in Project Honeypot and now we’ve joined forces to help OpenBL.org out.

# But why OpenBL.org?

The Internet has an unfortunate infestation of individuals and organisations who abuse it for illegal gains. They abuse networks, compromise applications and breach server security. Attacks can result in spam, viruses, phishing attacks, identity theft, data loss, financial fraud and more.

OpenBL.org operates a network of honeypot servers, strategically located in many countries around the world. Like all honeypots, the servers do nothing but wait patiently to be probed and attacked. When an attack is detected, all information associated with the attack is logged and reported to the network owner. Furthermore, (the part I love) they name and shame offenders on the OpenBL.org Blacklist. Myself and many other fellow sysadmins use this info to help protect our own networks and servers from attack.

# So that’s a lot of data…

Here’s what I love about honeypot blacklisting: it’s next to impossible to be mistakenly blacklisted – the only way to be listed is to engage in malicious activity against an elusive honeypot server.

These attacks are reported to network and server owners enabling them to respond and stop the attack. In many cases I’ve seen server owners be the victims as their server was compromised and turned into a zombie in a much larger attack. Reporting it to them gets a faster turn around in resolving the compromise and stopping the attack.

OpenBL.org also make available publically the offending IP addresses in their blacklists. These results can be used to proactively block dangerous IP addresses in firewalls, for signatures in intrusion detection systems and anti-spam measures.

An example of it’s use – I prepared a script only 24 hours ago (I’ll post a variant of it later) to proactively add offenders from the last 90 days to one of my server firewalls. Since going live with the script, going to bed, getting up, working all day, coming home, evening meal, etc – the firewall has logged and recorded 31 dropped attempts from these known bad IP addresses. I’m protecting my server, I’m saving bandwidth and encouraging these attackers to simply go away.

This data is invaluable to me and my clients in mitigating against known attackers; and OpenBL.org do all this for free.

# How can I help?

In April 2013, there were 3,400 recorded attacks on the OpenBL.org honeypot servers. Governments and corporations around the world are becoming more and more fearful of cyber-terrorism. This isn’t just something on television – it’s reality.

Get this, it is so incredibly easy to help out.

## Donate a virtual server

Virtualisation makes servers dirt cheap these days. Simply sign up for a cheap VPS server through your preferred provider or find a cheap one on Low End Box.

Drop them an email and find out their preference for operating systems, etc however I’ve found really low specs such as 1 CPU Core (shared is cheaper), 128MB memory, 5GB disk space and 1 IPv4 address are perfect. The more IP addresses on different subnets the better!

I simply paid for my donated servers for a year and sent them the credentials. I must admit it’s fun watching the stats and seeing my servers catching bad guys!

## Throw them some cash

This extensive network needs upgrades and TLC. They’re eager to grow and improve services.

## Send them a nice email

Tell them how awesome they are. Because everyone enjoys a complimentary email!

SysadminSpot.com is extremely proud to be helping the greater community fight back. Why don’t you join us?

UPDATE: 9 days later.

VN:F [1.9.22_1171]
VN:F [1.9.22_1171]

## What process is locking this file?

I recently had a problem with Microsoft Lync locking my Outlook data file despite exiting Lync. It happened constantly on reboot and locked up my email. It would also lock up the Mail control panel window and I couldn’t rename or move it for debugging purposes.

I used a Windows tool to find it which did exactly what it advertised. It was easy and straightforward to use. It was perfect.

I wanted to share it to hopefully save someone else some time.

# http://lockhunter.com/

• It has both 32 and 64bit versions
• The installer is 1.5MB
• It’s very light weight and doesn’t do anything more than you need it to do
• The GUI is simply and straightforward
• Simply right mouse click on the file, click What is locking this file? and there it is.

It does require administrator privileges to work though. But I wouldn’t hesitate to use this on a server if I needed to.

Check out their website. I recommend it. *Adds to bookmarks*

VN:F [1.9.22_1171]
VN:F [1.9.22_1171]

# How to Restore Microsoft SQL Azure Backup to Microsoft SQL Server

I’ve been exploring Microsoft Azure more and more recently. One of the most common pains I’ve seen though is getting an SQL Azure backup into to a local development environment for troubleshooting.

Of course, SQL Azure database backups are not straightforward .bak files, no no, they’re .bacpac files. What is a bacpac? Good question, I had to check myself:

“The SQL Database Import/Export Service creates a logical backup (BACPAC) file containing the schema definition and table data of a database in Windows Azure SQL Database. You can then recreate the database on another Windows Azure SQL Database server or an on-premise instance of the SQL Server Database Engine.” - http://msdn.microsoft.com/en-us/library/windowsazure/hh335292.aspx

A significant portion of my searching has turned up mounds of results about moving SQL Server Databases to SQL Azure, but not the other way around. There were some subtle hints of it in the Microsoft documentation but it wasn’t obviously.

This blog post is intended to make it obvious. I found two methods of restoring a .bacpac file to Microsoft SQL Server Database Engine.

1. Via the Microsoft SQL Management Tools
2. Via command line.

I know Microsoft SQL Server 2012 is out and about, and that’s really nice. But the majority of the business world is still invested in Microsoft SQL Server 2008 R2 or earlier. I also want to be clear that the below instructions were conducted on:

Server Version: Microsoft SQL Server 2008 R2 SP2 Standard
Management Tools Version: Microsoft SQL 2012 Management Tools

I suspect the server version will be portable at a guess to 2008, maybe 2005 – I’ve not tested it.

A common problem with restoring MSSQL Server .bak files is you cannot restore them to earlier versions of MS SQL Server (e.g. 2008 R2 to 2008, or 2008 to 2005). However I’m so far impressed at the ability to be able to restore the .bacpac files to MSSQL Server 2008 R2. Please, test it out on earlier versions and let me know in the comments how you go.

## Backup your SQL Azure Database

This process is already very well documented so I’m not going to rewrite it. Check these out:

http://msdn.microsoft.com/en-us/library/windowsazure/jj650016.aspx

http://msdn.microsoft.com/en-us/library/windowsazure/ff951631.aspx

I personally found it easy and straightforward preferable to use the Azure Management Portal.

I generally Export the .bacpac to blob storage and download it to the dev environment from there. I’m sure it’s not the best method but it’s one.

I use and recommend CloudBerry Explorer Free for Azure. It allows you to connect to Azure Blob storage and transfer files to and from. The product developer CloudBerry Lab has some other good products worth checking out too.

## Restoring BACPAC with Microsoft SQL Management Tools

As I mentioned, there’s a small catch – you need the Microsoft SQL Management Tools from SQL 2012. You simple need a release version (not the release candidate) of the Management Tools, even the Express edition works fine.

ENU\x64\SQLManagementStudio_x64_ENU.exe
ENU\x86\SQLManagementStudio_x86_ENU.exe

Once again, to be clear, the server I’m restoring to is Microsoft SQL Server 2008 R2 SP2 Standard but the Management Tools are 2012. You can have both the 2008 R2 and 2012 Management Tools installed side-by-side.

I’ve not tested this on other SQL Server versions, I’m sure it will work though. Please test and let me know in the comments.

Confirmation of the Management Tools version I’m using:

Connect to your MSSQL Server. Ensure you’re using an appropriately permissioned account – I use an account with sysadmin role.

Right mouse click on Databases and click Import Data-tier Application. Yes, I know, it looks unusual. Just roll with it.

Alternatively, you can import from Windows Azure right from the wizard however it is incredibly slow depending on your location.

Give the database you’re restoring a name and define a location for the data and log files. The default location was wrong for me, I had to define the paths below where I normally store my respective files.

Review the summary and ensure all your details are correct.

Sit back and let it import. The BACPAC I used in this example was a about 25mb and it took a few minutes.

All done! The Import Data-tier Application wizard is now complete. Click close and verify your restore worked.

And there it is! Now you can start your troubleshooting.

## Restoring BACPAC via Command Line

Thank you to the author (Flea) of the post on Stackoverflow who recommended this process.

You’ll need at least the SQL Server Data Tools – December 2012 update. You can download the “installer” which downloads all the other files for you. I recommend though you download the ISO image, select a Visual Studio and you’ll find the links at the bottom:

http://msdn.microsoft.com/en-us/data/hh297027

Note: Don’t let the words Visual Studio throw you off. As a test, I spun up a fresh Windows 8 VM and installed only the above “Microsoft SQL Server Data Tools 2010 update” and found I could restore the database perfectly. I went with this version in my testing.

Also remember in Windows 8, you can mount ISOs simply by double clicking on them.

Once you’ve installed the update:

Open command prompt and type the following:

cd C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin

SqlPackage.exe /a:Import /sf:D:\path\to\file.bacpac /tdn:Destination-DB-Name /TargetServerName:(local)

This will restore the BACPAC (D:\path\to\file.bacpac) to database Destination-DB-Name on server (local).

This may not suit your needs so check out the parameter options for SqlPackage.exe.

I hope this helps people out.

VN:F [1.9.22_1171]
VN:F [1.9.22_1171]

# The Problem

One of the test environments I help maintain is subject to dynamic and regular changes in .NET applications. The development team are constantly releasing new builds that are slightly different.

You may not be aware that .NET applications go through a compilation process when they first start up. I’ve also been told on application pool recycle however I haven’t confirmed this. However, after the application has been removed or updated, the compilation temporary files remain. On a test environment similar to my above scenario, a total of 50GB of disk space can be easily wasted, doing nothing. So if you’re in a similar scenario, you may need to routinely clean up these files.

I know of four locations where these files can build up:

• C:\Windows\Microsoft.NET\Framework\v1.1.4322\Temporary ASP.NET Files
• C:\Windows\Microsoft.NET\Framework64\v1.1.4322\Temporary ASP.NET Files
• C:\Windows\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files
• C:\Windows\Microsoft.NET\Framework64\v2.0.50727\Temporary ASP.NET Files
• C:\Windows\Microsoft.NET\Framework\v4.0.30319\Temporary ASP.NET Files
• C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files

If you application pools run in 64bit mode, you’ll find the “Framework64″ locations more applicable. If your application pools use 32bit mode, you’ll need to consider the “Framework” locations.

Below is an example of a production environment using nearly 13GB:

# Powershell Script to Automatically Delete Temp Files

As system administrators, we need to automate and script our workload so we can focus on the important stuff! So we devised a simple implementation of Powershell script which takes care of this clean up problem in one line:

Get-ChildItem “C:\Windows\Microsoft.NET\Framework*\v*\Temporary ASP.NET Files” -Recurse | Remove-Item -Recurse

If you’re reading this and thinking, EWWWW Powershell. Don’t mock it until you try this single line command.

I saved the above command into a file names CleanUpASPNETTempFiles.ps1 and saved it to D:\Tools\. Name and save yours as you see fit.

To take a load off, you can schedule this script with a scheduled task. You can click on these screenshots if you need more detail:

(I opted to run on the first Saturday of the month. Use your own preferences.)

(My powershell.exe was at %SystemRoot%\System32\WindowsPowerShell\v1.0\powershell.exe)

(Note you need to tick the option box to get to the next screenshot or load the properties on the newly created task.)

# Execution Policy Errors

If you’re being told to sign your newly created script, you can either do that for high security environments or you can turn off the signing requirement. It’s great to have this option build into the language but in the typical environment I’ve found it a burden.

In a Powershell command window, type the following to check your current ExecutionPolicy:

Get-ExecutionPolicy

You can change it to Unrestricted by typing:

Set-ExecutionPolicy Unrestricted

# A Few Notes

A reminder that files in these locations are normal. Don’t go over the top trying to clean them up.

It should be noted that while the files are in use by the web server, you will not be able to delete them – this is fine; the goal is to clean up unused files.

You should also be aware the next time the application fires up, on app pool start, the application will re-compile again. This may lead to a longer than average initial page load. If this is of concern, consider only removing files that are older than 30 days.

Of course, this script is provided as is. You should test it thoroughly in a test environment before use in production (though I certainly do).

VN:F [1.9.22_1171]
VN:F [1.9.22_1171]

## ISAPI Filter ‘C:\Windows\Microsoft.NET\Framework\v4.0.30319\\aspnet_filter.dll’ could not be loaded due to a configuration problem

Problem

After monthly server patching, I also made some security enhancements to the web.config files in the C:\Windows\Microsoft.NET\FrameworkXX\vXXX\CONFIG\ directories. It was therefore important that I test both 32bit and 64bit application pool modes. I found that tests running 64bit mode were suddenly failing with the following error message:

It was of no surprise what so ever that the link included in the error message was 100% useless (thanks again Microsoft). One article I found suggested I uninstall .NET 4 – that’s like turning a monitor off to fix a dead pixel.

Solution

Upon closer inspection of the error message I noticed the problem:

v4.0.30319\\aspnet_filter.dll

Windows doesn’t handle double slashes very well as opposed to Linux. What is strange is none of these settings are manually changed which suggests it was perhaps a Windows update that caused this, however I have no evidence beyond change control logs.

I fixed with the following process:

Opened IIS Manager and selected the IIS web server node:

In the middle pane, browse to the IIS section and select ISAPI Filters:

Expand the columns as required, looking for an Executable path with \\ in it, then click Edit:

From the above screenshot, change the \\ to \ so the path reads:

C:\Windows\Microsoft.NET\Framework\v4.0.30319\aspnet_filter.dll

Just keep in mind that the folders Framework and Framework64 or the .NET version may change depending on your implementation.

After clicking OK, all my testing started to work again and the 500 Internal Server errors went away.

Conclusion

A possible Microsoft update (exact cause unknown) made an error updating an existing or installing a new ISAPI handler to IIS. By mishandling the path, it inserted \\ instead of \ into the executable path causing IIS to be unable to load the filter.

This caused 500 Internal Server errors to sites/applications running application pools in 64bit mode.

Hopefully this helps someone out. If it does, leave me some feedback! Thanks all!

VN:F [1.9.22_1171]