October 4, 2005 at 11:47 am
Hi
we have an environment where we have "weblogic" as the application server through which users connects to the sql serveser database server.
We have only one "sql server" login/user who is used for all the users and when I see the "sp_who2" or any similar functionality in the "login" name I see that single login name and in the "HostName" I see the name/ip for the application server.
Is there a way to really look for the individual user (ip) information that is connecting to the database in this kind of setup. When someone reports a problem it's really difficult to isolate what is coming from that user.
Thanks for all the help.
October 6, 2005 at 7:37 am
You are talking about two types of users. One is sql server user (login), another is your application users.
Your app user information can not captured by sp_who2 or even sql profiler becuase to sql, they are all the same user, i.e. the sql server login.
In my database, all my stored procedures have two predefined parameters, UserID (app user) and LcoaleID. So from profiler i can figure out who is calling the SP. But this cannot solve your problem unless you change your application.
Another possible way is to set context information for your session (connection).
execute SET SET CONTEXT_INFO your user or client machine name. it will be saved in context_info column in sysprocess table. But again it requires to change the code in your app server. It may also affect app server connection pooling (i am not sure).
October 6, 2005 at 3:29 pm
If you are using 'connection pooling' then this is probably not possible. If you are not using connection pooling, then it's time to change the application or look at another vendor's product. Without connection pooling you will soon exceed most normal resource usage levels and 'kill' the database server. That's why connection pools are used for web applicaitons.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
October 7, 2005 at 7:45 am
We had similar situation with our website, we would use one SQL user called "webapp". This is a problem since we didn't know what user was really doing what.
Our solution was to create unique SQL accounts for different applications on the website. For example, LSOnline_USER, IVR_USER, etc. At least, in the future you can narrow down what user.
October 11, 2005 at 2:19 pm
Thanks for all the help.
Unfortunately changing the application or it's architecture is not an option at this time for me. One thing though this is not a public web site application this is a secured application where users can login only if they have a valid application user/passwd.
But after these users are connected to the application server from there, there is only one sql login used for all connections to the database server and when I do a "sp_who2" or aything similar I see the same name in "Hostname" and "login"
I was wondering there might be a way to actually know the machine address/ip for those individual users but it looks like that's not possible.
I will continue to explore and see if I can find some ideas to do this, please let me know if anyone has any more suggestion about this for me.
Again thanks much for all the help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply