22 February 2014

Blocking RDP brute-force logon attacks

One thing that has annoyed me for some time is RDP brute force attacks on my servers. If you have a physical or virtual Windows server hosted in a datacenter or in the cloud, you most likely use RDP (Remote Desktop) to access it. Script kids trying to gain access to systems of course like to attempt to do so via RDP, and they use automated tools they found on some "hacker" site to try to brute-force servers with open RDP listeners.

If you have a secure combination of username and password to log in to your server (or VM), brute-force attacks are unlikely to succeed. If you use a bad/insecure password, you will be hacked in a whiff. If your password is even on the top 10k list of reused passwords, you're likely to be hacked sooner or later.

I believe my choice of username and password is secure enough to survive RDP brute force attacks for the time being, but I still find it annoying that these kids are trying to break into my server so I wanted to have a way to block them.

Signs of a RDP brute-force attack

Whenever a server is under attack, it will log large amounts of Audit Failure events with event id 4625 in the Security event log. Each entry contain details about the login attempt, including the remote IP address of the attacker. It looks something like this on a Windows 2008 R2 server:

Blocking attackers

Since the event log entries contain the attacker's IP address, they can of course be blocked by adding them to a firewall rule. Naturally, noone would want to do that by hand, as it would be time consuming and most likely too late (the attack has either failed and the attacker moved on, or been successful and the server compromised).

Instead, I updated and repurposed a service I wrote back in the days of the ASP.NET POET vulnerability. The service simply monitors the event log for a pre-defined type of events (in this case the above mentioned Audit Failures), and if it detects too many originating from the same source IP within an hour it adds the originating IP to a firewall block rule, and optionally sends an email alert to the server admin.

In short, the service creates an event log listener and will listen for failed login attempt event log entries. If the same IP address has more than a configurable (default 10) number of failed logins within an hour, it is added to a firewall block rule in the Windows firewall. Warning: this will of course also block legitimate users if they have forgotten their username and/or password and try to log in repeatedly with invalid credentials.

The service also looks for signs of a distributed attack; if failed logins from multiple IP addresses exceed a pre-defined threshold (default 50) within an hour, it sends an email notification to the server admin.


I have made a beta version of the RDP blocker service available for download for anyone brave enough to test it. Binaries for the compiled version is available here: HuagatiRDPBruteForceBlockerService_release_v112.zip, and the source code is available here: HuagatiRDPBruteForceBlockerService_source_v112.zip

Disclaimer: Use at your own risk. If used incorrectly, or if there is a bug or flaw I didn't think of, this service can potentially lock you out of your server permanently and/or prevent legitimate users from accessing it.  Do not use on vital/important/production systems. Any use (or misuse) is your own responsibility. No support provided. Batteries not included. Don't use this service if you don't know what you are doing, and even if you do it has not yet been tested enough to be deemed safe. I have only tested this service on a Windows 2008 R2 system with US-English Windows. Using it on other versions of Windows and/or other localized variants may have unintended consequences. Depending on network configuration and/or firewalls in front of your server, *all* external traffic may appear to come from the same (internal NATed) IP address to your server. If this is the case, the service will block all traffic to your server.

Installing and configuring the service

The following steps are required to install this service:

  1. Download the service onto the server where you are going to install it.
  2. Right-click on the zip file, go to Properties, click on the "Unblock" button.
  3. Unzip the contents into a folder where you want to install the service.
  4. Run installutil /i HuagatiRDPBruteForceBlockerService.exe to register the service in the Windows service configuration. The installer will prompt for the username and password that the service will run under, this account must have administrative privileges to be able to create firewall blocking rules.

    Installutil is part of the .net framework and can usually be found under C:\windows\Microsoft.NET\Framework\v4.0.30319.
  5. Edit HuagatiRDPBruteForceBlockerService.exe.config using your favorite text editor. Update all relevant configuration options* to match your system/environment.
  6. Start the service from the windows service manager.

* = The configuration file contain several configurable parts. The following describes the important ones that you need to, or may want to update:

SMTP settings

configuration/system.net/mailSettings - this section contains the details needed by the service to send email via your SMTP server. You need to update (at a minimum) the host name, user name, and password used to authenticate against your SMTP server. Additional details about available configuration options are available here.

Service app settings

configuration/applicationSettings/HuagatiRDPBruteForceBlockerService.Properties.Settings - this section contains the settings that controls the configurable parts of the service's behavior.

The following describes each of those settings:
  • MaxFailedLoginsPerIP - numeric value, number of failed login attempts allowed for one individual IP address within an hour.
  • UseFirewallBlock - boolean (True/False), controls if firewall blocking is enabled. If you want to run the service in "warnings only" mode where it sends email alerts without blocking potential attackers, set this to false.
  • SendEmailNotification - boolean (True/False), controls if email notifications are enabled. If you do not want the service to send email alerts/notifications when detecting potential brute-force attacks, change this settings to false.
  • EmailNotificationFrom - email address that will appear as the sender for all notification emails sent from the service.
  • EmailNotificationTo - email address that any email notifications will be sent to.
  • WhiteList - comma separated list of IP addresses that the service will not attempt to block/blacklist. Add your own static IPs to this list to avoid getting blocked if you mistype your password.
  • EventLogName - the name of the security event log on the local system.
  • EventLogQuery - event log listener query used for finding failed login attempts in the Security event log.
  • IPMatchRegEx - regex used to find the IP address of the attacker in the event log entry.
  • DistributedAttackWarningThreshold - threshold for number of failed login attempts allowed from multiple IP addresses within an hour before an alert is sent for a potential distributed attack.

Again, feel free to grab the blocker service from the download links, but use at your own risk. If you have any comments/feedback/questions, please feel free to post in the comments section below.

19 May 2011

Entity Framework Model Creation beyond ‘Database First’ / ‘Model First’

The built in tools for Entity Framework 4 in Visual Studio forces users to make a distinct choice between the “Database First”, “Model First”, or “Code First” model creation and update strategies. A recent MSDN Magazine article by Julie Lerman describes the options and choices that has to be made on which approach to use for updating the model.

I think those three choices are a bit limiting in that not being able to continually evolve both the model and the underlying database is not an ideal situation for many projects. Applications evolve and so do the underlying databases, often with changes made by several people using different tools. That is the reason why I created the Model Comparer for EF4 in the first place.

Even if the model is based on an existing database, a.k.a. ‘Database First’, I think it should be possible to use the EF designer built into Visual Studio to update the model and add/remove/change entities/tables, members/columns, associations/foreign keys etc.

Likewise, if the model is designed from scratch in the EF designer (‘Model First’), it should be possible to make incremental changes with SQL-DDL diff scripts, as well as making schema changes directly in the database and sync those changes back to the model.

I made a short (4½ minute long) screencast illustrating how this is possible to do with the EFv4 visual designer together with the model comparer. In this video I make changes both to the database in SQL Server Management Studio and to the model in Visual Studio and then sync it up to replicate the changes into both the database and the model.


The video above is also available on youtube at http://www.youtube.com/watch?v=doqYOlcEAZM , although the youtube version is lower resolution and has more MPEG compression artifacts than the one embedded above.

Oh, and if you want to try it out yourself, you can download the add-in that makes this possible from http://huagati.com/dbmltools/ and get a free trial license from the same site.

18 January 2011

Redistribution licenses for the HuagatiEDMXTools.dll EDMX file object model

In an earlier blog post, Creating or modifying Entity Framework EDMX files from code: an introduction to HuagatiEDMXTools.dll, I wrote about the EDMX file wrapper that is used by the Huagati DBML/EDMX Tools add-in for parsing, updating, creating, and writing EDMX files. HuagatiEDMXTools adds an easy to use object model on top of EDMX files (see documentation at http://huagati.com/edmxtools/help ), making it a lot easier to create/read/update EDMX files, and to write LINQ queries against Entity Framework 4 metadata. It also has a number of built-in queries and lookup functions that make it a breeze to work with Entity Framework 4 EDMX files from code.

Previously, using the HuagatiEDMXTools runtime library required an end-user license for Huagati DBML/EDMX Tools. As of the latest version (v 2.20) this has been extended with a new redistributable license option, allowing third party applications to use and include HuagatiEDMXTools.

Redistribution licenses are divided into three categories:

  • Non-development tool license: allows redistribution of HuagatiEDMXTools.dll together with software products not primarily targeted at software developers and whose primary purpose is not to develop software. This may be line-of-business applications that need to generate, update, or read EDMX data to fulfill some functional requirement.
  • Development tool license: allows redistribution of HuagatiEDMXTools.dll together with software products whose primary purpose is to assist software development. This may be a tool specifically for working with Entity Framework 4 EDMX files, such as a new model editor or EF model designer.
  • Source code license: Source code licenses includes the source code for the HuagatiEDMXTools runtime library. The source code can not be redistributed, and any libraries/assemblies based on or derived from it may only be distributed as part of another software product that is not substantially similar to or competitive with HuagatiEDMXTools. (It may however be used for products competitive with the Huagati DBML/EDMX Tools add-in.)

Pricing and additional details are available at http://huagati.com/dbmltools/

28 December 2010

Inferring Foreign Key Constraints in Entity Framework Models (Part 2)

In my last blog entry, Inferring Foreign Key Constraints in Entity Framework Models, I wrote about a new feature in the Model Comparer for EFv4 that allow foreign key constraints to be inferred and added to an Entity Framework model even if some or all FKs are missing in the database. The inferred FKs can then be used to generate associations and navigation properties in Entity Framework models.

In the latest release of Huagati DBML/EDMX Tools, the FK inference feature has been improved with a new regex match-and-replace feature for matching foreign key columns to primary key columns. This allows FKs to be inferred even if the FK/PK columns to be matched use inconsistent naming and/or are named with special prefixes/suffixes etc.

Of course, the basic name matching feature described in the previous article is still there, and can be used under many (if not most) common scenarios. However, when the PK/FK naming is more complex then using Regex match+replace comes in handy for resolving what columns in one table can be mapped to the PK in another table.

The following screenshot shows the settings dialog with the new settings for RegExp-based column matching:


In addition to allowing the standard regular expression language elements to be used in the Match field, and the substitution elements to be used in the Replace field, there are two reserved keywords that can be used in the replacements as well; %pktable% which is replaced with the name of the primary-key-side table and %fktable% which is replaced with the name of the foreign key table when the match is evaluated.

The above screenshot shows how regular expression replacements plus the %pktable% substitution keyword can be used to match two tables even if the PK column is named “id” and the FK column in another table is named using the PK table’s name followed by “_key”; if the result of the Match+Replace on both sides evaluate to the same value, a foreign key constraint is inferred and will be displayed in the diff-tree and diff-reports in the Model Comparer’s main window.

The Model Comparer

If you want to read more about the Model Comparer and the other features for Entity Framework contained in the Huagati DBML/EDMX Tools add-in for Visual Studio, see the following links:

Introducing the Model Comparer for Entity Framework v4
What’s new in the latest version of the Model Comparer for Entity Framework 4
Using the Model Comparer to generate difference reports for Entity Framework v4 models
Mixing inheritance strategies in Entity Framework models
SQL Azure support in Huagati DBML/EDMX Tools
Inferring Foreign Key Constraints in Entity Framework Models
Simplify Entity Framework v4 models with complex types

…and of course, there is a brief summary of the add-in’s supported features for both Entity Framework and Linq-to-SQL at the product page: http://huagati.com/dbmltools/ where you can also download the add-in and get a free 30-day trial license.

21 December 2010

Inferring Foreign Key Constraints in Entity Framework Models

Foreign key constraints are one of the most important components of relational database models. At the database level they are key to maintaining data integrity and prevents applications and users from writing invalid data or from orphaning data by inadvertently deleting data referenced from other tables.

When reverse-engineering entity relationships in modelling tools and OR mappers, foreign key constraints serve yet another important role; they also describe the relationship between tables/entities. Without foreign key constraints, OR mappers can’t generate navigation properties between entities, and will not know in what order* it should process inserts, updates, and deletes for various tables.

* = e.g. generating order detail records before the main order record has been created.

Databases with no FK constraints

Unfortunately, there are lots of real-world databases that - although they are created and deployed in RDBMSes – lack foreign key constraints. The reasons for omitting FK constraints vary, but it is not uncommon that the reasons stated are based on misconceptions, misunderstandings, or lack of information. Sometimes they are omitted by programmers that can’t get their head around proper order of inserts/deletes, sometimes they are omitted “for performance reasons”, sometimes noone can remember anymore why they were omitted. Whatever the reason, relational databases with some or all FK constraints missing are out there and they’re fairly common.

Adding FK constraints to legacy databases

One way to tackle FK-less databases is simply to add the missing FK constraints. Simple solution to data integrity issues, and makes OR mappes happy, right? Unfortunately it is not that simple. The real problem with FK-less databases is the applications behind them. If there are tens-of-thousands, hundreds-of-thousands, or millions of lines of code that interact with those databases you can be certain that some of it will break if you just add all the missing FK constraints. There’s almost certainly code that do inserts/deletes in the wrong order, and there’s a fairly good chance that there is junk data or orphaned data that will break the FK constraint already during creation, and so on.

Inferring FK constraints

Since FK constraints are key to generating the associations in OR mappers such as Entity Framework, I decided to add a feature to infer FKs. Instead of creating them in the database, I use table and column names and types to deduce what might be a FK candidate in the database. After that, the inferred FKs are displayed in the Model Comparer’s list of missing FK constraints.

The user can choose and pick both real and inferred foreign key constraints and add them to the model selectively, and generate model-level associations and navigation properties without actually creating the FKs in the database.

The tool also allows inferred FK constraints to be materialized into SQL-DDL scripts, but that part is entirely optional.


The following set of screenshots demonstrate how the FK inference feature in the Model Comparer for EFv4 can be used to infer FKs in a database and to add selected keys to the EF4 model.

Step 1 – open a EFv4 model in Visual Studio 2010, and bring up the Model Comparer:

The Huagati Model Comparer for Entity Framework 4

Step 2 – Click on Settings to open the settings dialog. Go to the Infer Foreign Keys tab.

Select the type of objects you want to infer FKs for; tables and/or views. Although views can not have FK constraints in the database it is perfectly fine to infer FKs and use them to add associations to views. This makes it possible to have navigation properties to/from views.

There are four different name matching options controlled by two checkboxes: whether PK members contain the name of the entity or not, whether entity names and member names are separated with underscores or not. Changing the checkboxes updates the table example in the middle of the dialog between the four different naming conventions, to make it easy to verify that the settings match the desired naming conventions.

Model Comparer settings for inferring keys


Step 3 – with the Infer Foreign Key Constraints for Tables setting and/or the Infer Foreign Key Constraints for Views setting enabled, the model comparer will infer FK constraints where they don’t exist in the database. Inferred FK constraints start with the FKI prefix and has a different icon than existing FK constraints to make it easy to differentiate between inferred and real FK constraints.

Model Comparer with Inferred FKs


Step 4 (optional) – The Report view also show all inferred keys. If generating a lot of keys the report view is a more eye-friendly way to go through them in order to determine which ones are valid/desired in the model.

Huagati Model Comparer Reports View


Step 5 – Select the FK constraints that you want to use for generating associations in the model. Click Update SSDL to add them to the storage model (SSDL) in the Entity Framework 4 model.

Inferred Foreign Key Constraints


Step 6 (optional) – The SSDL-to-DB tree will now show the newly added association. This allows inferred FKs to be materialized as SQL-DDL-scripts if desired. If not, select them and click Ignore. If you want to generate a SQL-DDL script, allowing the inferred FKs to be added to the database, select the FKs and click on Update Database.

Huagati Model Comparer


Step 7 (optional) – If you generated SQL-DDL in Step 6, the SQL-DDL script will appear in a separate SQL editor in the Visual Studio instance that the Model Comparer belongs to. This allows you to review, edit, and save the script before applying to the database.

Huagati Model Comparer with generated SQL-DDL script in the background


Step 8 – Select the new association(s) in the SSDL-to-CSDL tree and click Update CSDL to add them to the conceptual layer of the model. This will result in navigation properties added between the involved entities.

Huagati Model Comparer

Step 9 – Close the model comparer and return to Visual Studio. Continue coding. Enjoy. Smile

Entity Framework model with inferred association (in AdventureWorks)


The Huagati Model Comparer

The Model Comparer is a feature in the Huagati DBML/EDMX Tools add-in for Visual Studio 2010. The add-in can be downloaded from http://huagati.com/dbmltools/ , and time-limited free trial licenses are available from the same site.

Screencasts showcasing some of the basic functionality in the Model Comparer for EFv4 is available at http://huagati.blogspot.com/2010/07/introducing-model-comparer-for-entity.html and http://huagati.blogspot.com/2010/08/using-model-comparer-to-generate.html

13 December 2010

Query Profiling SQL Azure when using Entity Framework or Linq-to-SQL

One slight shortcoming in Microsoft’s SQL Azure (the SQL Server 2008 flavor that is hosted  in Microsoft’s cloud platform) is that users don’t have the trace privileges needed to use SQL Profiler to profile query and performance behavior. Fortunately there are other ways to extract performance data for individual queries; SQL Azure exposes IO statistics, timings, and execution plans in the same way as normal non-cloud editions of SQL Server.

I have recently done some testing against SQL Azure using my tools. Huagati DBML/EDMX Tools needed some minor adjustments to work against SQL Azure as outlined in a previous blog post: http://huagati.blogspot.com/2010/12/sql-azure-support-in-huagati-dbmledmx.html

Next up was testing the profiling / logging components for Entity Framework 4 and Linq-to-SQL in Huagati Query Profiler against SQL Azure. Fortunately I can announce that it works just fine; the techniques used by the Huagati Query Profiler’s logging components for capturing server-side timings, I/O statistics, query execution plans are all supported in SQL Azure. The log entries will look the same and the same performance data and filtering options that work against local SQL Server instances can be used against SQL Server in the cloud.


One interesting thing I noticed while testing is that the roundtrip times against the SQL Azure instance I am accessing is only 100-150ms above accessing a database on my local LAN. That is impressive considering I am in Thailand and the SQL Azure instance is in Singapore, and faster than what I have seen mentioned in some forums and blog posts from US SQL Azure users.

To get started, download and install the Huagati Query Profiler from http://huagati.com/l2sprofiler/ and take a look at these blog posts and/or the sample code that is installed together with the profiler:

Linq-to-SQL: http://huagati.blogspot.com/2009/06/profiling-linq-to-sql-applications.html
Entity Framework: http://huagati.blogspot.com/2010/06/entity-framework-support-in-huagati.html

Next, you can either try the sample projects that ship with the profiler against your own SQL Azure instance, or add profiling support to your own Entity Framework 4 or Linq-to-SQL based projects against a SQL Azure database.

02 December 2010

Creating or modifying Entity Framework EDMX files from code: an introduction to HuagatiEDMXTools.dll

Microsoft’s Entity Framework stores models as xml-based EDMX files. Within the EDMX file, the model is divided up into the conceptual layer (CSDL) which describes the C# or VB entity classes and methods, the storage layer (SSDL) which describes the database tables/keys/views/procedures etc, the mapping layer (MSL) which describes how everything in the conceptual model maps to the storage model, and finally one portion describing the designer diagram layout.

The EDMX files are typically generated and modified by the designer in Visual Studio, by edmgen.exe/edmgen2.exe, or by third party tools such as the model comparer in Huagati DBML/EDMX Tools.

Reading/writing EDMX files

Sometimes people look for ways to generate, modify, or parse EDMX files from code. The straightforward answer to that is usually to use an XML parser; XmlDocument, XmlReader/XmlWriter, Linq-to-XML etc. Using XML parsers to read and write EDMX files works fine, but it also requires a fair bit of time spent on reading the CSDL, SSDL, and MSL specifications as well as some manual work figuring out some of the things not clearly spelt out in the specification such as how different common or uncommon mapping scenarios are best described in the MSL.

The HuagatiEDMXTools.dll library

Before developing the new Entity Framework 4 features in Huagati DBML/EDMX Tools, I decided to step away from directly using an XML parser and instead add an intermediate abstraction layer. That makes it a lot easier to work with the models and separates out most of the quirky mapping logic. The result was a separate wrapper library called HuagatiEDMXTools.dll . Underneath it still uses a XML parser, but all model artifacts are exposed as classes, properties, IEnumerables, IQueryables etc. Some related object types such as associationsets and associations are merged together, related objects available as properties, and useful queries and lookups are exposed as simple lookup properties and IQueryables/functions.

Although HuagatiEDMXTools was written to support the EFv4-related functionality in Huagati DBML/EDMX Tools, I decided to document it to allow add-in users to write their own code to create/modify/read EDMX models for scenarios that are not covered by the add-in or other tools. Although the library has been around and exposed since version 1.90 and for the most part fairly self-explanatory, the documentation for it wasn’t released until version 2.11. The documentation is in MSDN format (thanks, Sandcastle and SHFB), and is available online at http://huagati.com/edmxtools/help/ as well as in the form of a HTML help file that is installed together with the Huagati DBML/EDMX Tools add-in.

Online help screenshot

To use the HuagatiEDMXTools library from a VS project, simply add a reference to the file HuagatiEDMXTools.dll from the project references:

Adding a reference to HuagatiEDMXTools.dll

When using the library, start by initializing the licensing module by passing the license data from the add-in’s license file ( \users\[username]\Documents\Visual Studio 2010\Addins\HuagatiDBMLTools2010.dll.lic ) to the license class:

Setting license data in Huagati EDMX Tools' license class


Next, you can use the library to create, modify, read EDMX files, or simply to query for various model metrics using some of the built-in queries, or by writing new ones against the many IEnumerables and IQueryables exposed by the library.

Sample showing how to read and query metrics from an existing EDMX file

Sample 1/2 showing how to create a new EDMX file from scratch

Sample 2/2 showing how to create a new EDMX file from scratch


As I mentioned above, the HuagatiEDMXTools library ships with Huagati DBML/EDMX Tools. You can download the latest version of Huagati DBML/EDMX Tools from http://huagati.com/dbmltools/ and get a license (free trial, or a full paid license) from the same site.

The online help for the library is available at http://huagati.com/edmxtools/help/ as well as in a .chm file that is installed together with Huagati DBML/EDMX Tools.