By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,940 Members | 1,300 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,940 IT Pros & Developers. It's quick & easy.

host_name

P: n/a
Hi,

I have a question regarding host_name() and IP addresses of clients. I'm
running on a shared server - so access to xp_cmdshell is barred which is the
standard response to questions about getting the IP address of a client from
sql server. My issue is this:

For security reasons every user of our database system logs into our custom
security system all under the *same* sql-server user name (who only has
access to a discrete set of stored procedures). This can't be changed as we
are limited to 3 database users. I store the host_name that the user log's
in from when he logs in - and then check the host_name of any further calls
to sp's under this login context. I have however just discovered that
host_name() is set in the connection string - so the client can pass pretty
much whatever he wants to - so all an imposter would have to do is *fake*
the client name of an existing user. Is there anyway of detecting the *real*
client's host? Is there any way of forcing a client to be limited to just
one client machine? Can I get hold of the IP address in a reliable way?

Thanks

Nick

Nov 1 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Nick Stansbury (ni************@sage-removepartners.com) writes:
I have a question regarding host_name() and IP addresses of clients. > I'm running on a shared server - so access to xp_cmdshell is barred which is the standard response to questions about getting the IP address
of a client from sql server. My issue is this:

For security reasons every user of our database system logs into our
custom security system all under the *same* sql-server user name (who
only has access to a discrete set of stored procedures).
This is a reasonable scenario, if the user authenticates with some middle
layer and the middle layer in its turn logs into the database with some
built-in username/password (or Windows authentication.)

But it does not really sound like this is the case here. Are you saying
that the all users are entering the same username/password? That sounds
like a bad idea, and whatever the reason is for that, I would not quote
security reasons. From a security point of view, this would simply not be
an acceptable arrangement.
This can't be changed as we are limited to 3 database users. I store the
host_name that the user log's in from when he logs in - and then check
the host_name of any further calls to sp's under this login context. I
have however just discovered that host_name() is set in the connection
string - so the client can pass pretty much whatever he wants to - so
all an imposter would have to do is *fake* the client name of an
existing user. Is there anyway of detecting the *real* client's host? Is
there any way of forcing a client to be limited to just one client
machine? Can I get hold of the IP address in a reliable way?


There is a net_address column in sysprocesses, but really what you can
make with that one, I don't know.

If you had been using the middle-layer scenario that I mention, the
middle-layer could have used SET CONTEXT_INFO to set information that
you then could pick up from sysprocesses.context_info.

But I think the root problem is that you are using general accounts,
instead of individual accounts. (I don't understand what you mean with
"we are limited to 3 database users", could you explain that?)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Nov 1 '05 #2

P: n/a
(snip)
Nick wrote:
For security reasons every user of our database system logs into our
custom security system all under the *same* sql-server user name (who
only has access to a discrete set of stored procedures).
Erland wrote:
There is a net_address column in sysprocesses, but really what you can make with that one, I don't know.
Thanks - I'll look at this possibility. I'm not sure if I'm allowed
access to sysprocess with my security rights though.

Erland wrote:If you had been using the middle-layer scenario that I mention, the
middle-layer could have used SET CONTEXT_INFO to set information that you then could pick up from sysprocesses.context_info. But I think the root problem is that you are using general accounts,
instead of individual accounts. (I don't understand what you mean with
"we are limited to 3 database users", could you explain that?)


Ok - I wasn't clear enough - I apologise. Let me be very clear. Our hosting
provider has allocated us
3 database logins - and *only* three database logins. So I'm stuck with
this. Here is what I'm trying to achieve.
We have a web-client which is open to the world - anyone can use it to
register themself as a new user and browse our event calendar. We, or a
member of our admin team, allocates these guys various "rights" and this
determines what they can and can't see / edit / delete etc.
We also have a windows client utility for our heavy users - this plugs into
the same framework of SP's etc.
I'm proceeding under the assumption that anyone will have our general
database login name (i.e. anyone could listen in on the traffic between
client and server and get that login) - so what I was hoping to do is this:

1) Client tool (either web system or windows system) logs into DB and opens
connection
2) User logins in (using our LOGIN sp) to database - passing a small part of
his password (like a bank login syste)
3) The Stored Procedure records the IP address of the request, and then
allocates this IP address and user with a unique KEY (a GUID) and returns it
to the client
4) In every additional request made by this user the "Key" is passed back to
the database - and the IP address of the requesting machine is then checked
against the IP address stored in the table - if there is a discrepancy the
request fails and the key is "deactivated" permanently.

But obviously for this to work i need to reliably get the IP address of each
request - *not* just the host_name (because it seems like you can basically
make that up however you want!)

Any further thoughts?

Nick


Nov 1 '05 #3

P: n/a
It looks like Net_address will do it -

net_address nchar(12) Assigned unique identifier for the network
interface card on each user's workstation. When the user logs in, this
identifier is inserted in the net_address column.
"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...
Nick Stansbury (ni************@sage-removepartners.com) writes:
I have a question regarding host_name() and IP addresses of clients.


I'm running on a shared server - so access to xp_cmdshell is barred
which is the standard response to questions about getting the IP address
of a client from sql server. My issue is this:

For security reasons every user of our database system logs into our
custom security system all under the *same* sql-server user name (who
only has access to a discrete set of stored procedures).


This is a reasonable scenario, if the user authenticates with some middle
layer and the middle layer in its turn logs into the database with some
built-in username/password (or Windows authentication.)

But it does not really sound like this is the case here. Are you saying
that the all users are entering the same username/password? That sounds
like a bad idea, and whatever the reason is for that, I would not quote
security reasons. From a security point of view, this would simply not be
an acceptable arrangement.
This can't be changed as we are limited to 3 database users. I store the
host_name that the user log's in from when he logs in - and then check
the host_name of any further calls to sp's under this login context. I
have however just discovered that host_name() is set in the connection
string - so the client can pass pretty much whatever he wants to - so
all an imposter would have to do is *fake* the client name of an
existing user. Is there anyway of detecting the *real* client's host? Is
there any way of forcing a client to be limited to just one client
machine? Can I get hold of the IP address in a reliable way?


There is a net_address column in sysprocesses, but really what you can
make with that one, I don't know.

If you had been using the middle-layer scenario that I mention, the
middle-layer could have used SET CONTEXT_INFO to set information that
you then could pick up from sysprocesses.context_info.

But I think the root problem is that you are using general accounts,
instead of individual accounts. (I don't understand what you mean with
"we are limited to 3 database users", could you explain that?)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Nov 1 '05 #4

P: n/a
Nick Stansbury (ni************@sage-removepartners.com) writes:
1) Client tool (either web system or windows system) logs into DB and
opens connection
2) User logins in (using our LOGIN sp) to database - passing a small
part of his password (like a bank login syste)
3) The Stored Procedure records the IP address of the request, and then
allocates this IP address and user with a unique KEY (a GUID) and
returns it to the client
4) In every additional request made by this user the "Key" is passed
back to the database - and the IP address of the requesting machine is
then checked against the IP address stored in the table - if there is a
discrepancy the request fails and the key is "deactivated" permanently.

But obviously for this to work i need to reliably get the IP address of
each request - *not* just the host_name (because it seems like you can
basically make that up however you want!)


I will have to admit that when it comes to web sites, I am in foreign
territory. But it sounds to me as if the users are directly connected
to the database. I thought the normal procedure was that the web server
worked as the middle-man. In this case, the web server have the information
about IP-adresses. And if the web service connects to the database, it
can pass that information in the connection string, and you can use
host_name() as the web server can be regarded as reliable. (But of course
you can spoof your IP address if you want to.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Nov 1 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.