[Help] SQL security & website php scripts

[DeviL125]

Hello all, i've run into a bit of a snag and was hoping the good people here on epvp could give me a clue... I've searched for this but unable to find anything clear.

For security in my SQL Server Configuration Manager under TCP/IP I've disabled external IP's from accessing the database, (Listen all = no, All IPs other than 127.0.0.1 switched to no) Problem is, the scripts on my website such as registration, online players etc can't communicate with the DB either, which some are prudent to function of a server lol. I did try changing one of the IPs in the config manager to my websites IP and enabling. after which i couldnt even connect to DB locally, tried the same with listen all=on, with same result.

so ive switched the settings back to blocking all external so i can access locally again... any clue how i can get my website communicating again WITHOUT having it set listen all and lowering security? am i missing something silly here?

[RebeccaBlack]

I'm a bit confused.. if it denies everything remote from accessing through the port, how is local access a security risk?

How do you expect the PHP handler to be able to communicate with something it can't even "see"?

[DeviL125]

thank you for your reply...
well what i had done was shut down all remote access, local access wasn't a risk ofc but somehow after turning listen all on, and allowing only the website AND local IPs to connect...neither would. so, i went back to local only and listen all to off and i can locally connect to db again.

Local isnt the issue, what i was hoping to accomplish by listen all=on and every IP except that of local access and website blocked, was allow only php from my website and local SQL login, making it impossible to access from anywhere else.

you'll have to forgive me i'm a little nubbish to network security, was just hoping to leave as few doorways open as possible.

[player1up]

There are a number of ways to secure the SQL instance. What you are looking at with the listener is the IP address that it listens on, not the IP address that it listens to...

For example, your telephone line listens on (***) ***-xxxx but receives calls from all numbers, this is what SQL does.

What you are asking ( what I think you are asking ) is how to only receive "calls" from 1 IP address or specified IP addresses.

For that you can create a login trigger that checks the IP address of the incoming request and allows or disconnects based on the login name and IP.

To accomplish that, you'll need a trigger and a table to house the authorized combination of login name and IP

Login trigger:
CREATE TRIGGER [LOGIN_IP_RESTRICTION]
ON ALL SERVER FOR LOGON
AS
BEGIN
DECLARE @host NVARCHAR(255);

SET @host = EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(max)');

IF(EXISTS(SELECT * FROM master.dbo.IP_RESTRICTION
WHERE UserName = SYSTEM_USER))
BEGIN
IF(NOT EXISTS(SELECT * FROM master.dbo.IP_RESTRICTION
WHERE UserName = SYSTEM_USER AND ValidIP = @host))
BEGIN
ROLLBACK;
END
END
END;

Create Table syntax: -- allows only one combination of UserName and IP ( see constraint )
use master
go

CREATE TABLE [dbo].[IP_RESTRICTION](
[UserName] [varchar](255) NOT NULL,
[ValidIP] [varchar](15) NOT NULL,
[Comment] [nvarchar](255) NULL,
CONSTRAINT [PK_IP_RESTRICTION] PRIMARY KEY CLUSTERED
([UserName] ASC, [ValidIP] ASC) ON [PRIMARY]
) ON [PRIMARY]

So at the end of the day, you'll have about 5 lines in your IP_RESTRICTION table, one for your local admin account ( DON'T forget this ) one for your web server, and a couple for your shaiya service accounts

Simple right?

[.Necrosis.]

If you are simply trying to block remote access to SQL...just close port 1433 in the firewall. If you wish to allow access from only select IP addresses, that is what the "Scope" option is for within the firewall, assuming you are using windows integrated firewall.

Make an inbound rule for port 1433 allowance with only the approved IP addresses applied under the scope tab.

[player1up]

yep, firewall rules work well too.
I think I'm leaning toward the SQL trigger route myself. I can pull a table access report to see how many times the table was scanned or how many times the trigger was executed ( thus the number of login attempts ) but I'm also thinking that I think too much

[DeviL125]

a little late on reply i know, haven't been on the last couple days. but thanks for the good answers guys its much appreciated. I will give those a try, because keeping 1433 totally open with only a login to keep hacks at bay doesnt seem up to par. again thanks

[gawrshman]

Originally Posted by player1up

There are a number of ways to secure the SQL instance. What you are looking at with the listener is the IP address that it listens on, not the IP address that it listens to...

For example, your telephone line listens on (XXX) xxx-xxxx but receives calls from all numbers, this is what SQL does.

What you are asking ( what I think you are asking ) is how to only receive "calls" from 1 IP address or specified IP addresses.

For that you can create a login trigger that checks the IP address of the incoming request and allows or disconnects based on the login name and IP.

To accomplish that, you'll need a trigger and a table to house the authorized combination of login name and IP

Login trigger:
CREATE TRIGGER [LOGIN_IP_RESTRICTION]
ON ALL SERVER FOR LOGON
AS
BEGIN
DECLARE @host NVARCHAR(255);

SET @host = EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(max)');

IF(EXISTS(SELECT * FROM master.dbo.IP_RESTRICTION
WHERE UserName = SYSTEM_USER))
BEGIN
IF(NOT EXISTS(SELECT * FROM master.dbo.IP_RESTRICTION
WHERE UserName = SYSTEM_USER AND ValidIP = @host))
BEGIN
ROLLBACK;
END
END
END;

Create Table syntax: -- allows only one combination of UserName and IP ( see constraint )
use master
go

CREATE TABLE [dbo].[IP_RESTRICTION](
[UserName] [varchar](255) NOT NULL,
[ValidIP] [varchar](15) NOT NULL,
[Comment] [nvarchar](255) NULL,
CONSTRAINT [PK_IP_RESTRICTION] PRIMARY KEY CLUSTERED
([UserName] ASC, [ValidIP] ASC) ON [PRIMARY]
) ON [PRIMARY]

So at the end of the day, you'll have about 5 lines in your IP_RESTRICTION table, one for your local admin account ( DON'T forget this ) one for your web server, and a couple for your shaiya service accounts

Simple right?

I put this in and must have goofed or not set it up right because it completely locked me out of the sql and every player out of the game lol.

[oasis1984]

If my understanding of that script is correct, it locks out everyone except the people that are located in that table. You should however be able to log into your sql installation locally either using your Windows Auth or the acct/pass info you used to set up the linked servers with.

Secondly, is it possible to add a firewall rule that say, only allows incoming information for sql to come in from your registration website only?