View current connection in SQL Server with a ADO.NET application

I have an web application with backend SQL Server 2005. I would like to view the current connections of the web applictaion to the sql server with client computer name.

can I ask how to do it?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Connection String are defined in web.config, like
<add name="conn" connectionString="Data Source=server\SQLEXPRESS;Initial Catalog=db;User ID=uid;Password=pwd" providerName="System.Data.SqlClient" />
where datasource=<server name> is the place where database is. I am not sure if you are looking for this. Could you explain your question.
Do you mean displaying the SqlConnection.ConnectionString on a web page ?
I miss something because as far as I know the "client" computer name has nothing to do with the connection the SQL Server since this connection is supposed to occur between aspnet_wp.exe and the Sql Server on the Web Server side, not from the client computer with ASP... Or perhaps you should provide more info about your Web App ?
esther_6694Author Commented:
I would like to how can I view the current connection to sql server through my application.

For example, if there are 3 client PC connection to my sql server database thru the web application, can I view their connection information (i.e. computer name, process ID etc) in the SQL Server system table?
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

esther_6694Author Commented:
kaylanreilor, do yo mean I cannot view the current connection client from SQL Server sysprocesses table?
oh ok, I get your question now, like a monitoring system. I dont have a answer, will educate my self, when a answer comes up.
Yes I do.
When the code behind of an ASPX page is compiled it's happening on the Web Server machine and the executiotn of the code is hosted in the aspnet_wp process. So that when you write something like "SqlConnection cnx = new SqlConnection(myconnectionstring);" in the method of your page class the SQL Server reserves and gives the handle of a connection object from one of its connection pool to the process on the Web Server machine. That means that you should monitor this ASP.Net working process to know how many connections are currently used to this SQL Server.
If I'm wrong that mean that you're probably executing some scripts on the client side and then I would say : why are we talking about ASP. Because then, it is not compliant with what ASP (Active Server Page) proposes (even if it is still possible).
Notice that, generally you are connecting to a SQL server to deal with a DB inside a method of page to close this connection before the end of this method which should be a small sequence of statement. I mean that when you create a SqlConnection object you open the connection as late as possible and close it as early as possible. When closing the connection, the connection object that your code has been using is simply returned to the connection pool of the SQL Server. Thus connection that are consumed by a Web Aplication even when several requests come from several client browsers at a time are existing for a very short amount of time when the Web App is well designed.
OR, you could have your Web Application hosted by a Web Farm. Nevertheless, here again what you call "client machines" will simply be the Web Servers machines.
So you'd better try to figure out how to monitor ASP>Net itself or even the Sql Server. If the client machines are really performing the connection themselves through some strange scripting or ActiveX that the users would have to had allowed, then you'd better dig the question through monitoring the connections on the SQL Server side.
Actually I think we miss some info on your Web App.
"if there are 3 client PC connection to my sql server database thru the web application, can I view their connection information (i.e. computer name, process ID etc)"
=> It's probably better to add some code on your pages to try to monitor the current requests. I don't see what process ID you could get from the client side : what is the targeted process ? IExplorer (OK, or opera, firefox, safari...) ? If you really need to monitor processes you can use System.Diagnostics.Process (BTW you have a MachineName property with this class). If you want to monitor ASP.Net you can use System.Web.ProcessModelInfo along with the ProcessInfo class.
I think that you should be clear about the purpose of your question. Is it a performance issue ?
esther_6694Author Commented:
thanks kaylanreilor.
actually I would like to use the connected Client's PC name (log by SQL Server) + Logged in User ID (log by ASP application) to create DB View. The logic is:

User_Company (to store the access right for each user)
UserID    AccessCompany
A            CompanyX
A            CompanyY
B            CompanyX

LogOn_Info (to store logon info)
UserID      PCName      LoginTime
A              PC123         yyyy-mm-dd hh:mm:ss
B              PC456         yyyy-mm-dd hh:mm:ss

I just wonder if the SQL Server can provide the connected host name for each connected user, so that I can prepare the DB View to identify the authoried company access right for each user.

for example, if the connected client with PC123 is logged in SQL Server, My view can select the authorized company info (CompanyA & CompanyB) to A.

Is there any workaround?

1. You're connection code to the DB is in the code behind of an ASPX page , right ? So SQL Server, as I tried to explained previously, won't be abble to know the "client" machine since it is the Web Server machine (where your web app is hosted) which will perform the connection. So I think we agree on that. So "SQL Server can provide the connected host name for each connected user" cannot happen. You APPLICATION can. So you have to handle this problem with your logon policy. You can for example store its logon information with the Session state.
2. You can create one connection string for each user before connecting to SQL Server. You can pass the autentication info in the connection string so that each different user will be connecting with her own identity... which is not good at all for performance issue. Because in that case each user connecting to the BD will force SQL Server to create a new pool of connection object. Generally the Application manage the identification issues and connect to a DB with the same connection string for everyone so that SQL Server only have to create ONE pool of connection object. Remember that when you write "mySqlConnection.Open()" SQL Server give you a handle of one of its objects in the previously pool created and when you write "mySqlConnection.Close()" your application gives this handle back to SQL Server but the connection object is not destroyed.
3. Forget about identification of the "client" machine in your case because the same user may connect from different machines. Whenever you wanna get some info about the machine, you have to code a script or an activeX (supposing that the client browser allows scripts execution or ActiveX) that will be executed on the client side. Again, an ASPX page code is executed on the Web Server machine. Nevertheless, lients sending HTTP requests do include client IP addresses (so the server knows where to send the response) and various other fields, many of which are exposed as properties of the HttpRequest object, or can be accessed via the HttpRequest.ServerVariables collection property => check these members (especially UserHostAddress or UserHostName) :
BTW, in the case of a script (which is not a good solution because some user may have turn off the options of their browser) I found this following snipped code... if you want to have a look but I doub you should dig this.

To summarize, or you still go with your idea of base your app on the client machine name or we're talking about a whole chapter in the big book on "How to develop a Web App with ASP.Net". I recommend you read a tutorial. I personnally like this one :
Don't be affraid, I swear that you'll find many answer after less than one hour. I really recommend that you read this series (reference at the very beginning of the page).
You can also read this good one : But that also suppose that you enhance you Web App by adding a WCF service to your architecture (which is always recommended when accessing to a DB to develop the business layer part).
Maybe this article ( is more straightforward but I really recommend that you avoid autentication based on forms. For performance issues you should base the identification on Windows Authentication.

If you think we can talk about Session state and role in web.config files for navigation...
    <title>WMI Scripting HTML</title>
    <script FOR="foo" EVENT="OnCompleted(hResult,pErrorObject, pAsyncContext)" LANGUAGE="JScript">
    <script FOR="foo" EVENT="OnObjectReady(objObject,objAsyncContext)" LANGUAGE="JScript">
        if(objObject.IPEnabled != null && objObject.IPEnabled != "undefined" && objObject.IPEnabled == true)
            if(objObject.MACAddress != null && objObject.MACAddress != "undefined")
                MACAddr = objObject.MACAddress;
            if(objObject.IPEnabled && objObject.IPAddress(0) != null && objObject.IPAddress(0) != "undefined")
                IPAddr = objObject.IPAddress(0);
            if(objObject.DNSHostName != null && objObject.DNSHostName != "undefined")
                sDNSName = objObject.DNSHostName;
    <object classid="CLSID:76A64158-CB41-11D1-8B02-00600806D9B6" id="locator" VIEWASTEXT></object>
    <object classid="CLSID:75718C9A-F029-11d1-A1AC-00C04FB6C223" id="foo"></object>
    <script LANGUAGE="JScript">
        var service = locator.ConnectServer();
        var MACAddr ;
        var IPAddr ;
        var DomainAddr;
        var sDNSName;
        service.InstancesOfAsync(foo, 'Win32_NetworkAdapterConfiguration');
    <form method="POST" action="NICPost.asp" id="formfoo" name="formbar">
        <input type="text"  name="txtMACAddr">
        <input type="text"  name="txtIPAddr">
        <input type="text"  name="txtDNSName">

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.