March 11, 2004 at 6:47 am
When I run the query below in Query Analyzer on my MSSQL/2000 server I get a blank nt_username. However, when I connect to another MSSQL/2000 server via a VPN connection and run the same query I get back the nt_username. I have tried connecting via Windows Authentication and SQL Server Connection in Query Analyzer but the results don't change.
Can anyone tell me what I have to do in order to retrieve the nt_username via sysprocesses (or any other system table)?
/* nt_username
The Windows user name running the process if the process is
using Windows Authentication or a trusted connection.
*/
select sysdatabases.name,sysprocesses.hostname,sysprocesses.nt_username,
sysprocesses.login_time,sysprocesses.loginame,sysprocesses.program_name,sysdatabases.dbid
from sysprocesses
inner join
sysdatabases on sysprocesses.dbid=sysdatabases.dbid
where sysprocesses.hostname <> '' and sysdatabases.name like 'Membership%'
order by sysprocesses.hostname
Thanks,
Howard Bock
Bock and Bock
Dallas, TX
March 12, 2004 at 11:36 am
Use the loginame column instead. The nt_username column is only populated when the user id is a Windows user. For SQL Server login id's, the column will be blank.
select CONVERT(varchar(25),d.name) as database_name,
CONVERT(varchar(25),p.loginame) as loginame,
CONVERT(varchar(25),p.hostname) as hostname,
CONVERT(varchar(25),p.nt_username) as nt_username,
p.login_time,
CONVERT(varchar(25),p.program_name) as program_name,
d.dbid
from master.dbo.sysprocesses p
join sysdatabases d on p.dbid = d.dbid
where p.hostname <> ''
and d.name like 'Membership%'
order by p.hostname
To list the Windows login id's, run this query:
select CONVERT(varchar(30),name) as name,
CONVERT(varchar(30),loginname) as loginname,
isntname,
isntuser
from master.dbo.syslogins
where isntname = 1
To list the SQL Server login id's, run this query:
select CONVERT(varchar(30),name) as name,
CONVERT(varchar(30),loginname) as loginname,
isntname,
isntuser
from master.dbo.syslogins
where isntname = 0
Mike
March 12, 2004 at 2:34 pm
Thank you for the detailed response. The information you supplied is very useful. However, I still need to retrieve the nt_username and what I do not understand is why I can retrieve this from the remote server but not from the local server when both server's are running the same application.
The following is a response I got from another user but it did not work:
it's an problem with the network libarys.
only named pipes support the nt usernames. if you use tcp/ip as first
protocol then you cant see the usernames.
the order of libarys can be changed under programms --> sql server -->
network configuration
Any additional ideas will be greately appreciated.
Howard
March 13, 2004 at 12:50 pm
As I mentioned previously, the only time the nt_username column is populated is when the user connects using Integrated Security (Windows login). The ability to login this way is dependant upon how the login is created. Once the user id is created, the mode required for login cannot be changed. The login would have to be dropped and recreated to change the login mode required. So, if a user is assigned only a SQL Server login id, then they cannot login with Integrated Security, and their user id will never appear in the nt_username column.
You didn't say if you verified that the user's who connect to your local server from the application were given Windows login id's (that, the id is mapped to a domain user id).
I'm not sure why you must use the nt_username column versus the loginame column. But just to investigate the problem, if you list the loginame, you will see Windows logins stored in the form domain\userid, while the SQL Server logins will not have a domain (and cannot contain a backslash). I'd do that first. If a domain is present in loginame, but nt_username is blank, then you might indeed have some kind of a problem.
Are both SQL Servers in the same domain?
Just to be clear, only the users listed from the query I posted initially can possibly appear in the nt_username column:
select CONVERT(varchar(30),name) as name,
CONVERT(varchar(30),loginname) as loginname,
isntname,
isntuser
from master.dbo.syslogins
where isntname = 1 -- means Windows (nt) user (has a domin id)
Mike
March 13, 2004 at 1:17 pm
Mike:
Thank you for your response. The reason I want the nt_username is this is the only way I can determine what individuals are logged into the database. The loginname is the same for everyone as this is the name that the application uses to connect to the database.
The application uses a single user name and password. Thus, the loginname is always the same (ex: app_user) for anyone that runs the application.
Therefore, if two individuals (ex: jdoe and asmith - Windows Authentication) run the application the loginname will be the same for both (ex: app_user) but the nt_username will be different (ex: jdoe and asmith). If I just saw the loginname of 'app_user' I could not tell that 'jdoe' and 'asmith' where the ones logged into the database. I hope that this makes sense to you.
Each server is on a different domain.
Thank you for your help in this matter.
Howard
March 14, 2004 at 7:07 am
That's the problem, Howard. Your application logs in using standard SQL Security, using the same username for all users. In that case, nt_username column will be NOT be populated. The information you desire cannot be obtained under your current conditions.
The conditions required for the population of the nt_username column are:
- The user is a member of a windows domain.
- The SQL Server is a member of the same or a trusted domain.
- The domain user name is added to the SQL Server as a Windows user (see sp_grantlogin).
- The application logs into SQL Server using Integrated Security (a username and password are not specified).
The closest you can get to identifying the user is to use the hostname column to identify the machine that is used to login to the database.
If the users are all member of different domains, and a trust relationship does not exist among all of the domains and the SQL Server domain, then you can't use nt_username.
Then, to use the loginame column, you would have to assign each user their own SQL Server login (see sp_addlogin).
Mike
March 14, 2004 at 1:28 pm
Mike:
You said,"That's the problem, Howard. Your application logs in using standard SQL Security, using the same username for all users. In that case, nt_username column will be NOT be populated. The information you desire cannot be obtained under your current conditions".
You are correct in that the application logs in all users using standard SQL Security, using the same username and password for all users. However, on one server the nt_username is returned. On another identical server, my server, the nt_username is NOT returned.
I am obviously overlooking something, but I cannot figure out what it is.
Howard
March 15, 2004 at 6:00 am
Do you know how the application logs in? Some applications try to first log in with Integrated Security, and if that fails, log in with a standard SQL Server login. Maybe the users that log into the server in which nt_username is populated are in the same (or a trusted) domain as the SQL Server and are being logged in using Windows security.
You need to examine the list of users in the database for each server. You can use the queries I provided previously, or use Enterprise Manager. Expand the server name, then Databases, then the database name, then click on users. The domain users (and thus the only usernames that will appear in the nt_username column) will have the domain append to the beginning (domain\username).
Or, under the server name in Enterprise Manager, look in Security, then Logins. The "Type" column will say Standard, Windows User, or Windows Group.
Only the user id's that are "Windows User" or "Windows Group" can login with Integrated Security, and thus appear in the nt_username column.
Ask the following questions.
- What domain is the local server a member of?
- What domain is the remote server a member of?
- What domain are all of the database user a member of?
- How are the user domains related to both of the server domains (trusts)?
- Do all users use the same version of the application? How does the application login (what is the login algorithm)? Does it allow for both Integrated and standard SQL logins?
- Are users logging into their PC's with their domain userid and password or with a local account?
Mike
March 15, 2004 at 7:45 am
Mike, listed below are your questions followed by my answers.
-Do you know how the application logs in?
Standard SQL Server login.
-Expand the server name, then Databases, then the database name, then click on users. The domain users (and thus the only usernames that will appear in the nt_username column) will have the domain append to the beginning (domain\username).
Each server displays the same information in Enterprise Manager:
Name: app_user
Login Name: app_user
Database Access: Permit
There is a difference for dbo. On the remote server dbo login name is DomainA\Administrator and on the local server dbo login name is blank.
- Or, under the server name in Enterprise Manager, look in Security, then Logins. The "Type" column will say Standard, Windows User, or Windows Group.
Each server displays the same information in Enterprise Manager:
Name: app_user
Type: Standard
Server Access: Permit
Default Database: Accounting
Default Language: English
- What domain is the local server a member of?
domain01
- What domain is the remote server a member of?
DomainA
- What domain are all of the database user a member of?
Each domain is separate with its own SQL Server and NT users. The NT users are NOT in SQL Server either as Users under the Database or as Logins under Databases Security.
- How are the user domains related to both of the server domains (trusts)?
The domains are totally separate. I connect to the remote server using Query Analyzer via a vpn dsl connection.
- Do all users use the same version of the application? How does the application login (what is the login algorithm)? Does it allow for both Integrated and standard SQL logins?
All users use the same version of the application. It uses standard SQL logins.
- Are users logging into their PC's with their domain userid and password or with a local account?
All users log into their PC’s with their NT username and password.
I appreciate the time you are spending on resolving this problem. I hope that I have clearly answered all your questions.
Howard
March 15, 2004 at 11:36 am
Well, given the situation you've described, the nt_username should not be populated on either server, if the application is run as you described. In fact, it cannot be populated that way.
Again, if a user logs in using standard SQL Security, that is, by providing a SQL Server user id and password, then nt_username will be blank. You said your application logs the user in with SQL Server login id 'app_user' and it's associated password.
If the sysprocesses table on the server you are accessing through the VPN connection lists an nt_username, then those particular processes did NOT login with SQL Security - they logged in using Windows Security. Are you sure those processes are created from the application? Can users connect to the database some other way?
With a setup like you describe, with an application using one shared SQL Server login id to log in all users - there really is no way track individual users. Like I said before, the best you can do right now is get the name of each workstation.
March 15, 2004 at 2:37 pm
Mike:
I understand what you are saying but I can still find no differences between the two servers with the following exception:
select * from sysprocesses on the remote server returns the nt_username and 'Named Pipes' as the net_library.
select * from sysprocesses on the local server does not return the nt_username and 'TCP/IP' as the net_library.
The following is a response I got from another user but it did not work:
"it's an problem with the network libarys.
only named pipes support the nt usernames. if you use tcp/ip as first
protocol then you cant see the usernames.
the order of libarys can be changed under programms --> sql server -->
network configuration"
Is it possible that the Named Pipes and TCP/IP Net Library has something to do with this?
Howard
March 16, 2004 at 7:02 am
I ran some tests where I changed the network libraries, and under one set of conditions, nt_username is populated with my domain user id and loginame is populated with my SQL Server login ID.
I ran the Client network configuration utility and moved Named Pipes to the top of the list. I then logged into Query Analyzer requesting SQL Server authentication. When I examined sysprocesses, the nt_domain column contained the name of the domain my workstation was logged into, and the nt_username column contained my domain user id. The network library used was Named Pipes. The loginame column contained the SQL Server login id I specified when I logged into Query Analyzer.
However, when I checked the SQL Server log, it indicated my domain user id had logged in with a trusted connection (i.e. with Windows authentication).
Apparently, when Named Pipes in used, Windows Authentication will occur if possible regardless of the connection type specified.
My SQL servers support both TCP/IP and Named Pipes connections.
My tests involved changing the Client configuration only. Maybe the clients who connect to the other server are configured for Names Pipes only, or with Named Pipes listed first. Or, maybe the server is configured for named pipes only.
Note that the network library order applies only to clients.
Mike
March 16, 2004 at 7:27 am
Mike:
I believe that you are on to something. However, I believe that it has to do with the Server and not with Client.
You said “your tests involved changing the Client configuration only”. Was this on the Client or on the Server? The remote clients do not have the client Network Utility installed on their PC’s. I do have it installed on my PC.
You said, “Maybe the clients who connect to the other server are configured for Names Pipes only. Or, maybe the server is configured for named pipes only.” I believe that it is the Server configuration that is coming into play here. Can you tell me how to make the change on my Server to Named Pipes to see if this solves the issue?
When I run Client Network Utility on both the remote and local servers I see the same results. Under the General Tab: Enabled protocols by order: TCP/IP and Named Pipes. Under the Network Libraries tab: Named Pipes is listed first followed by TCP/IP, etc.
Also, do you have a recommendation on which net_library is best to use?
Howard
March 16, 2004 at 7:56 am
Log on to the server and run the Server Network Utility ( svrnetcn.exe ). By default, you should see Named Pipes and TCP/IP. Again, the order on the server is not relevant, because these are simply the list of network libraries available for clients to connect to. The client decides which library to use.
You could disable TCP/IP, leaving only named pipes, but if another process or client requires TCP/IP, those processes will cease to function (or not start at a later time). This could be a dangerous step on a production server. Of course, you could re-enable TCP/IP and restart those processes, if necessary.
The Server Network Utility is used to provide the available net-libs.
The client then must use one of those net-libs to connect to the server. The order of the net-libs listed the Client Network Utility is the order in which a connection will be attempted. So if Named Pipes comes first, a Named Pipes connection will be attempted first. If that fails, then TCP/IP would be attempted next.
As an example, assume the SQL Server allows only Names Pipes. A client on another machine tries to log in using Query Analyzer. That client is configured for TCP/IP, then Names Pipes. TCP/IP will not be used because it is not supported on the server. A Named Pipes connection will be made. Now, the same client logs into another SQL Server that supports both TCP/IP and Named Pipes. Since the client is configured to attempt TCP/IP first, the connection will be made using TCP/IP.
As far as using Named Pipes versus TCP/IP, the primary reason is that for larger networks, and networks over slower links, TCP/IP is generally faster.
Mike
March 16, 2004 at 8:12 am
Mike:
svrnetcn.exe is on the remote server but not on the local server. Do you know how I can load the program onto my server?
Howard
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply