Configuring Local Security Policy to control access to MSSQL DesktopEd

[Guest daz_oldham]

Hi everyone

 

I'm not too sure if this post is more relevant for the SQL group or

not - as it is really a windows security question... but here goes :)

 

I'm trying to lock my SQL Server Desktop Edition down which is

running on a relatively fresh install of Windows 2003 Server Standard

Edition which is a dedicated server from Fasthosts.

 

I'd like to create a local security policy that denies access to all

remote addresses other than my fixed IP address at home and work.

 

However, all attempts to create a policy just result in nothing

actually changing - can anybody suggest an article with step-by-step

idiot proof instructions.

 

Somebody is trying to log on as SA but they aren't going to get far as

one of the first things I did was disable the user.... but the

constant logon attempts are affecting server performance.

 

Many thanks in advance everyone

 

Darren

[Guest Ace Fekay [MVP]]

Re: Configuring Local Security Policy to control access to MSSQL Desktop Ed

 

Re: Configuring Local Security Policy to control access to MSSQL Desktop Ed

 

In news:e4beea64-8b5a-492e-8966-c588adf23ed0@s50g2000hsb.googlegroups.com,

daz_oldham <Darren.Ratcliffe@gmail.com> typed:

> Hi everyone

>

> I'm not too sure if this post is more relevant for the SQL group or

> not - as it is really a windows security question... but here goes :)

>

> I'm trying to lock my SQL Server Desktop Edition down which is

> running on a relatively fresh install of Windows 2003 Server Standard

> Edition which is a dedicated server from Fasthosts.

>

> I'd like to create a local security policy that denies access to all

> remote addresses other than my fixed IP address at home and work.

>

> However, all attempts to create a policy just result in nothing

> actually changing - can anybody suggest an article with step-by-step

> idiot proof instructions.

>

> Somebody is trying to log on as SA but they aren't going to get far as

> one of the first things I did was disable the user.... but the

> constant logon attempts are affecting server performance.

>

> Many thanks in advance everyone

>

> Darren

 

Have you actually determined who is trying to log on as SA? Is it actually

someone or an app running that's using the service? I'm not sure how to

determine this in SQL.

 

This would probably be better for the SQL MSDE newsgroup. I cross posted it

to that group for you. Responses (followups) will return to both groups.

 

(x-posted to microsoft.public.sqlserver.msde, f/ups to both)

 

--

Regards,

Ace

 

This posting is provided "AS-IS" with no warranties or guarantees and

confers no rights.

 

Ace Fekay, MCSE 2003 & 2000, MCSA 2003 & 2000, MCSE+I, MCT,

MVP Microsoft MVP - Directory Services

Microsoft Certified Trainer

 

For urgent issues, you may want to contact Microsoft PSS directly. Please

check http://support.microsoft.com for regional support phone numbers.

 

Infinite Diversities in Infinite Combinations

[Guest Andrea Montanari]

Re: Configuring Local Security Policy to control access to MSSQL Desktop Ed

 

Re: Configuring Local Security Policy to control access to MSSQL Desktop Ed

 

hi Darren,

Ace Fekay [MVP] wrote:

> In

> news:e4beea64-8b5a-492e-8966-c588adf23ed0@s50g2000hsb.googlegroups.com,

> daz_oldham <Darren.Ratcliffe@gmail.com> typed:

>> Hi everyone

>>

>> I'm not too sure if this post is more relevant for the SQL group or

>> not - as it is really a windows security question... but here goes :)

>>

>> I'm trying to lock my SQL Server Desktop Edition down which is

>> running on a relatively fresh install of Windows 2003 Server

>> Standard Edition which is a dedicated server from Fasthosts.

>>

>> I'd like to create a local security policy that denies access to all

>> remote addresses other than my fixed IP address at home and work.

>>

>> However, all attempts to create a policy just result in nothing

>> actually changing - can anybody suggest an article with step-by-step

>> idiot proof instructions.

>>

>> Somebody is trying to log on as SA but they aren't going to get far

>> as one of the first things I did was disable the user.... but the

>> constant logon attempts are affecting server performance.

 

unfortunately, from the SQL Server point of view, you can not do anything

else.. there's plenty "out there" trying external attacks to "well known"

ports, and SQL Server 1433 tcp/ip port is one of them.. you can modify the

port SQL Server/MSDE is listening on, but this requires the definition of

server Alias on each remote "legal" client or, as better choice", provide

that port info in the connection strings of the applications that are

required to connect to SQL Server/MSDE...

the "real solution" is what you are actually trying to do at the firewall

level, allowing only your subnet and filtered external IPs to connect to

that server, and this will be better answered in OS related NGs..

regards

--

Andrea Montanari (Microsoft MVP - SQL Server)

http://www.asql.biz http://www.hotelsole.com

DbaMgr2k ver 0.21.1 - DbaMgr ver 0.65.1 and further SQL Tools

--------- remove DMO to reply

[Guest Ace Fekay [MVP]]

Re: Configuring Local Security Policy to control access to MSSQL Desktop Ed

 

Re: Configuring Local Security Policy to control access to MSSQL Desktop Ed

 

In news:69d07fF2trjf6U1@mid.individual.net,

Andrea Montanari <andrea.sqlDMO@virgilio.it> typed:

> hi Darren,

<snipped>

>

> unfortunately, from the SQL Server point of view, you can not do

> anything else.. there's plenty "out there" trying external attacks to

> "well known" ports, and SQL Server 1433 tcp/ip port is one of them..

> you can modify the port SQL Server/MSDE is listening on, but this

> requires the definition of server Alias on each remote "legal" client

> or, as better choice", provide that port info in the connection

> strings of the applications that are required to connect to SQL

> Server/MSDE... the "real solution" is what you are actually trying to do

> at the

> firewall level, allowing only your subnet and filtered external IPs

> to connect to that server, and this will be better answered in OS

> related NGs.. regards

 

So the better bet for Darren is to block 1433 at the firewall/VPN inbound?

Probably easier to just create an IPSec policy on the machine blocking 1433.

If this is the case, the following is a good read to help Darren out.

 

How to block specific network protocols and ports by using IPSec

Internet Protocol security (IPSec) filtering rules can be used to help

protect Windows 2000-based, Windows XP-based, and Windows Server 2003-based

computers ...

http://support.microsoft.com/kb/813878

 

 

Ace

[Guest Martin Harran]

Re: Configuring Local Security Policy to control access to MSSQL Desktop Ed

 

Re: Configuring Local Security Policy to control access to MSSQL Desktop Ed

 

 

"Andrea Montanari" <andrea.sqlDMO@virgilio.it> wrote in message

news:69d07fF2trjf6U1@mid.individual.net...

> hi Darren,

> Ace Fekay [MVP] wrote:

>> In

>> news:e4beea64-8b5a-492e-8966-c588adf23ed0@s50g2000hsb.googlegroups.com,

>> daz_oldham <Darren.Ratcliffe@gmail.com> typed:

>>> Hi everyone

>>>

>>> I'm not too sure if this post is more relevant for the SQL group or

>>> not - as it is really a windows security question... but here goes :)

>>>

>>> I'm trying to lock my SQL Server Desktop Edition down which is

>>> running on a relatively fresh install of Windows 2003 Server

>>> Standard Edition which is a dedicated server from Fasthosts.

>>>

>>> I'd like to create a local security policy that denies access to all

>>> remote addresses other than my fixed IP address at home and work.

>>>

>>> However, all attempts to create a policy just result in nothing

>>> actually changing - can anybody suggest an article with step-by-step

>>> idiot proof instructions.

>>>

>>> Somebody is trying to log on as SA but they aren't going to get far

>>> as one of the first things I did was disable the user.... but the

>>> constant logon attempts are affecting server performance.

>

> unfortunately, from the SQL Server point of view, you can not do anything

> else.. there's plenty "out there" trying external attacks to "well known"

> ports, and SQL Server 1433 tcp/ip port is one of them.. you can modify the

> port SQL Server/MSDE is listening on, but this requires the definition of

> server Alias on each remote "legal" client

 

That works very well in practice. I had a similar situation with a dedicated

server with almost constant attacks on Port 1433 so I changed the port to a

very high one, well outside the normal range of ports used in scanning

attacks. I ecpected to still get the occasional attack but have had none at

all in just over 2 years.

 

As you say, you have to modify the aliases on remote clients but that is

very easy.