How to verify user's login and IP with SQL Authentication?

I have SQL server 2000 with SQL Authentication. Can I verify user's
login and IP when he is connecting to the server. Our network doesn't
use Domain. All what I want is just - "User can logging only from his
computer".
ThanksI don't know of a simple way of getting hold of the IP address of the
client - I suspect if there is a means, it's not particularly nice.
You can get the name of the client's PC, using HOST_NAME(), SUSER_SNAME()
will get you the login.
"mildlimit" <mildlimit@.gmail.com> wrote in message
news:42e7dba4-bdb1-40d7-8d36-515978fa96ac@.d21g2000prf.googlegroups.com...
>I have SQL server 2000 with SQL Authentication. Can I verify user's
> login and IP when he is connecting to the server. Our network doesn't
> use Domain. All what I want is just - "User can logging only from his
> computer".
> Thanks|||A bit of searching turned up this:
http://www.codeprof.com/dev-archive/33/19-85-338850.shtm
...I would advise considering an upgrade to 2005 if and when you can - the
fact that you can get the IP address from the connections DMV is just one of
the wonders that the DMVs/DMFs can work!
"mildlimit" <mildlimit@.gmail.com> wrote in message
news:42e7dba4-bdb1-40d7-8d36-515978fa96ac@.d21g2000prf.googlegroups.com...
>I have SQL server 2000 with SQL Authentication. Can I verify user's
> login and IP when he is connecting to the server. Our network doesn't
> use Domain. All what I want is just - "User can logging only from his
> computer".
> Thanks|||2 Will Alber
Thanks for link. Unfortunately we can't upgrate to 2005 8( But how to
retrieve this information from moment when user is trying to connect.
Maybe I need to write DLL (I know C++ well) or trigger on that event.
This is very important for me, because it touches on the security of
our network.|||SQL Server 2005 (sp2) has Logon Triggers, from which you would validate these things (from instance
using a SQLCLR function). But 2000 doesn't have such. So you either would have to do this in the
client app when connecting or have some polling mechanism which validates and kicks out non-OK
connections.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"mildlimit" <mildlimit@.gmail.com> wrote in message
news:1676cc54-fa20-43f5-a078-326134234d62@.k39g2000hsf.googlegroups.com...
>2 Will Alber
> Thanks for link. Unfortunately we can't upgrate to 2005 8( But how to
> retrieve this information from moment when user is trying to connect.
> Maybe I need to write DLL (I know C++ well) or trigger on that event.
> This is very important for me, because it touches on the security of
> our network.|||2 Tibor Karaszi
Understood. I was have one's suggestion from community. It's including
setup a middle level (ISA server - proxy) between a user and SQL
Server. Of course I can analysing IP address, but how to get SQL
login? And where I can read about installing such thing?|||I think the easy and best solution is to turn on windows firewall (or
another firewall) and create a rule to allow connections only from the
computer you want. SQL Server is not built to act as a TCP/IP request
filter.
--
Rubén Garrigós
Solid Quality Mentors
"mildlimit" <mildlimit@.gmail.com> wrote in message
news:42e7dba4-bdb1-40d7-8d36-515978fa96ac@.d21g2000prf.googlegroups.com...
>I have SQL server 2000 with SQL Authentication. Can I verify user's
> login and IP when he is connecting to the server. Our network doesn't
> use Domain. All what I want is just - "User can logging only from his
> computer".
> Thanks