Using xp_cmdshell to query active directory, global group names are truncated in results

Question

Using xp_cmdshell to query active directory, global group names are truncated in results

Viewing 10 posts - 31 through 39 (of 39 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 1

Elliott Whitlow (6/14/2011)
Jeff,
I've reviewed that code and as I read it, you have to take advantage of a hack that allows you to use xp_regwrite as a non-privileged user coupled with an exploit available in the jet drivers (and potentially the ACE drivers). That was actually fairly cool and I learned something new. I'll have look a bit deeper into that xp_regwrite exploit since I can't find anything on it.
I did have one question, did you really need to add the linked server, it doesn't appear to be used at all?
CEWII

I don't remember if the linked server creation is actually necessary and I was in a bit of a hurry when I posted the code so didn't test it.

The hack for the write to the registry without the use of xp_regwrite is actually a bit of precompiled code that's executed via an EXEC statement. Like I said, though, that code is so dangerous that I deleted the only copy of the code I had. I just don't want to take a chance on it getting out.

I also agree that the chances of someone actually performing the low-priv hack are very slim but I just want people to know that it can be done. I especially wanted people to know that just because xp_CmdShell isn't enabled doesn't mean that a priv'd user couldn't do something with the CMD module.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Stringzz Ten Centuries Points: 1266 More actions · Answer 2

Was there any determination on the query syntax?

Can you send your actual command, it looks like you were using the command from the article. I'm just wondering if perhaps there are too many double quotes.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

Stringzz (6/14/2011)
Was there any determination on the query syntax?
Can you send your actual command, it looks like you were using the command from the article. I'm just wondering if perhaps there are too many double quotes.

Sorry, man, got hung up and lost ya.. I did, replace the double quotes with 2 single quotes.

Now it gets tricky, if you have more than 901 users you will get an error when it hits the 902nd. But I have already done the research for you on that and the resolution for that is found at the bottom of this post, 2nd from the bottom:

http://social.msdn.microsoft.com/Forums/en-US/databasedesign/thread/5592cf59-0ae8-43a5-bf71-10e978af5976/[/url]

CEWII

Stringzz Ten Centuries Points: 1266 More actions · Answer 4

I replaced the double quotes with two single quotes. I also did a TOP 10 and I am still getting the same error

Msg 7321, Level 16, State 2, Line 10

An error occurred while preparing the query "SELECT top 10 title

, displayName

, sAMAccountName

, givenName

, telephoneNumber

, facsimileTelephoneNumber

, sn

FROM 'LDAP://CN=Users,DC=corp,DC=mybusiness,DC=com'

WHERE objectClass = 'User'

" for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 5

Two things I would confirm:

> Is your LDAP string is correct? See this post.

> Do you have permission to query that data in Active Directory?

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

opc.three (6/20/2011)
> Is your LDAP string is correct? See this post.

If you get this error:

Just delete the "refresh" line (second line) in the script so it looks like this:

Set objSysInfo = CreateObject("ADSystemInfo")

WScript.Echo "User name: " & objSysInfo.UserName

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

Stringzz Ten Centuries Points: 1266 More actions · Answer 7

Thanks for the script, I did try the LDAP string that was returned for the script. I still get the same error. I would think I would have permission since when I test the linked server it returns correctly. But maybe that is more like a ping than permissions test?

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 8

I just set up the linked server on my side and found that the LDAP query did not work when providing TOP.

This worked for me:

SELECT *

FROM OPENQUERY(ADSI, 'SELECT *

FROM ''LDAP://OU=users,OU=AAA,OU=BBB,DC=us,DC=global,DC=some_name,DC=com''

WHERE objectCategory = ''Person''

AND objectClass= ''user''')

This did not:

SELECT *

FROM OPENQUERY(ADSI, 'SELECT TOP 10 *

FROM ''LDAP://OU=users,OU=AAA,OU=BBB,DC=us,DC=global,DC=some_name,DC=com''

WHERE objectCategory = ''Person''

AND objectClass= ''user''')

My environment setup with environment-specific / optional items bolded:

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 sys.sp_addlinkedserver

@server = N'ADSI',

@srvproduct = N'',

@provider = N'ADSDSOObject',

@datasrc = N'' ;

EXEC sys.sp_addlinkedsrvlogin

@rmtsrvname = N'ADSI',

@useself = false,

@locallogin = NULL,

@rmtuser = N'mydomain\mylogin',

@rmtpassword = N'my_domain_password' ;

GO

EDIT: also confirming that I only got 1000 rows returned from the one that worked

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

Stringzz Ten Centuries Points: 1266 More actions · Answer 9

Awesome that worked! The missing piece of the puzzle was creating the login with my credentials.

You can use the TOP clause on the outer select, not the inner select.

Now, how can I include group names in this query? So I can display all groups a user belongs too.

I found a list of all Active Directory attributes, but it is a huge list, and I found now group attribute as of yet.

http://msdn.microsoft.com/en-us/library/ms675090

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 10

Stringzz (6/21/2011)
Awesome that worked! The missing piece of the puzzle was creating the login with my credentials.
You can use the TOP clause on the outer select, not the inner select.
Now, how can I include group names in this query? So I can display all groups a user belongs too.
I found a list of all Active Directory attributes, but it is a huge list, and I found now group attribute as of yet.
http://msdn.microsoft.com/en-us/library/ms675090

Awesome 😎

You're on your own with the actual query piece...AD/LDAP is a bit over my head at this point, good luck 🙂

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