Can you restrict connections to Sybase ASE by IP address?

[Susan]

We would like to restrict access to our Sybase ASE
dataserver by IP address. We would like to learn if Sybase
ASE allows you to restrict the connection to a specific
and/or list of IP addresses?

Thank you,

Susan

[Manish Negandhi]

Yes you can *restrict* connections to specific and/or list of IP
address in ASE, the better word is you can *deny* the connections.
This means connection will still come to ASE but as soon as connection
is established it will be disqualified and hence terminated, if it is
not from the machine you desire or if it is from the list of
restricted connections defined by you Although there is no direct
way to achieve it you can write a simple sql in a stored proc and
attach the proc to all the logins. The script will execute upon a
successful login and will validate if user connection is authorized to
access the server or not , here is how you do it

create proc restrictuser as
declare @ip char(15)
select @ip = ipaddr from master..sysprocesses where spid = @@spid
if @ip in ('ipaddr1','ipaddr2','ipaddr3')
begin
select syb_quit()
end
go

sp_modifylogin login1,'login script',restrictuser
go

grant execute on restrictuser to public
go

Execute sp_modifylogin for all the logins you have and you should be
all set !

-HTH
Manish Negandhi
[TeamSybase Intern]

[David Wein]

No built-in way that I know of. However, you could probably use login
triggers to accomplish this. For example, you can create a table of
legal ip addresses, then check if sysprocesses.ipaddr is in that table.

-Dave

[Susan]

Great advice. I like the idea a lot.

What about using one of the following third party
solutions...

- LDAP
- KERBEROS
- PAM

Are the above commonly used?

In addition, we read about using..

- Authentication by Certificates

What are your thoughts about the above?

Thank you.

[Susan]

Dave,

Thank you again for your response. I was just thinking
about your idea in using login triggers...

How would we cleanly terminate the user connection, before
they gained access to the database, if they failed to match
an IP address in our special IP address table? I'm thinking
we will have to write some logic into the application that
can handle a rejected user connection as well. What do you
think?

Thank you.

[Cory Sane]

Susan,

I've successfully used ldap on my dev systems.

Here is a white paper that Sybase produced a few yrs ago.
http://www.sybase.com/detail?id=1026313
The white paper is a little dated as Sybase has enhanced the prouct greatly
in the last few releases.
ASE 15.0.2esd#2 just came out with LDAPS support also.

sp_ldapadmin does most of the configuration for these options.

Cory Sane
[TeamSybase Intern]
Certified Sybase Associate DBA for ASE 15.0

<Susan> wrote in message news:47a7efb9.6aa...@sybase.com...

[David Wein]

It might be tough to do gracefully. In the login trigger you can select
the "undocumented" builtin syb_quit:

select syb_quit()

This will terminate the connection. Because TDS does not have a
server-side logout protocol the client sees an abrupt disconnected - the
same as if the sa had done a kill <spid>.

As you mentioned, ASE does support lots of other authentication
mechanisms such as PAM, LDAP, and Kerberos. This is outside of my
expertise so I don't feel qualified to give advice on those.

-Dave

[Derek Asirvadem]

> What about using one of the following third party solutions...
>
> - LDAP
> - KERBEROS
> - PAM
>
> Are the above commonly used?

In my experience (Banks and Financial Institutions outside US) LDAP and
Kerberos are commonly used. The latter needs quite a bit of set up,
and components in several locations (including an ASE chunk), but is
very thorough, and requires administration.

> In addition, we read about using..
>
> - Authentication by Certificates

Which really is good old Secure Sockets Layer [NetScape], implemented
at the ASE level. The Certificates are issued by a real third party
(eg. VeriSign) or fudged (eg. you can be your own Cartificate
Authority) is you are not doing commercial transactions across the
InterNet. But most of us were using SSL plus Certificates before
Sybase implemented it in ASE/FFS and have not changed (it remains in
the domain of the Unix Admins, not the DBAs).

But keep in mind:
1 virtually every site (not just banks) do some form of IP address
filtering, and that is usually doen in a simple, cheap, firewall-type
appliance, which is pretty much designed to do that, only. In a
typical ASE installation in a bank over here, there are at least four
firewall-type appliances between ASE and the outside world, the
inner-most of which filters internal (already inside three firewalls
and authenticated on other corporate services) users.
2 Who do you want to (a) set up and (b) administer/maintain whatever
security model your organisation chooses.

--
Cheers
Derek
Senior Sybase DBA / Information Architect
Copyright Š 2007 Software Gems Pty Ltd
Quality Standards = Zero Maintenance + Zero Surprises
Performance Standards = Predictability + Scaleability

[susan]

Great feedback! Thank you all for your great feedback.

From reading your thoughts and ideas, I am starting to feel
like this should be handled by the UNIX team and is not a
DBA team function. Especially your explanation of
firewalls. This makes sense. Or even LDAP and KERBEROS,
they too should be driven from the server team and not the
DBA team. Hmm...

The challenge is... everyone keeps saying... Oracle can
filter by ip address, why can't Sybase ASE. I will tell
them that just because you can, doesn't mean you should.
Meaning, work with the UNIX team on this effort, but let
them be the driver.

Many thanks guys!

> Copyright © 2007 Software Gems Pty Ltd