Introduction

For the last part of this series, we will go over an alternate implementation of the login monitor that utilizes Powershell and the Window Task Scheduler API. This method is both more secure and more effective at protecting the database server against malicious login attempts while continuing to use built-in functionality in Windows and T-SQL for managing the firewall rules.

Background

The original article in this series introduced a way to implement protection against brute force login attacks entirely in T-SQL. While simple to implement, it introduces a vulnerability by making the SQL Server service account a local administrator (a requirement to interact with the Windows firewall via xp_cmdshell and netsh). This is problematic as it gives any user account in the sysadmin server role the ability to run shell commands within an elevated security context, greatly increases the attack surface area of the server, should the database be compromised (I admittedly didn’t have a full understanding of how SQL Server executes xp_cmdshell). There are also plenty of reasons to prevent regular users that have sysadmin-level access to the database from having administrator access to the OS.

Read full news article on THE CODE PROJECT