October 27, 2003 at 7:10 am
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
October 27, 2003 at 9:02 am
AFAIK when you use SQL authentication mode, SQL Server will not be aware of NT accounts.
Frank
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]
October 27, 2003 at 9:11 am
Frank,
But if I use "Named Pipes", it can show this info !
Jan
October 27, 2003 at 9:38 am
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
K. Brian Kelley
@kbriankelley
October 27, 2003 at 9:40 am
thx !
Is there any way fo still collect this data. Or should I write a Custom Extended Stored Proc who could collect this data ?
October 27, 2003 at 10:04 am
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
K. Brian Kelley
@kbriankelley
October 27, 2003 at 10:08 am
No reason ... except for historical inheritance 😉 And is impossible to change this 🙁
October 28, 2003 at 12:18 am
Any chance to collect this information from the client app(s)?
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
October 28, 2003 at 5:12 am
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...
October 28, 2003 at 5:51 am
what do you need the user info for? You can always use suser_sname() to get the name.
* Noel
October 28, 2003 at 5:54 am
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
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]
October 28, 2003 at 5:56 am
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)
October 28, 2003 at 7:04 am
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
K. Brian Kelley
@kbriankelley
October 28, 2003 at 7:12 am
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 ?
October 28, 2003 at 10:26 am
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