SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


xp_logininfo not finding user


xp_logininfo not finding user

Author
Message
pdanes
pdanes
Right there with Babe
Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)

Group: General Forum Members
Points: 746 Visits: 1354
I'm using xp_logininfo to find which domain group a user belongs to, and manipulate some controls in the application based on the results. I want to enable/disable various controls, depending on the privilege level of a user, so that a particular action is not possible for a user with insufficient privilege, rather than let them try it and have the app berate them them for the attempt.

All the logic in the app works fine, but xp_logininfo is not locating one of my users. The command:

EXEC xp_logininfo 'nmp\zagorsekka'

or
EXEC xp_logininfo 'nmp\zagorsekka',  'all'



both give me the following error message.


Msg 15404, Level 16, State 11, Procedure xp_logininfo, Line 62
Could not obtain information about Windows NT group/user 'nmp\zagorsekka', error code 0xffff0002.


EXEC xp_logininfo 'nmp\zagorsekka',  'members'


gives me nothing.

The user DOES exist, and has been on this network for years - I just tried logging onto another computer using his credentials - no problem.

The group to which he belongs is defined as a login and is mapped to the database. Also, another user in the same group works fine - returns the account name, type, privilege, mapped login name and permission path with no fuss. Is there something special that needs to be done with this SP? We do have a mirrored domain controller on site, while the main one is in another building across town.
sqlsurfer1
sqlsurfer1
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 94
"I'm using xp_logininfo to find which domain group a user belongs to, and manipulate some controls in the application based on the results." - Why are you using SQL for this since you are going to manipulate the application controls?

Well, I was trying to say that xp_logininfo for me hasn't been accurate in the sense that it returns which group the user is in, but, the user can be in another group connecting to the same instance of SQL which you might miss.

I think there are certain AD commands. I normally use a AD lookup tool, check for the user, the groups they belong to. You could then look if those groups exist in SQL depending on how many groups you may need to look at.
pdanes
pdanes
Right there with Babe
Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)

Group: General Forum Members
Points: 746 Visits: 1354
sqlsurfer101 (8/19/2013)
"I'm using xp_logininfo to find which domain group a user belongs to, and manipulate some controls in the application based on the results." - Why are you using SQL for this since you are going to manipulate the application controls?
Well, because it seemed the most sensible way to do it. All the schemas, roles, logins and permissions are in the database - isn't it reasonable for the app to query the database about such matters? I could do something in the application, in fact, I did have it that way originally, but it seems back-asswards to me. The database should be telling the app what is permitted, not the app deciding for itself.

Well, I was trying to say that xp_logininfo for me hasn't been accurate in the sense that it returns which group the user is in, but, the user can be in another group connecting to the same instance of SQL which you might miss.
The documentation states that it should return all groups of which the user is a member, up to some astronomical limit that I will never approach. Are you telling me that this SP has known incorrect behavior? I found no mention of this while researching how to use it.

And my issue isn't that it reports some other group than the one that might interest me, but that it claims the user doesn't exist at all.

I think there are certain AD commands. I normally use a AD lookup tool, check for the user, the groups they belong to. You could then look if those groups exist in SQL depending on how many groups you may need to look at.
There are, you can even hook into the domain controller as a linked server in SQL Server, but I think you have to have domain admin privileges to use it. At least, when I tried it, I could link to the controller with no problem, but I was unable to execute any queries against it.

What is this AD lookup tool you mention? Is it accessible from SQL Server?
sqlsurfer1
sqlsurfer1
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 94
pdanes (8/19/2013)
I'm using xp_logininfo to find which domain group a user belongs to, and manipulate some controls in the application based on the results. I want to enable/disable various controls, depending on the privilege level of a user, so that a particular action is not possible for a user with insufficient privilege, rather than let them try it and have the app berate them them for the attempt.

I'm sorry, I should've said - xp_logininfo doesn't give you the information unless the account or the group that it is in already has access to the instance the application needs to connect to. i.e - wherever you ran xp_logininfo.
However, I guess you answered that - "The group to which he belongs is defined as a login and is mapped to the database".

When a valid AD account doesn't have access to the instance, I think you get no results, but when the AD account doesn't exist is when you generally get the error 'could not obtain information..' So perhaps I don't know. Could it be that you typed in the name and there is a typo?

"Are you telling me that this SP has known incorrect behavior?" - Maybe not anymore. I did not know about the 'all' parameter. I just tried it. It seems to provide information I need. I had only used xp_logininfo 'domain\user' - that I thought was returning inconsistent info. (We have other custom built means of getting permissions information, so I haven't needed/missed xp_logininfo)

EXEC xp_logininfo 'nmp\username', 'members' -> this needs to be EXEC xp_logininfo 'nmp\groupname', 'members'

All in all, for sanity, my first steps would be to check if there was a typo in the name, and if it is not a typo, to check if the user can access the database even though you mentioned his login is mapped to the database.
pdanes
pdanes
Right there with Babe
Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)

Group: General Forum Members
Points: 746 Visits: 1354
sqlsurfer101 (8/19/2013)
When a valid AD account doesn't have access to the instance, I think you get no results, but when the AD account doesn't exist is when you generally get the error 'could not obtain information..' So perhaps I don't know. Could it be that you typed in the name and there is a typo?
No, I checked it very carefully, and retyped it several times, just in case there might have been a hidden character lurking in there. The name absolutely is correct, but the error persists.

"Are you telling me that this SP has known incorrect behavior?" - Maybe not anymore. I did not know about the 'all' parameter. I just tried it. It seems to provide information I need. I had only used xp_logininfo 'domain\user' - that I thought was returning inconsistent info. (We have other custom built means of getting permissions information, so I haven't needed/missed xp_logininfo)
How do you do it? Is it something I might be able to use?

EXEC xp_logininfo 'nmp\username', 'members' -> this needs to be EXEC xp_logininfo 'nmp\groupname', 'members'

Oops, thank you, good catch. But I just tried it like this:
exec  xp_logininfo 'nmp\paleoces', 'members'

and the two users in this group came up, like so:

NMP\kvacekji user user NMP\kvacekji nmp\paleoces
NMP\zagorsekka user user NMP\zagorsekka nmp\paleoces


I then took the two users again, using copy/paste, just to be certain that I didn't mistype something, and the results are the same:
exec  xp_logininfo 'NMP\zagorsekka'
and
exec xp_logininfo 'NMP\zagorsekka', 'all'

both throw the error, but
exec  xp_logininfo 'NMP\kvacekji'
and
exec xp_logininfo 'NMP\kvacekji', 'all'

both work correctly. And both users can log in to the database.

All in all, for sanity, my first steps would be to check if there was a typo in the name, and if it is not a typo, to check if the user can access the database even though you mentioned his login is mapped to the database.
Well, the name IS correct, and the user CAN log in to the database. Can you think of anything else I might try?
sqlsurfer1
sqlsurfer1
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 94
How do you do it? Is it something I might be able to use?

We capture security data related to databases (db servers) we administer, as to who connects, what access they have, etc. We have an SSIS package, that connects to all servers and pulls the data daily. (Some similar scripts I think are available online too, to get database level permissions and server level permissions.) This is stored in our database and made available via reports too.

Well, the name IS correct, and the user CAN log in to the database. Can you think of anything else I might try?
1)
Well, doubtful on this one since you can retrieve data for one user atleast, but can you please check this link?
http://blog.matticus.net/2009/08/windows-2008-and-xplogininfo.html

2) Is there any error in SQL error log when this xp errors? If there is, I was wondering what the login error state number is?

Thats all I can think of right now.
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15061 Visits: 14396
Sorry pdanes, but using SQL Server as a conduit to AD, especially via xp_logininfo, to control what a user can see or do within an application seems like a bad design to me. Authentication and authorization for an application is traditionally handled within the application tier, not offloaded to the data tier. Just my two cents. If you're coding your application in .NET there are some very robust classes built into the Framework that make it very easy to do what you're trying to do directly from within your application.

If you're in love with the idea of tucking all the AD lookups into your data tier then consider a Linked Server configured to connect to and issue queries against AD. Getting a user's group memberships, or a group's members list, is trivial using a Linked Server.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
pdanes
pdanes
Right there with Babe
Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)

Group: General Forum Members
Points: 746 Visits: 1354
Well, I got it running using
exec  xp_logininfo 'nmp\paleoces', 'members'

and looping to extract all members. That works, although it seems to me it's asking the same question, just in a different way. No idea why one way works and the other way bombs.

However, I got to thinking about the process, and I realized that I'm not really asking the database for permission information, as I originally envisioned myself to be. I'm actually just asking for membership information on the current user from the domain controller. I can get the same information directly in the app - there's no need to burden SQL Server and the database server connection with this, since the decisions I make are solely in the app anyway.

In a nutshell, either the app asks the domain controller for some information, or the app asks SQL Server to ask the domain controller for the same information. In hindsight, the second way is just plain dumb. The logic in the app deals with the information the same way in either case.

So, thank you both for the thoughts. Sometimes I have to explain my reasoning to someone else before I see the error I made.
pdanes
pdanes
Right there with Babe
Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)

Group: General Forum Members
Points: 746 Visits: 1354
opc.three (8/21/2013)
... consider a Linked Server configured to connect to and issue queries against AD. Getting a user's group memberships, or a group's members list, is trivial using a Linked Server.
Despite my deciding to deal with this situation in a different way, the Linked Server approach sounds interesting. As I mentioned in my first response, I did try this method, and created a Linked Server connection to our local domain controller mirror. However, all my attempts to execute queries against this connection came up dry. Do you know what privilege or such I would need to make this work?
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15061 Visits: 14396
pdanes (8/22/2013)
opc.three (8/21/2013)
... consider a Linked Server configured to connect to and issue queries against AD. Getting a user's group memberships, or a group's members list, is trivial using a Linked Server.
Despite my deciding to deal with this situation in a different way, the Linked Server approach sounds interesting. As I mentioned in my first response, I did try this method, and created a Linked Server connection to our local domain controller mirror. However, all my attempts to execute queries against this connection came up dry. Do you know what privilege or such I would need to make this work?

It can be a regular Domain User, but for some reason I have never had luck getting SQL Server to pass along the credentials of the logged in user so I have to explicitly set the creds it uses. Here is the boilerplate script I use to setup an AD Linked Server and a basic test query. In the script change the domain creds and in the query set your DC path, e.g. if you domain was level4.level3.level2.level1 the FROM in the test query would be correct if you were looking for members of group GroupName stored in OU OUName

USE DB
GO

IF EXISTS ( SELECT srv.name
FROM sys.servers srv
WHERE srv.server_id != 0
AND srv.name = N'ADSI' )
EXEC master.dbo.sp_dropserver
@server = N'ADSI',
@droplogins = 'droplogins'
GO

EXEC master.dbo.sp_addlinkedserver
@server = N'ADSI',
@provider = N'ADSDSOObject',
@srvproduct = N'ADSDSOObject'
GO

EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'ADSI',
@useself = N'False',
@locallogin = NULL,
-- THIS IS WHERE YOU PUT IN THE DOMAIN USER CREDS THAT WILL AUTH TO AD
@rmtuser = N'DOMAIN\USER',
@rmtpassword = N'PASSWORD'
-- THIS IS WHERE YOU PUT IN THE DOMAIN USER CREDS THAT WILL AUTH TO AD
;
GO

EXEC master.dbo.sp_serveroption
@server = N'ADSI',
@optname = N'collation compatible',
@optvalue = N'false'
EXEC master.dbo.sp_serveroption
@server = N'ADSI',
@optname = N'data access',
@optvalue = N'true'
EXEC master.dbo.sp_serveroption
@server = N'ADSI',
@optname = N'dist',
@optvalue = N'false'
EXEC master.dbo.sp_serveroption
@server = N'ADSI',
@optname = N'pub',
@optvalue = N'false'
EXEC master.dbo.sp_serveroption
@server = N'ADSI',
@optname = N'rpc',
@optvalue = N'true'
EXEC master.dbo.sp_serveroption
@server = N'ADSI',
@optname = N'rpc out',
@optvalue = N'true'
EXEC master.dbo.sp_serveroption
@server = N'ADSI',
@optname = N'sub',
@optvalue = N'false'
EXEC master.dbo.sp_serveroption
@server = N'ADSI',
@optname = N'connect timeout',
@optvalue = N'0'
EXEC master.dbo.sp_serveroption
@server = N'ADSI',
@optname = N'collation name',
@optvalue = NULL
EXEC master.dbo.sp_serveroption
@server = N'ADSI',
@optname = N'lazy schema validation',
@optvalue = N'false'
EXEC master.dbo.sp_serveroption
@server = N'ADSI',
@optname = N'query timeout',
@optvalue = N'0'
EXEC master.dbo.sp_serveroption
@server = N'ADSI',
@optname = N'use remote collation',
@optvalue = N'true'
EXEC master.dbo.sp_serveroption
@server = N'ADSI',
@optname = N'remote proc transaction promotion',
@optvalue = N'true'
GO

/*
---------------------------------------------------------------------------------------------------------
-- test it out

SELECT *
FROM OPENQUERY(ADSI,
'SELECT sAMAccountName, sn
FROM ''LDAP://DC=level4,DC=level3,DC=level2,DC=level1''
WHERE memberOf=''cn=GroupName,OU=OUName,DC=level4,DC=level3,DC=level2,DC=level1''')
ORDER BY sn;

*/



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search