June 7, 2011 at 3:36 pm
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?
June 7, 2011 at 4:00 pm
June 8, 2011 at 11:07 am
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
June 8, 2011 at 11:17 am
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
June 8, 2011 at 3:02 pm
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
June 8, 2011 at 3:16 pm
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
June 8, 2011 at 3:38 pm
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
June 8, 2011 at 4:24 pm
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
June 8, 2011 at 5:16 pm
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".
June 8, 2011 at 10:40 pm
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
June 9, 2011 at 4:21 am
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
June 9, 2011 at 7:18 am
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
June 9, 2011 at 9:41 am
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
June 9, 2011 at 9:43 am
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
June 9, 2011 at 10:20 am
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"
')
Viewing 15 posts - 1 through 15 (of 39 total)
You must be logged in to reply to this topic. Login to reply