473,322 Members | 1,480 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

host_name

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
4 2712
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
(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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Lauren Quantrell | last post by:
I did this thing with host_name(). I'm hoping someone can tell me if I'm gonna get into trouble with this scheme before it's too late... I have maybe 75 users. They all call on a table of...
20
by: Neil | last post by:
I have an Access 2000 MDB file with a SQL 7 back end. I have a main table with 50,000 records; and I have a selections table with 50,000 records for each machine that uses the database (about...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.