sysprocesses::nt_username Empty

  • The column nt_username is always empty when my users connect with TCP/IP. When they connect through Named Pipes the column is filled in. Any idea how I can still retrieve the Windows NT Account ? (they connect via a SQL Connection)

    Thanks for any help.

    Jan

  • AFAIK when you use SQL authentication mode, SQL Server will not be aware of NT accounts.

    Frank

    http://www.insidesql.de

    Edited by - Frank Kalis on 10/27/2003 09:03:18 AM

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Frank,

    But if I use "Named Pipes", it can show this info !

    Jan

  • That's because Named Pipes is a communication process which is requiring authentication. You're getting the benefit of that in SQL Server as a result. TCP/IP, on the other hand, doesn't require a windows authenticated user and therefore the field won't be populated.

    K. Brian Kelley

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/

    K. Brian Kelley
    @kbriankelley

  • thx !

    Is there any way fo still collect this data. Or should I write a Custom Extended Stored Proc who could collect this data ?

  • Custom extended stored procedure probably won't collect this information... is there a reason you aren't using Windows authentication?

    K. Brian Kelley

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/

    K. Brian Kelley
    @kbriankelley

  • No reason ... except for historical inheritance 😉 And is impossible to change this 🙁

  • Any chance to collect this information from the client app(s)?

    Frank

    http://www.insidesql.de

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • If you can get the client IP, then you could write an XP to resolve this to a MACHINENAME. If you have a 1-to-1 relationship between machines and users, then you can effectively get the username. If you have roaming users or dynamic IP's, this won't work though...

  • what do you need the user info for? You can always use suser_sname() to get the name.


    * Noel

  • quote:


    what do you need the user info for? You can always use suser_sname() to get the name.


    ??? with sql authentication, how will you get information with SUSER_SNAME() about NT logins or NT groups?

    Frank

    http://www.insidesql.de

    Edited by - Frank Kalis on 10/28/2003 06:13:37 AM

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • I need to query the sysprocesses table and store this info into 1 central server, to check who is connected to my remote servers (± 500)

  • Here's the problem you run into... you only are guaranteed a Windows username is you're using Windows authentication. Named Pipes happens to be a communications mechanism that's requiring authentication.

    Keep in mind that if you have a SQL Server connection, there is no guarantee you're coming from a Windows machine. There are JDBC and ODBC drivers on the *nixes that could connect in via a SQL Server login using TCP/IP. Also, I believe the Sybase driver can be used, as can OpenTDS.

    This isn't to say your users are coming from the *nixes, just to point out that if you aren't using Windows authentication there's no guarantee.

    K. Brian Kelley

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/

    K. Brian Kelley
    @kbriankelley

  • You're right, but in my case. I know for sure they are authenticated on a WinNT4 PDC.

    Is there any other way to find more information on the users connected to the server at a certain time ?

  • I ran into a similar issue in an intranet app. Because the IP addresses are not static, there is not a 1:1 relationship. I had to do a dns lookup, code for which follows. Perhaps there is something here you can adapt.

    <%'page variable is set above the include for this page

    'the format for the include would be something like this:

    'dim strPage

    'page = "What you want displayed as page info" (up to 150 characters)

    '<!--#Include Virtual="Inc_remote_host.asp"-->

    Function NSlookup(strHost)

    dim oshell

    'Create Shell Object

    Set oShell = Server.CreateObject("Wscript.Shell")

    'Run NSLookup via Command Prompt

    'Dump Results into a temp text file

    oShell.Run "%ComSpec% /c nslookup " & strHost _

    & "> C:\" & strHost & ".txt", 0, True

    Set oFS = Server.CreateObject("Scripting.FileSystemObject")

    Set oTF = oFS.OpenTextFile("C:\" & strHost & ".txt")

    tempData = Null

    Data = Null

    i = 0

    Do While Not oTF.AtEndOfStream

    Data = Trim(oTF.Readline)

    If i > 2 Then ' Don't want to display local DNS Info.

    tempData = tempData & Data & " "

    End If

    i = (i + 1)

    Loop

    oTF.Close

    oFS.DeleteFile "C:\" & strHost & ".txt"

    Set oFS = Nothing

    nsLookup = tempData

    End Function

    %>

    <%

    dim strIP

    dim strComputerName

    dim strLogDate

    dim strConn2

    dim objConn2

    strConn2 = "dsn=IntranetProdAlerts;uid=;pwd=;"

    strConn2 = strConn2 & "DATABASE=ProdAlerts;APP=ASP Script"

    Set objConn2 = Server.CreateObject("ADODB.Connection")

    objConn2.Open strConn2

    strIP = Request.ServerVariables("REMOTE_ADDR")

    strReturn = nsLookup(strIP)

    If strReturn <> "" Then

    strComputerName=RTrim(strReturn)

    Else

    ' A Lame Host is any Valid Host that DNS Cannot Resolve

    ' See InterNic for Details

    strComputerName="Lame Host - Could Not Resolve DNS For " & strIP

    End If

    strIP=replace(strIP,"'","''")

    strComputerName=replace(TRIM(strComputerName),"'","''")

    strSQL = "Insert Into tblUsage (ip,ComputerName, page, logdate)"

    strSQL = strSQL & "Values('" & strIP & "', '" & strComputerName & "', '"

    strSQL = strSQL & strPage & "', '" & now() & "')"

    'response.write(strsql)

    set rsRemoteHost=objConn2.execute(strSQL)

    set rsRemoteHost=nothing

    objconn2.close

    set objConn2=nothing

    %>

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply