Block access to SQL Server

Imagine what will happen if some unauthorized people get access to your database. They gets the username/password and hacked into your server. You will lose all of your data. They can manipulate or delete data. That might cost you a fortune. Data plays a vital role in your application and are the key of any application. From security point of view, we need to prevent our data in database. This is our primary job.
Is there any alternatives to prevent this? Yes, by blocking access to database based on IP address. In MySQL, there is a built-in control that can allow access from certain host IPs only. But in SQLSERVER there is none. But there definitely is a workaround. We can block access to sqlserver by IP by using a function called EVENTDATA(). This function is used with a trigger and we will know who is trying to connect to the sqlserver. This can be used with any trigger like create table, drop table, logon etc. With each of these event types, EVENTDATA returns specific xml data that can be used as per own need. In case of LOGON, it returns XML data that holds the IP accress of the connecting client. This is the key for our topic. WE will use this IP to filter the connection request and allow or deny the connection even if it has valid credential.

How to do it? We will do this by creating a trigger for "LOGON" on all server. PLease note, triggers like "Create table" etc can be done on database level, but the "LOGON" has to be created on All Server. Lets create a trigger like

CREATE TRIGGER CheckLogin
ON ALL SERVER
FOR LOGON
AS
Declare @IP NVARCHAR(4000);
SET @IP = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]','nvarchar(max)'))
if (@IP != 'xxx.xxx.xxx.xxx')
ROLLBACK;
GO

This will check if the login is originated from "xxx.xxx.xxx.xxx" or not. If not , it will reject the connection even if valid credentials were supplied. There is little more we can do with this. Instead of hard coding the IP, we can create a table that stores a valid IPs. and we can check the @IP against this table to decide whether to allow or deny the connection. To check against the table, we need to access table as [databse].dbo.table. Since this Trigger is fired in root level, we need to specify the table with its database name.
We can even store the information from EVENTDATA() in certain table to check who are trying to access the database and take necessary action.
Please note that, after you create this trigger, make sure you are logged into the server or otherwise you might lose access to the server yourself unless you filtered your IP out in the check. Be Careful.
To drop this trigger simply run following command
DROP TRIGGER CheckLogin
ON ALL SERVER;
GO

Hope this is some information that will be of use to most of us.