In most cases when we make API calls from a web application hosted on a Server or a VM. We want to make sure that the database is secured and only accessible to a particular group of individuals. In this case we can allow or block particular IP addresses as situation may demand.
There is no direct method where an IP address can be blocked or allowed to a particular MS SQL database, But the trick is to write a command language trigger on logon event to suffice the need.
For this we need to maintain the list of IP addresses that need to be blocked or allowed on the database (we shall look at blocked example). We can create a table on the master database with the IP address list as follows
|
1 2 3 4 5 |
CREATE TABLE master.dbo.BLockedIPAddresses (ip NVARCHAR(15)) INSERT INTO BLockedIPAddresses VALUES('192.168.0.10') INSERT INTO BLockedIPAddresses VALUES('192.168.0.50') INSERT INTO BLockedIPAddresses VALUES('27.133.7.*') |
The following trigger will help in blocking the request coming from the listed IP addresses
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
CREATE TRIGGER trgBlockIP ON ALL SERVER FOR LOGON AS BEGIN DECLARE @clientip NVARCHAR(15); SET @clientip = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(15)')); IF EXISTS(SELECT ip FROM master.dbo.BLockedIPAddresses WHERE ip = @clientip) BEGIN Print 'Your IP Address is blocked' ROLLBACK END ELSE BEGIN DECLARE @Range VARCHAR(15) SELECT @Range = SUBSTRING(@clientip ,1,LEN(@clientip )-CHARINDEX('.',REVERSE(@clientip )))+'.*' IF EXISTS(SELECT ip FROM master.dbo.BLockedIPAddresses WHERE ip = @Range) BEGIN Print 'Your IP Address Range is blocked' ROLLBACK END END END GO |
The above trigger will prevent login request that are generated from the listed IP addresses.
Post comments if this helps.
