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

Question

Post reply

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

Stringzz

Ten Centuries

Points: 1266
More actions
June 7, 2011 at 3:36 pm

#239579

In tsql I have a query that is using xp_cmdshell to run "Net User <username> /domain". Some of the global groups that are returned are not the full name. It seems to return a max of 21 characters for the name, so if the name is longer, I only get partial name returned (truncated).
Is there a way to increase the output to get full name?

Viewing 15 posts - 1 through 15 (of 39 total)

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

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

Not that I know of, but have you considered using the ADSI provider to query the data as a linked server?

On most of my systems I disable xp_cmdshell and don't allow its use..

You might look here:

http://codebetter.com/brendantompkins/2003/12/19/create-a-sql-server-view-of-your-ad-users/[/url]

CEWII

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

Elliott Whitlow (6/7/2011)
On most of my systems I disable xp_cmdshell and don't allow its use..

+1000

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

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

opc.three (6/8/2011)
Elliott Whitlow (6/7/2011)
On most of my systems I disable xp_cmdshell and don't allow its use..
+1000

And on the systems where it is allowed (not by my choice, I inherited them) all calls to xp_cmdshell are traced. By policy SQLCLR is to be usedto execute any command shell like apps.

CEWII

Stringzz Ten Centuries Points: 1266 More actions · Answer 4

Elliott Whitlow (6/7/2011)
Not that I know of, but have you considered using the ADSI provider to query the data as a linked server?
On most of my systems I disable xp_cmdshell and don't allow its use..
You might look here:
http://codebetter.com/brendantompkins/2003/12/19/create-a-sql-server-view-of-your-ad-users/[/url]
CEWII

I wouldn't mind using the ADSI provider, but I don't know any LDAP information to provide for the query to return any data

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

Its pretty easy to figure out in most cases. if you look at the example in the article it references ”LDAP://DC=whaever,DC=domain,DC=org” which means their AD domain is whaever.domain.org and their short name for the domain is whaever. Worst case scenario, ask your notworking folks.

CEWII

Stringzz Ten Centuries Points: 1266 More actions · Answer 6

in a large corporation, nothing is easy to find, even finding a network resource would be difficult and time comsuming.

Is there a way to determine the fully qualified AD domain. I know the second and third part, but have no clue on the first

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

If you goto a comand prompt and type SET and hit enter, look for the item: USERDNSDOMAIN that should be the AD domain name. It is for me..

CEWII

Stringzz Ten Centuries Points: 1266 More actions · Answer 8

Thanks, that gave me what I needed, but I still get an error. I created the ADSI linked server successfully, but when I run the query, I get the following error:

USERDNSDOMAIN=CORP.BIZAM.COM

Msg 7321, Level 16, State 2, Line 1

An error occurred while preparing the query "SELECT title

, displayName

, sAMAccountName

, givenName

, telephoneNumber

, facsimileTelephoneNumber

, sn

FROM "LDAP://DC=corp,DC=bizam,DC=com"

WHERE objectClass = "User"" for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 9

opc.three (6/8/2011)
Elliott Whitlow (6/7/2011)
On most of my systems I disable xp_cmdshell and don't allow its use..
+1000

-1000 😉

Why all the FUD about xp_CmdShell? Done properly, it's an incredible tool.

--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)

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 10

Elliott Whitlow (6/7/2011)
Not that I know of, but have you considered using the ADSI provider to query the data as a linked server?
On most of my systems I disable xp_cmdshell and don't allow its use..
You might look here:
http://codebetter.com/brendantompkins/2003/12/19/create-a-sql-server-view-of-your-ad-users/[/url]
CEWII

You do realize that it's possible to run a command prompt from ad hoc queries, correct? Done properly, enabling xp_cmdshell is actually safer than allowing ad hoc queries.

--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)

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

Jeff Moden (6/8/2011)
opc.three (6/8/2011)
Elliott Whitlow (6/7/2011)
On most of my systems I disable xp_cmdshell and don't allow its use..
+1000
-1000 😉
Why all the FUD about xp_CmdShell? Done properly, it's an incredible tool.

No FUD to be found here, at least not about xp_CmdShell. It's the done properly part I have issues with. I have yet to see it be done securely when chosen as an integral part to a solution. For Production/System/Support/Whatever DBA work, fine, but IMHO it does not belong in the DB Developer toolkit. It's tossed around as a panacea when there are perfectly good managed options out there of which this thread is a perfect example. I would liken it to how cursors are used when there are perfectly good set-based solutions available 😉

It's not that xp_CmdShell is inherently bad, I just see it misused, a lot, and in a forum setting like this where folks are seeking out solutions it's likely they are not choosing xp_CmdShell for the right reasons. I see it a lot where they're choosing it as a fallback to get a working solution because they did not know how else to do it. I wish it were an "undocumented feature" so it would be a little more scary and people would think a bit more before they chose it. "Chose" being the key word, because I (setting myself up here) have yet to see a solution where xp_CmdShell was necessary from a development standpoint, i.e. where it was necessary to achieve something that could not be done via SSIS or PowerShell or C# or some other managed application development environment could not handle as well or better.

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

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

Jeff,

I have to fundamentally disagree. xp_cmdshell allows you to run commands in the same context as the SQL server, now depending on the user and the rights you have chosen this could allow a fundamental takeover of the machine. I view it as an apples to oranges comparison to reference ad-hoc queries. When xp_cmdshell was the only game in town you didn't have much choice but to use it, now, I recommend against it if there is almost any other viable method.

I think we may have to agree to disagree..

CEWII

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

Stringzz (6/8/2011)
Thanks, that gave me what I needed, but I still get an error. I created the ADSI linked server successfully, but when I run the query, I get the following error:
USERDNSDOMAIN=CORP.BIZAM.COM
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT title
, displayName
, sAMAccountName
, givenName
, telephoneNumber
, facsimileTelephoneNumber
, sn
FROM "LDAP://DC=corp,DC=bizam,DC=com"
WHERE objectClass = "User"" for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".

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.

CEWII

Stringzz Ten Centuries Points: 1266 More actions · Answer 14

I have tried double quotes and two single qoutes and no quotes. I get the same error. I tested the ADSI linked server and it returned successful.

SELECT * FROM OpenQuery(ADSI, 'SELECT title

, displayName

, sAMAccountName

, givenName

, telephoneNumber

, facsimileTelephoneNumber

, sn

FROM "LDAP://DC=corp,DC=bizam,DC=com"

WHERE objectClass = "User"

')