|
|
Posted by Nick Stansbury on 11/01/05 13:05
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" <esquel@sommarskog.se> wrote in message
news:Xns97016C6BAA7B3Yazorman@127.0.0.1...
> 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
>
Navigation:
[Reply to this message]
|