These scripts are used to allow only IPs/Range defined for clients to connect to SQL Database without any firewall changes:
sqlipallow1.sql ⮕ Creates tables and functions (IPs/Ranges can be configured in this script)
sqlipallow2.sql ⮕ Creates trigger
rollback.sql ⮕ Rollback all the changes
It is possible to check SQL Server Logs for troubleshooting:
This table is used to store IPs/Ranges allowed to connect to database. IP ranges uses CIDR (Classless Inter-Domain Routing) notation. More explanation about this notation can be found at https://networkengineering.stackexchange.com/questions/3697/the-slash-after-an-ip-address-cidr-notation
Examples:
<local machine> ⮕ Local machine where SQL instance is installed
10.145.33.22 ⮕ Fixed IP
10.0.0.0/8 ⮕ IP range, same as 10.x.x.x
IPAddressIsInRange ⮕ Function to check if IP is contained in range
IPAddressToInteger ⮕ Function to convert IP to int
allow_ipaddress ⮕ Trigger to allow connection only for client IPs/ranges found in table IPAllow
Original IP blocking code with simple range mask and more explanations:
http://sql-articles.com/articles/security/ip-address-blocking-or-restriction-in-sql-server/
Original source of functions IPAddressIsInRange and IPAddressToInteger:
https://gist.github.com/phdesign/e899c7536375ad0d373262226c0d00ec