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:

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.

Doron Farber - The Farber Consulting Group

I started to develop custom software since 1985 while using dBase III from Aston Tate. From there I moved to FoxBase and to FoxPro and ended up working with Visual FoxPro until Microsoft stopped supporting that great engine. With the Visual FoxPro, I developed the VisualRep which is Report and Query Engine. We are also a dot net development company, and one of our projects is a web scrapping from different web sites. We are Alpha AnyWhere developers, and the Avis Car Rental company trusted us with their contract management software that we developed with the Alpha Five software Engine.


Got questions about unleashing the full potential of your project?
We’ve got the answers!

Contact Us