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

Posted on 2008-11-19
Last Modified: 2012-05-05
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?
Question by:esther_6694
    LVL 4

    Expert Comment

    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.
    LVL 13

    Expert Comment

    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 ?

    Author Comment

    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?

    Author Comment

    kaylanreilor, do yo mean I cannot view the current connection client from SQL Server sysprocesses table?
    LVL 4

    Expert Comment

    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.
    LVL 13

    Expert Comment

    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 ?

    Author Comment

    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?

    LVL 13

    Accepted Solution

    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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Suggested Solutions

    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now