[freetds] How to specify the local ip address to connect from

[Steven Peterson]

I have exactly the same issue and am looking for a solution. It is quite
common to have a linux server with multiple IP addresses and only one of
the IPs whitelisted by an external database server. Many database drivers
and linux utilities provide the capability to specify the bindAddress,
which is the IP address from which the request is sent.

That being said, what strategies for working around this have people found
work best? 'ip route'? 'iptables'? Something easier?

- Steve Peterson


Hello, I'm using FreeTDS to connect to a MS SQL Server. This worked fine on
the development host, because only the one single IP address of it had to
be whitelisted in the firewall of the MS SQL Server. But on the production
server, several aliased network interfaces with different IP addresses are
set up and the web application is obviously not connecting through the main
interface, which is already whitelisted. How can I set the local ip address
/ interface that FreeTDS connects from to the remote database host?

Kind regards
Marten Lehmann

[James K. Lowden]

On Mon, 29 Oct 2012 12:19:44 -0400

It is quite common to have a linux server with multiple IP addresses
and only one of the IPs whitelisted by an external database server.

If you say so.

Why not fix the network/whitelist/firewall? A rational organization
avoids work instead of creating it.

I see no reason to have a firewall between a TDS server and client,
even if that client is a webserver. I see still less reason to block
legitimate packets from known interfaces. Not only is extra admin work
created, but the efficiency of the network is reduced.

If the reason is "security" then the answer should explain the specific
threat, and why ordinary routing rules are insufficient.

--jkl

[Steven Peterson]

James,

Yours would be my solution if I controlled all of the computers in the
network. But the fact is that the computers I am trying to connect to are
clients' computers for which I am reluctant to ask to change their
firewall. (There are many clients and each has a different procedure for
requesting changes.) The computers I am connecting from use virtual IPs so
that the computers are easy to move around and upgrade without changing the
source IP (theoretically). Simply changing the bind address works in all
my other database connections except for FreeTDS. It is by far easier for
me to use a portable virtual IP address and specify it in a bind address
than contacting each client and getting them to update their firewall rules
whenever I upgrade or change my server configuration. Please consider this
a use case supporting a bind address parameter.

That being said, FreeTDS is an awesome package and I don't know what I
would do without it; thanks for all the work you do on it.

- Steve

Post by James K. Lowden
On Mon, 29 Oct 2012 12:19:44 -0400

It is quite common to have a linux server with multiple IP addresses
and only one of the IPs whitelisted by an external database server.

If you say so.
Why not fix the network/whitelist/firewall? A rational organization
avoids work instead of creating it.
I see no reason to have a firewall between a TDS server and client,
even if that client is a webserver. I see still less reason to block
legitimate packets from known interfaces. Not only is extra admin work
created, but the efficiency of the network is reduced.
If the reason is "security" then the answer should explain the specific
threat, and why ordinary routing rules are insufficient.
--jkl
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

[James K. Lowden]

On Mon, 29 Oct 2012 16:11:21 -0400
Steven Peterson <speterson at listpilot.com> wrote:

Thanks for explaining the scenario; at least now I see why the firewall
is a legitimate obstacle.

the computers I am trying to connect to are clients' computers for
which I am reluctant to ask to change their firewall.

OK.

The computers I am connecting from use virtual IPs so that the
computers are easy to move around and upgrade without changing the
source IP (theoretically).

I suppose the same machine is connecting to multiple servers, each with
its own restriction on source IP address?

It seems the source address *could* be controlled in FreeTDS using
bind(2). I've never done it, but a lap around the Internets suggests
that would work. I would guess that's what "ssh -b" does, for
instance. If you provided a patch to do that using a new setting in
freetds.conf, along with documentation, I could see myself applying
it.

However, this is a general problem: in general, to connect to remote
machines, all clients, not only FreeTDS, need to present themselves as
originating from an acceptable source. The natural answer is to use
network address translation at the source.

--jkl

[Steven Peterson]

James,

I suppose the same machine is connecting to multiple servers, each with

Post by James K. Lowden
its own restriction on source IP address?

Yes; exactly

Post by James K. Lowden
However, this is a general problem: in general, to connect to remote
machines, all clients, not only FreeTDS, need to present themselves as
originating from an acceptable source. The natural answer is to use
network address translation at the source.

I'd love to help, but I'm afraid I do not have the C skills to provide a
patch. My interim solution will be as you suggest -- use the iptables nat
table to change the source IP address based on the port, (I can specify a
dummy port in FreeTDS, and use that as a flag to iptables to rewrite the
'from' IP). While this may be the general solution, it is probably
getting further into linux than many FreeTDS users are able. So, please
consider a bindAddress parameter as a suggestion to make using FreeTDS
easier for people who are not so familiar with linux.

- Steve

[Peter Deacon]

Post by Steven Peterson

Post by James K. Lowden
However, this is a general problem: in general, to connect to remote
machines, all clients, not only FreeTDS, need to present themselves as
originating from an acceptable source. The natural answer is to use
network address translation at the source.

I'd love to help, but I'm afraid I do not have the C skills to provide a
patch. My interim solution will be as you suggest -- use the iptables
nat table to change the source IP address based on the port, (I can
specify a dummy port in FreeTDS, and use that as a flag to iptables to
rewrite the 'from' IP). While this may be the general solution, it is
probably getting further into linux than many FreeTDS users are able.
So, please consider a bindAddress parameter as a suggestion to make
using FreeTDS easier for people who are not so familiar with linux.

FWIW unless you bind to a specific interface the kernel normally selects
source address based on local knowledge of routing table.

If you add a route preferencing destination OS will pick the right
source address for you.

For example:

route add -host mysqlserver mysourceinterface
(route add -host 10.0.0.1 eth1:1)

regards,
Peter

[Steven Peterson]

Peter,

Thanks for your comment on using 'route'. Do you think that using 'route'
would be preferred over 'iptables' as a way to indicate the sending IP in
FreeTDS connections? What are the advantages?

- Steve

Post by Steven Peterson
My interim solution will be as you suggest -- use the iptables

Post by Steven Peterson
nat table to change the source IP address based on the port, (I can
specify a dummy port in FreeTDS, and use that as a flag to iptables to
rewrite the 'from' IP). While this may be the general solution, it is
probably getting further into linux than many FreeTDS users are able.
So, please consider a bindAddress parameter as a suggestion to make
using FreeTDS easier for people who are not so familiar with linux.

FWIW unless you bind to a specific interface the kernel normally selects
source address based on local knowledge of routing table.
If you add a route preferencing destination OS will pick the right
source address for you.
route add -host mysqlserver mysourceinterface
(route add -host 10.0.0.1 eth1:1)
regards,
Peter

[James K. Lowden]

On Tue, 30 Oct 2012 10:13:50 -0400

Post by Steven Peterson
Do you think that using 'route'
would be preferred over 'iptables'

FWIW, I do. If you can use route(8) to meet your needs, you're just
using the kernel and ordinary tcp/ip routing. NAT (e.g. iptables)
would add another layer: more to understand, and more to manage.

--jkl

[Frediano Ziglio]

Post by James K. Lowden
On Tue, 30 Oct 2012 10:13:50 -0400

Post by Steven Peterson
Do you think that using 'route'
would be preferred over 'iptables'

FWIW, I do. If you can use route(8) to meet your needs, you're just
using the kernel and ordinary tcp/ip routing. NAT (e.g. iptables)
would add another layer: more to understand, and more to manage.

And I would add that route is more portable than iptables. I know it
work (with different syntax!) on HP-UX, Linux and Windows.

Frediano

[Craig A. Berry]

Post by James K. Lowden
Why not fix the network/whitelist/firewall? A rational organization
avoids work instead of creating it. *

Bzzt. You used "rational" and "organization" in the same sentence :-). And you also assumed it's a single organization that designs the architecture of the network and the architecture of the services and applications.

Post by James K. Lowden
I see no reason to have a firewall between a TDS server and client,
even if that client is a webserver. I see still less reason to block
legitimate packets from known interfaces. Not only is extra admin work
created, but the efficiency of the network is reduced.

I get the feeling that internal firewalls are on the rise, especially the ones that do "deep packet inspection." In other words, they do the equivalent of wireshark on your connection attempt in order to see if it's worthy of being allowed. If it doesn't match the profile for something explicitly allowed by rule, the connection isn't made, even if it's on an allowed port. Telnet to port 1433 tells you nothing about whether your SQL Server is running.

And a successful connection from your workstation tells you nothing about why the user you are supporting can't connect because you are in a different zone from the user, and the various servers involved are in one or more different zones from both of you and possibly from each other.

Post by James K. Lowden
If the reason is "security" then the answer should explain the specific
threat, and why ordinary routing rules are insufficient.

I suspect there are piles of whitepapers that attempt to do just that but it's not something I've ever looked into.



________________________________________
Craig A. Berry
mailto:craigberry at mac.com

"... getting out of a sonnet is much more
difficult than getting in."
Brad Leithauser

[James K. Lowden]

On Tue, 30 Oct 2012 22:23:13 -0500

Post by Craig A. Berry
And you also assumed it's a single organization that designs the
architecture of the network and the architecture of the services and
applications.

Quite so. From time to time questions are asked here that make me
wonder, "But why?". Sometimes by asking I help the person asking to
find an easier way. And sometimes the siituation turns out to be one I
haven't dealt with.

Post by Craig A. Berry

Post by James K. Lowden
I see no reason to have a firewall between a TDS server and client,

I get the feeling that internal firewalls are on the rise, especially
the ones that do "deep packet inspection."

What was that your said about rational organizations? Back when I was
riding the Pony Express, they told us networks exist to facilitate
communication, not to prevent it.

Post by Craig A. Berry

Post by James K. Lowden
If the reason is "security" then the answer should explain the
specific threat, and why ordinary routing rules are insufficient.

I suspect there are piles of whitepapers that attempt to do just that
but it's not something I've ever looked into.

That may be. I approach security policies skeptically because in
general they follow a feasibilty gradient: the policy in force is that
which is technically feasible. We control what we can because we can,
nothing more.

--jkl