Configuring Local Security Policy to control access to MSSQL DesktopEd

  • Thread starter Thread starter daz_oldham
  • Start date Start date
D

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
 
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
 
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
 
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
 
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.
 
Back
Top