Secure MS SQL server when running web application

When people need to connect publically, those individuals are taking the risk of outside attacks on your server. A year and half ago I noticed that 2 hackers are trying to get into my MS SQL 2008 server via the SA account.

The Windows Log account basically provided me with every attempt to log into the server plus the IP address of the hacker. One was in Canada and the other was from China. I called the Internet provider of the individual from Canada, and they shut down his Internet, the person from China was another story, I sent them an email with no reply.

Call us for Free Consultation for Remote DBA services at our SQL Consulting Firm at: 732-536-4765

But with some simple steps we can avoid the attacks on the server. The first step is to change the default port which is 1433 to any new one that are you comfortable with, but try in a different range then the current default port.

Secondly, disable the SA account and you can use the below code for that. Make sure that you have a valid admin account before you do that.

Before you create the trigger, let's create the table with the following fields in a different database then your data. It could be Utils.mdf

Now we can populate this table with all authorized accounts and the below code will add the Administrator's account into the table. The Principal_ID is the ID number of the Principals table. It is unique within that table.

A Windows Login is an example of an indivisible principal. If you ran the below code then you could see all accounts available, and then you need to make a decision which one should be added into the Logins table.

The below were the first accounts that were added by me:

Then Insert other users' accounts that should be able to log in. I would add one account at the time using the above INSERT sample code.

Here is the Trigger code:

Conclusions:
===========
With the simple steps I described above I increased the security of my server tremendously. Changing MS SQL port is a crucial first step. Then when ever you log into the server the AuditLogin trigger is called which checks accounts found in the Logins table. If the account is found in the table then the Log In will be continued by the server. If not then the trigger will roll back that account. Using that technique you can basically disable a user's account by Deleting that record from the Login table without disabling the actual account in the server.