Posted by Erland Sommarskog on 11/01/05 11:41

Nick Stansbury (nick.stansbury@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, esquel@sommarskog.se

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

[Back to original message]