binding SQL server to localhost?

Greetings all,
I am a network security professional rather than a MS SQL admin, so I
apologize in advance if this is a bit of a basic question for this
list. I know an admin setting up a SQL server that will only be
accesible by a webserver running on the same host (not happy about
running private vs publicly avaialable services on the same host , but
it's what we've got). As such, I'd like to recommend to him that the
SQL server only listen on the localhost ip, 127.0.0.1, thereby making
it inaccesible to the outside world. I looked around the MS
knowledgebase but couldn't find a clear document stating how to do
this. Is it even possible? Is there a better option for this
configuration?
Thanks,
BrianYou propably need to firewall the server then.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
<tinbox@.nyct.net> schrieb im Newsbeitrag
news:1114053613.247153.87700@.o13g2000cwo.googlegroups.com...
> Greetings all,
> I am a network security professional rather than a MS SQL admin, so I
> apologize in advance if this is a bit of a basic question for this
> list. I know an admin setting up a SQL server that will only be
> accesible by a webserver running on the same host (not happy about
> running private vs publicly avaialable services on the same host , but
> it's what we've got). As such, I'd like to recommend to him that the
> SQL server only listen on the localhost ip, 127.0.0.1, thereby making
> it inaccesible to the outside world. I looked around the MS
> knowledgebase but couldn't find a clear document stating how to do
> this. Is it even possible? Is there a better option for this
> configuration?
> Thanks,
> Brian
>

binding SQL server to localhost?

Greetings all,
I am a network security professional rather than a MS SQL admin, so I
apologize in advance if this is a bit of a basic question for this
list. I know an admin setting up a SQL server that will only be
accesible by a webserver running on the same host (not happy about
running private vs publicly avaialable services on the same host , but
it's what we've got). As such, I'd like to recommend to him that the
SQL server only listen on the localhost ip, 127.0.0.1, thereby making
it inaccesible to the outside world. I looked around the MS
knowledgebase but couldn't find a clear document stating how to do
this. Is it even possible? Is there a better option for this
configuration?
Thanks,
Brian
You propably need to firewall the server then.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
<tinbox@.nyct.net> schrieb im Newsbeitrag
news:1114053613.247153.87700@.o13g2000cwo.googlegro ups.com...
> Greetings all,
> I am a network security professional rather than a MS SQL admin, so I
> apologize in advance if this is a bit of a basic question for this
> list. I know an admin setting up a SQL server that will only be
> accesible by a webserver running on the same host (not happy about
> running private vs publicly avaialable services on the same host , but
> it's what we've got). As such, I'd like to recommend to him that the
> SQL server only listen on the localhost ip, 127.0.0.1, thereby making
> it inaccesible to the outside world. I looked around the MS
> knowledgebase but couldn't find a clear document stating how to do
> this. Is it even possible? Is there a better option for this
> configuration?
> Thanks,
> Brian
>

binding SQL server to localhost?

Greetings all,
I am a network security professional rather than a MS SQL admin, so I
apologize in advance if this is a bit of a basic question for this
list. I know an admin setting up a SQL server that will only be
accesible by a webserver running on the same host (not happy about
running private vs publicly avaialable services on the same host , but
it's what we've got). As such, I'd like to recommend to him that the
SQL server only listen on the localhost ip, 127.0.0.1, thereby making
it inaccesible to the outside world. I looked around the MS
knowledgebase but couldn't find a clear document stating how to do
this. Is it even possible? Is there a better option for this
configuration?
Thanks,
BrianYou propably need to firewall the server then.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
<tinbox@.nyct.net> schrieb im Newsbeitrag
news:1114053613.247153.87700@.o13g2000cwo.googlegroups.com...
> Greetings all,
> I am a network security professional rather than a MS SQL admin, so I
> apologize in advance if this is a bit of a basic question for this
> list. I know an admin setting up a SQL server that will only be
> accesible by a webserver running on the same host (not happy about
> running private vs publicly avaialable services on the same host , but
> it's what we've got). As such, I'd like to recommend to him that the
> SQL server only listen on the localhost ip, 127.0.0.1, thereby making
> it inaccesible to the outside world. I looked around the MS
> knowledgebase but couldn't find a clear document stating how to do
> this. Is it even possible? Is there a better option for this
> configuration?
> Thanks,
> Brian
>sql

binding SQL server to localhost?

Greetings all,

I am a network security professional rather than a MS SQL admin, so I
apologize in advance if this is a bit of a basic question for this
list. I also cross-posted this to microsoft.public.sqlserver.server,
so sorry if anyone's read it already.

I know an admin setting up a SQL server that will only be
accesible by a webserver running on the same host (not happy about
running private vs publicly avaialable services on the same host , but
it's what we've got). As such, I'd like to recommend to him that the
SQL server only listen on the localhost ip, 127.0.0.1, thereby making
it inaccesible to the outside world. I looked around the MS
knowledgebase but couldn't find a clear document stating how to do
this. Is it even possible? Is there a better option for this
configuration?

It's been suggested that firewalling is the only option, but I'd really
like to do *both* (firewall & bind to localhost). The firewall in this
case will have to be host-based instead (software) instead of hardware
for non-technical reasons, so additionally if anyone recommends a
software firewall they use for this purpose I'd appreciate it. My firs
impulse is to recommend Tiny, but I've never used a software firewall
for an MS SQL/Web server before.

Thanks,
BrianOne option is to disable network access completely, and use only shared
memory for access to MSSQL (this is how MSDE operates by default since
SP3), so only applications running on the same machine will be able to
access it. Although someone could still attack MSSQL by compromising
the web server, or using SQL injection.

MSSQL itself doesn't provide any way to accept connections from
specified hosts - you would normally use the operating system's IP
filtering functions to do that.

Simon|||There isn't a way to have sql server listen on a specific ip. Sorry.

If you haven't already seen this, take a look at the Network
Configuration dialog on the general tab of the server's properties.
Two protocols are enabled by default. TCPIP is one of them, and you
can change the port and set it to ignore discovery broadcasts. That
would make the server invisible to anyone looking for it. However,
anyone who portscans the server would notice whatever port you put it
on and (I'd assume) be able to figure out that it's a SQL Server. So,
you do still need to firewall it.

The other protocol is called Named Pipes. That's basically using
memory to communicate. If the application you're developing supports
it, I'd suggest using this and turning off TCPIP support altogether.
This is probably faster anyway.

Chris

Best way to install a database at a remote site

Greetings,

I am in the process of installing a SQL database at a customer
location. I have determined that there are 3 ways to do this, and I
wanted to know which is the best of the 3.

1 Install From Script.
In this method I create the database and its objects in scripts that
are run via osql utility on the SQL server machine. For loading any
initial data that I need in the database I also run bcp commands.

2 Install from a backup
In this method I created an empty database on the SQL server, and then
restore over it the database from a backup of the database that I need
to deploy. Then I add or re-attach the users for the database. I
perform all of these operations using osql as well.

3 Install by attaching the data files.
In this method I created an empty database on the SQL server, and then
I attach the data files to the database using the sp_attach procedure.
Then I add or re-attach the users for the database. I perform all of
these operations using osql as well.

Although it is no problem for me to use any of these methods, I wanted
to know from you veterans out there what the best practices are. And
also if there are any unseen hazards for each method above. Or if I
am totally off-the-target, and there is another method that is the
preferred way.

Thanks in Advance,
roger"great_googley_moogley" <pow_pow1476@.yahoo.com> wrote in message
news:dda0ee86.0404021123.46fd0e4e@.posting.google.c om...
> Greetings,
> I am in the process of installing a SQL database at a customer
> location. I have determined that there are 3 ways to do this, and I
> wanted to know which is the best of the 3.
> 1 Install From Script.
> In this method I create the database and its objects in scripts that
> are run via osql utility on the SQL server machine. For loading any
> initial data that I need in the database I also run bcp commands.
> 2 Install from a backup
> In this method I created an empty database on the SQL server, and then
> restore over it the database from a backup of the database that I need
> to deploy. Then I add or re-attach the users for the database. I
> perform all of these operations using osql as well.
> 3 Install by attaching the data files.
> In this method I created an empty database on the SQL server, and then
> I attach the data files to the database using the sp_attach procedure.
> Then I add or re-attach the users for the database. I perform all of
> these operations using osql as well.
> Although it is no problem for me to use any of these methods, I wanted
> to know from you veterans out there what the best practices are. And
> also if there are any unseen hazards for each method above. Or if I
> am totally off-the-target, and there is another method that is the
> preferred way.
> Thanks in Advance,
> roger

All three options can give the same results, as you say, but there are some
differences in functionality.

Option 1 is good if you need to load different data for different clients -
with other methods you'd need a copy of the database for every possible data
set you need to provide, but with scripts the object scripts are the same,
and only the data (BCP) files change. Or from the database object
perspective, you can provide different subsets and/or versions of stored
procedures etc. to different clients. In addition, this option doesn't
require you to have any access to the filesystem, which can be useful in
environments with limited bandwidth or tight security requirements. The
downside - if you consider it one - is that you need to manage the scripts,
but presumably you're using some sort of source control and deployment
scripting already.

Options 2 and 3 are essentially the same, in that you're creating the
database from files which you need to copy to a filesystem first. The major
difference is that you can restore from a network drive, but you can't
attach a database on a network drive (usually). So if you have no access to
the server's local filesystem, but you do have access to a network share
accessible to the server, you could only use the restore option. The big
advantage of these options is obviously simplicity..

Personally, I would consider option 1 the best in the sense that it's the
most flexible - you have complete control over which objects and data are
loaded, and the layout of the files and filegroups can be decided together
with the DBA before you create the database. But if you're always installing
identical databases in very similar environments (eg. inside a large
company), then the simplicity of one of the other options is likely to be
better for you - less time and effort required.

Simon|||On Fri, 2 Apr 2004 22:07:49 +0200, Simon Hayes <sql@.hayes.ch> wrote:

> "great_googley_moogley" <pow_pow1476@.yahoo.com> wrote in message
> news:dda0ee86.0404021123.46fd0e4e@.posting.google.c om...
>> Greetings,
>>
>> I am in the process of installing a SQL database at a customer
>> location. I have determined that there are 3 ways to do this, and I
>> wanted to know which is the best of the 3.
>>
>> 1 Install From Script.
>> In this method I create the database and its objects in scripts that
>> are run via osql utility on the SQL server machine. For loading any
>> initial data that I need in the database I also run bcp commands.
>>
>> 2 Install from a backup
>> In this method I created an empty database on the SQL server, and then
>> restore over it the database from a backup of the database that I need
>> to deploy. Then I add or re-attach the users for the database. I
>> perform all of these operations using osql as well.
>>
>> 3 Install by attaching the data files.
>> In this method I created an empty database on the SQL server, and then
>> I attach the data files to the database using the sp_attach procedure.
>> Then I add or re-attach the users for the database. I perform all of
>> these operations using osql as well.
>>
>> Although it is no problem for me to use any of these methods, I wanted
>> to know from you veterans out there what the best practices are. And
>> also if there are any unseen hazards for each method above. Or if I
>> am totally off-the-target, and there is another method that is the
>> preferred way.
>>
>> Thanks in Advance,
>> roger
> All three options can give the same results, as you say, but there are
> some
> differences in functionality.
> Option 1 is good if you need to load different data for different
> clients -
> with other methods you'd need a copy of the database for every possible
> data
> set you need to provide, but with scripts the object scripts are the
> same,
> and only the data (BCP) files change. Or from the database object
> perspective, you can provide different subsets and/or versions of stored
> procedures etc. to different clients. In addition, this option doesn't
> require you to have any access to the filesystem, which can be useful in
> environments with limited bandwidth or tight security requirements. The
> downside - if you consider it one - is that you need to manage the
> scripts,
> but presumably you're using some sort of source control and deployment
> scripting already.
> Options 2 and 3 are essentially the same, in that you're creating the
> database from files which you need to copy to a filesystem first. The
> major
> difference is that you can restore from a network drive, but you can't
> attach a database on a network drive (usually). So if you have no access
> to
> the server's local filesystem, but you do have access to a network share
> accessible to the server, you could only use the restore option. The big
> advantage of these options is obviously simplicity..
> Personally, I would consider option 1 the best in the sense that it's the
> most flexible - you have complete control over which objects and data are
> loaded, and the layout of the files and filegroups can be decided
> together
> with the DBA before you create the database. But if you're always
> installing
> identical databases in very similar environments (eg. inside a large
> company), then the simplicity of one of the other options is likely to be
> better for you - less time and effort required.
> Simon

I am using option 1 too. But there is one thing I cannot do with it. This
thing is installing custom dll implementing extended stored procedure into
remote computer. Do you know any solution for this problem?

--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/|||great_googley_moogley (pow_pow1476@.yahoo.com) writes:
> 1 Install From Script.
> In this method I create the database and its objects in scripts that
> are run via osql utility on the SQL server machine. For loading any
> initial data that I need in the database I also run bcp commands.

Be careful to use the -I option to enable the option QUOTED_IDENTIFIERS.
This is paricularly important if you are using indexed views.

> 2 Install from a backup
> In this method I created an empty database on the SQL server, and then
> restore over it the database from a backup of the database that I need
> to deploy. Then I add or re-attach the users for the database. I
> perform all of these operations using osql as well.
> 3 Install by attaching the data files.
> In this method I created an empty database on the SQL server, and then
> I attach the data files to the database using the sp_attach procedure.
> Then I add or re-attach the users for the database. I perform all of
> these operations using osql as well.

This far, all methods may appear to be the same. But then comes the next
step: you need to install fixes, updates and changes. Suddenly option
1 is the only one possible.

In our shop we ship install kits both for installation of new databases,
and upgrades. We have a toolset that includes a tool building an empty
database from SourceSafe. You can do that do step, so that you first
extract the files, and then build from the disk at the remote script.
The toolset also includes a tool that can build update scripts with
changed files, and the same applies to them that you can run in two steps.
If you're curious, it's available at http://www.abaris.se/abaperls/ as
freeware.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Igor Solodovnikov (igor@.helpco.kiev) writes:
> I am using option 1 too. But there is one thing I cannot do with it. This
> thing is installing custom dll implementing extended stored procedure into
> remote computer. Do you know any solution for this problem?

See my reply for the original poster, about the toolset we use.

When our installation staff build new databases or run update scripts at
customer sites, they rarely run them directly. They package everything
in Windows Installer kits. I don't know too much about that part of the
process, but I guess this is the way to package it. Of course, learning
Wise and all that may be too much - packaing in a BAT file could work too.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Fri, 2 Apr 2004 22:15:15 +0000 (UTC), Erland Sommarskog
<sommar@.algonet.se> wrote:

> Igor Solodovnikov (igor@.helpco.kiev) writes:
>> I am using option 1 too. But there is one thing I cannot do with it.
>> This
>> thing is installing custom dll implementing extended stored procedure
>> into
>> remote computer. Do you know any solution for this problem?
> See my reply for the original poster, about the toolset we use.
> When our installation staff build new databases or run update scripts at
> customer sites, they rarely run them directly. They package everything
> in Windows Installer kits. I don't know too much about that part of the
> process, but I guess this is the way to package it. Of course, learning
> Wise and all that may be too much - packaing in a BAT file could work
> too.

Thank you for answer. I must say that I also using Wise and Windows
Installer. My installer copies all required files to local computer and
then executes another utility (written by me) which can install new
instance of msde or create database through SQL script on existing
instance of SQL server/msde. When existing instance is on local computer I
can simply copy dll into sql server directory and my extended stored proc
will work. But I cant do that when existing instance is on another
computer.
Using SQL script to create database is very convenient but it has this
problem which complicates creating extended stored procs.
To solve this problem I must create one more Windows Installer package and
run it on remote computer. This is ugly.

--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/