August 19, 2013 at 11:47 am
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.
August 19, 2013 at 1:37 pm
"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.
August 19, 2013 at 2:04 pm
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?
August 19, 2013 at 4:33 pm
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.
August 20, 2013 at 12:10 am
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?
August 21, 2013 at 12:19 am
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.
August 21, 2013 at 12:47 am
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
August 22, 2013 at 4:58 am
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.
August 22, 2013 at 5:03 am
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?
August 22, 2013 at 12:05 pm
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply