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.

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.

ALTER LOGIN sa DISABLE

Then create a trigger script to validate an authorized user based on a table that stores all known credentials of the users. The trigger should be found under Server Objects->Triggers.

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

   
CREATE TABLE [dbo].[Logins](
    [Logins_PK] [Int] IDENTITY(1,1) NOT NULL,
    [Name] [VarChar](40) NULL) 
ON [PRIMARY]

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.

INSERT INTO Logins(Name) 
SELECT Name FROM Sys.Server_Principals
WHERE Principal_ID IN (273)

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.

SELECT Name FROM Sys.Server_Principals

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

MyDataBaseName\Administrator 
NT AUTHORITY\SYSTEM
NT SERVICE\MSSQLSERVER
NT SERVICE\SQLSERVERAGENT

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:
----------------------------------

CREATE TRIGGER [AuditLogin] 
--server means instance level
ON ALL SERVER  
WITH EXECUTE AS SELF
FOR LOGON
AS BEGIN
    IF ORIGINAL_LOGIN() NOT IN (SELECT Name FROM Utils..Logins) 
    ROLLBACK;
END
GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

ENABLE TRIGGER [AuditLogin] ON ALL SERVER
GO

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.