February 5, 2009 at 10:59 am
I am using ADSI to query active directory but I cant seem to find attribute that shows if an account is locked out from the list of available attributes. http://msdn.microsoft.com/en-us/library/ms675090.aspx
Does anybody know of a way to get this info using ADSI. Possibly with a calculation using lockouttime, lockoutduration , and lastlogon but the datatype they are in does not appear to go into sql server well.
Any suggestion will be appreciated. thanks
February 5, 2009 at 12:17 pm
I think you already found the attribute. If lockoutTime is > 0 then the account has been locked out.
February 5, 2009 at 3:12 pm
I cannot just use lockouttime attribute as is because when doing so as in the query below, I receive the following error.
Cannot get the data of the row from the OLE DB provider "ADSDSOObject" for linked server "ADSI".
Could not convert the data value due to reasons other than sign mismatch or overflow.
select objectSid, sAMAccountName, lockoutTime, createTimeStamp
from openquery(ADSI,'select objectSid, sAMAccountName,lockoutTime, createTimeStamp
from ''LDAP://dc=domain,dc=ca''
where objectCategory = ''Person''
and objectClass = ''user''
and sAMAccountName = ''A*'' ' )
order by sAMAccountName
I have tried to cast/convert the lockouttime but receive the same error.
select objectSid, sAMAccountName, convert(varchar(max),lockoutTime)[/size], createTimeStamp
from openquery(ADSI,'select objectSid, sAMAccountName,lockoutTime, createTimeStamp
from ''LDAP://dc=domain,dc=ca''
where objectCategory = ''Person''
and objectClass = ''user''
and sAMAccountName = ''A*'' ' )
order by sAMAccountName
i have also tried to convert on the query within the openquery but receive te following error which leads me to believe that I cant use sql function when querying active directory through ADSI.
select objectSid, sAMAccountName, convert(varchar(max),lockoutTime), createTimeStamp
from openquery(ADSI,'select objectSid, sAMAccountName,convert(varchar(max),lockoutTime), createTimeStamp
from ''LDAP://dc=domain,dc=ca''
where objectCategory = ''Person''
and objectClass = ''user''
and sAMAccountName = ''A*'' ' )
order by sAMAccountName
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "select objectSid, sAMAccountName,convert(varchar(max),lockoutTime), createTimeStamp
from 'LDAP://dc=domain,dc=ca'
where objectCategory = 'Person'
and objectClass = 'user'
and sAMAccountName = 'A*' " for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".
you would have to create a linked server to Active directory to to try the above SQL
EXEC master.dbo.sp_addlinkedserver @server = N'ADSI',
@srvproduct=N'Active Directory Services 2.5',
@provider=N'ADSDSOObject',
@datasrc=N'adsdatasource'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',
@useself=N'False',
@locallogin=NULL,
@rmtuser=N' ',
@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'use remote collation', @optvalue=N'true'
February 5, 2009 at 3:27 pm
I'd have to check this but I'm almost certain that's a limitation of the OLE DB Provider for Microsoft Directory Services. It won't handle muti-valued attributes either.
In that case your options would be to go outside SQL to get the answer, maybe xp_cmdshell to run dsquery, or build a CLR function to query AD using System.DirectoryServices.
February 6, 2009 at 9:42 am
I have just realized that I am trying to find the wrong value. What i wanted it disabled users, not locked users. I have found which attribute has this info (userAccountControl) now i just need to figure out which values are disabled accounts.
February 6, 2009 at 10:12 am
I have figured out how to get disabled users out of the useraccesscontrol value. When converted to binary the second bit signifies whether account is enabled or disabled. So with the help of udf found here, I have written sql to output only disabled users.
select objectSid, sAMAccountName, userAccountControl, createTimeStamp
from openquery(ADSI,'select objectSid, sAMAccountName, userAccountControl, createTimeStamp
from ''LDAP://dc=domain,dc=ca''
where objectCategory = ''Person''
and objectClass = ''user''
' )
where substring(dbo.udf_bin_me(userAccountControl),len(dbo.udf_bin_me(userAccountControl))-1,1) = 1
order by sAMAccountName
February 6, 2009 at 10:20 am
Glad to hear you got it working.
Here's another way to do it using the userAccountControl you mentioned
SELECT [Name], [sAMAccountname],[department],[telephonenumber]
FROM OPENQUERY( ADSI,
'SELECT Name,sAMAccountname,department, telephonenumber
FROM ''LDAP://OU=Users,DC=yourdomain,DC=local''
WHERE objectCategory = ''person'' AND objectClass = ''user'' AND ''userAccountControl:1.2.840.113556.1.4.803:'' = 2')
GO
February 6, 2009 at 12:23 pm
This works great and is better then my solution because it does not need UDF.
Would you happen to have a reference for the numbers after userAccountControl? I am the kind of person that likes to know what is going on instead of just accepting an answer.
February 6, 2009 at 12:27 pm
nevermind I have found it. It is specifying the use of bitwise filter.
for anyone else who is interested
http://support.microsoft.com/kb/269181
thanks for the help.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply