2nd try: SQL server network access restrictions, pls SQL gurus comment it

2nd try: SQL server network access restrictions, pls SQL gurus comment it

Post by DotNet00 » Fri, 29 Mar 2002 06:06:17



Hi,

Sorry for this repost, but I can't see the reason why this topic generates
so deep silence here?

Is it stupid?
or
Is it wellknown?
or
Is it so suprising, after using SQL 2000 for years?
or
Is it boring and unsignificant?
or
Wrong newsgroup? (then where to post this?)

------------------------
The original post was:

1) Is there any way to restrict SQL Server 2000 in which TCP/IP address
listen to if there is more than one interface exists?

I read in the errorlog this:

2002-03-25 16:35:34.36 server    SQL server listening on 192.168.0.7: 1052.
2002-03-25 16:35:34.37 server    SQL server listening on 192.169.0.7: 1052.
2002-03-25 16:35:34.37 server    SQL server listening on 127.0.0.1: 1052.
...
2002-03-25 16:35:36.87 server    SQL server listening on TCP, Shared Memory
(Name Pipes was nuked before this restart)

2) If the answer to 1) is yes, than
Is it possible to restrict SQL Server to listen TCP/IP socket only to
127.0.0.1 and disable network access?  (this will be a Web Server...) (of
course Named Pipers (and 100 Pipers also) are disabled)

3) If the answer is to 2) is no then

What if I disable all Named Pipes and TCP/IP sockets? (Shared memory
remains!!!)
...
See the log:
2002-03-25 16:35:36.87 server    SQL server listening on Shared Memory.

Is this mean that my SQL Server is accessible only for the local clients and
not for remote clients? (ODBC tools must use (LOCAL) keyword as the machine
name in this case, ADO connection string can use anything.
(Just for the case if U want try this setting, Enterprise Manager
registration must use (LOCAL) as the machin name...)

Is it an official or "right" way to increase security?  (+ Firewall of
course)

As I searched the MS groups many of questions found "How can I restrict
remote access to my SQL..." but the answers are always:

1) No it is not possible
2) Use TCP/IP packet filtering or more...
3) Other.

Missed I something?
pls comment/correct me

thx

 
 
 

2nd try: SQL server network access restrictions, pls SQL gurus comment it

Post by Carlos Eduardo Roja » Fri, 29 Mar 2002 06:30:53


The thing is that you already have the answer.
--
HTH
---------------------------------------------------------------------
Carlos Eduardo Rojas
MCSE+I, MCDBA, MCSS, SQL Server MVP
Author: SQL Server Programming By Example
http://www.sqlserverbyexample.com


Quote:> Hi,

> Sorry for this repost, but I can't see the reason why this topic generates
> so deep silence here?

> Is it stupid?
> or
> Is it wellknown?
> or
> Is it so suprising, after using SQL 2000 for years?
> or
> Is it boring and unsignificant?
> or
> Wrong newsgroup? (then where to post this?)

> ------------------------
> The original post was:

> 1) Is there any way to restrict SQL Server 2000 in which TCP/IP address
> listen to if there is more than one interface exists?

> I read in the errorlog this:

> 2002-03-25 16:35:34.36 server    SQL server listening on 192.168.0.7:
1052.
> 2002-03-25 16:35:34.37 server    SQL server listening on 192.169.0.7:
1052.
> 2002-03-25 16:35:34.37 server    SQL server listening on 127.0.0.1: 1052.
> ...
> 2002-03-25 16:35:36.87 server    SQL server listening on TCP, Shared
Memory
> (Name Pipes was nuked before this restart)

> 2) If the answer to 1) is yes, than
> Is it possible to restrict SQL Server to listen TCP/IP socket only to
> 127.0.0.1 and disable network access?  (this will be a Web Server...) (of
> course Named Pipers (and 100 Pipers also) are disabled)

> 3) If the answer is to 2) is no then

> What if I disable all Named Pipes and TCP/IP sockets? (Shared memory
> remains!!!)
> ...
> See the log:
> 2002-03-25 16:35:36.87 server    SQL server listening on Shared Memory.

> Is this mean that my SQL Server is accessible only for the local clients
and
> not for remote clients? (ODBC tools must use (LOCAL) keyword as the
machine
> name in this case, ADO connection string can use anything.
> (Just for the case if U want try this setting, Enterprise Manager
> registration must use (LOCAL) as the machin name...)

> Is it an official or "right" way to increase security?  (+ Firewall of
> course)

> As I searched the MS groups many of questions found "How can I restrict
> remote access to my SQL..." but the answers are always:

> 1) No it is not possible
> 2) Use TCP/IP packet filtering or more...
> 3) Other.

> Missed I something?
> pls comment/correct me

> thx


 
 
 

2nd try: SQL server network access restrictions, pls SQL gurus comment it

Post by DotNet00 » Fri, 29 Mar 2002 19:36:21


Maybe U have right, but my question was:

"Missed I something?"
"pls comment/correct me"

because I am not sure that what I've found is a correct working solution...
this is why feedback needed.

If this is correct and usable it will goes to the production...

Why I am not sure? Because it seems to be
1) undocumented.
2) not wellknown here in the newsgroups community.

btw, I would like to use this in a Web Server. Of course I know that the
perfect solution would be a separate SQL machine behind a second firewall.
But not all of my customers have money to pay for two machines, pay for two
OSs, and pay for the ISP for hosting two machines.

So I want dedicate this SQL Server installation to "local" use (IIS I mean).
Then I start to search in the MSDN, newsgroups, and anywhere "How to
restrict SQL server to local access" and found nothing. To be more exact I
found that it is impossible using SQL Server itself..

A week later I started my _client_ config util, and see this "shared memory"
checkbox. Immediatelly started the server config util, and found nothing.
Anyway, I killed all protocols in server (Named Pipes, TCP/IP sock.) and
tryed to connect.
After it I examined the logs. (As I wrote in my original post)

The strange thing is that it seems as this "shared memory" thing is a very
similar than named pipes, or tcp/ip socket (of course for local access), but
in the user interface of the client config and server config util this is
not consequent.

thx for all who spend time to read/comment/correct/improve this message.

Bye



> The thing is that you already have the answer.
> --
> HTH
> ---------------------------------------------------------------------
> Carlos Eduardo Rojas
> MCSE+I, MCDBA, MCSS, SQL Server MVP
> Author: SQL Server Programming By Example
> http://www.sqlserverbyexample.com



> > Hi,

> > Sorry for this repost, but I can't see the reason why this topic
generates
> > so deep silence here?

> > Is it stupid?
> > or
> > Is it wellknown?
> > or
> > Is it so suprising, after using SQL 2000 for years?
> > or
> > Is it boring and unsignificant?
> > or
> > Wrong newsgroup? (then where to post this?)

> > ------------------------
> > The original post was:

> > 1) Is there any way to restrict SQL Server 2000 in which TCP/IP address
> > listen to if there is more than one interface exists?

> > I read in the errorlog this:

> > 2002-03-25 16:35:34.36 server    SQL server listening on 192.168.0.7:
> 1052.
> > 2002-03-25 16:35:34.37 server    SQL server listening on 192.169.0.7:
> 1052.
> > 2002-03-25 16:35:34.37 server    SQL server listening on 127.0.0.1:
1052.
> > ...
> > 2002-03-25 16:35:36.87 server    SQL server listening on TCP, Shared
> Memory
> > (Name Pipes was nuked before this restart)

> > 2) If the answer to 1) is yes, than
> > Is it possible to restrict SQL Server to listen TCP/IP socket only to
> > 127.0.0.1 and disable network access?  (this will be a Web Server...)
(of
> > course Named Pipers (and 100 Pipers also) are disabled)

> > 3) If the answer is to 2) is no then

> > What if I disable all Named Pipes and TCP/IP sockets? (Shared memory
> > remains!!!)
> > ...
> > See the log:
> > 2002-03-25 16:35:36.87 server    SQL server listening on Shared Memory.

> > Is this mean that my SQL Server is accessible only for the local clients
> and
> > not for remote clients? (ODBC tools must use (LOCAL) keyword as the
> machine
> > name in this case, ADO connection string can use anything.
> > (Just for the case if U want try this setting, Enterprise Manager
> > registration must use (LOCAL) as the machin name...)

> > Is it an official or "right" way to increase security?  (+ Firewall of
> > course)

> > As I searched the MS groups many of questions found "How can I restrict
> > remote access to my SQL..." but the answers are always:

> > 1) No it is not possible
> > 2) Use TCP/IP packet filtering or more...
> > 3) Other.

> > Missed I something?
> > pls comment/correct me

> > thx

 
 
 

2nd try: SQL server network access restrictions, pls SQL gurus comment it

Post by Kevin McDonne » Sun, 14 Apr 2002 10:08:22


1. SQL will bind to all cards on the machine for SQL 2k as shown in your
errorlog.
You can't change or select what cards /ip's it will listen on.

2. SQL will by default listen on Shared Memory, TCP,and Named Pipes.
Shared Memory can only be used to connect while you're local to the
machine.  

I'm not sure why you want to disable all available protocols on the machine.

In previous versions (local) was used to make a local pipe connection.  
A local connection in SQL 2k uses Shared Memory instead.

Client application should use the Servername in the connection string, Not
"local".  I you are developing your application
on the SQL Server box, then local would work, but as soon as you deploy
this to another machine it will fail.

Security Concerns:
Never have a blank 'sa' password.
Use NT Authentication only.
Don't use localsystem for the SQL Server startup account.
Put SQL behind a firewall or use Proxy Server/ISA Server to protect /limit
who can connect to it.
Apply the latest security patches to your system.

Thanks,

Kevin McDonnell
Microsoft SQL Server

This posting is provided AS IS with no warranties, and confers no rights.

Are you secure? For information about the Microsoft Strategic Technology
Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.