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.
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
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.
Go back to our: Main Blog
Written by: Doron Farber - The Farber Consulting Group, Inc