|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
XP_CMDSHELL ProblemI've searched through the Newsgroups but have not been able to find a solution to this particular problem. We have a number of SQL servers used by developers who claim ignorance when things go wrong. As a consequence we now take a snap shot of who is using the servers every few minutes. To do this the servers execute a stored procedure to perform a 'netstat -an' using the xp_cmdshell command: exec xp_cmdshell 'netstat -an' - which works perfectly well on all servers. Later in stored procedure each server performs a 'nbtstat -a' against the IP addresses gathered from the execution of the netstat command: exec xp_cmdshell 'nbtstat -a' - which works on some of the servers. I'm sure the problem is permission-related but we're struggling to find where the problem resides. Each SQL server instance runs using the same domain account and all servers are members of the same domain. This domain account is a member of the local admin account on each server. We've tried executing just the 'nbtstat -a' using Query Analyser on each server and some run without issue and some result in the following error message: Failed to access NetBT driver -- NetBT may not be loaded Interestingly, executing just the 'nbtstat' (i.e. without any parameters) using Query Analyser on each server works consistently - giving a list of the available parameters to pass to nbtstat. Just wondering if anyone can give any guidance please. Many thanks. Hi
NetBT is "NetBIOS over TCP/IP", so have you enabled this protocol and not blocked the port (usually 445). John Show quote "Jinx" wrote: > Good people, > > I've searched through the Newsgroups but have not been able to find a > solution to this particular problem. > > We have a number of SQL servers used by developers who claim ignorance when > things go wrong. As a consequence we now take a snap shot of who is using > the servers every few minutes. To do this the servers execute a stored > procedure to perform a 'netstat -an' using the xp_cmdshell command: > exec xp_cmdshell 'netstat -an' > - which works perfectly well on all servers. > > Later in stored procedure each server performs a 'nbtstat -a' against the IP > addresses gathered from the execution of the netstat command: > exec xp_cmdshell 'nbtstat -a' > - which works on some of the servers. > > I'm sure the problem is permission-related but we're struggling to find > where the problem resides. > > Each SQL server instance runs using the same domain account and all servers > are members of the same domain. This domain account is a member of the local > admin account on each server. > > We've tried executing just the 'nbtstat -a' using Query Analyser on each > server and some run without issue and some result in the following error > message: > Failed to access NetBT driver -- NetBT may not be loaded > Interestingly, executing just the 'nbtstat' (i.e. without any parameters) > using Query Analyser on each server works consistently - giving a list of the > available parameters to pass to nbtstat. > > Just wondering if anyone can give any guidance please. > > Many thanks. Hi,
From your description, I understand that: In some Query Analyzers, you found that the statement "exec xp_cmdshell 'nbtstat -a'" was executed with the error: Failed to access NetBT driver. You logon account is a domain user who is added into local administrators group on each host. If I have misunderstood, please let me know. From my research, the execution privilege of this command "exec xp_cmdshell" is related to SQL Server service account. If a windows logon user is not a local administrator but the SQL Server service account is a local administrator, the command can also work. So I recommend that you check the SQL Server service account: 1. Run services.msc 2. Right click the "MSSQLSERVER", select "Properties", and switch to the "Log On" tab. 3. By default the "Log on as" is "Local System account". I recommend that you change the option to "This account" and set the domain account as its start account. 4. Restart the SQL Server service. If you have any other questions or concerns, please feel free to let me know. It's my pleasure to be of assistance. Charles Wang Microsoft Online Community Support ====================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from this issue. ====================================================== This posting is provided "AS IS" with no warranties, and confers no rights. ====================================================== Jinx wrote:
Show quote > Good people, I'm not sure what the solution to this specific problem might be, but I > > I've searched through the Newsgroups but have not been able to find a > solution to this particular problem. > > We have a number of SQL servers used by developers who claim ignorance when > things go wrong. As a consequence we now take a snap shot of who is using > the servers every few minutes. To do this the servers execute a stored > procedure to perform a 'netstat -an' using the xp_cmdshell command: > exec xp_cmdshell 'netstat -an' > - which works perfectly well on all servers. > > Later in stored procedure each server performs a 'nbtstat -a' against the IP > addresses gathered from the execution of the netstat command: > exec xp_cmdshell 'nbtstat -a' > - which works on some of the servers. > > I'm sure the problem is permission-related but we're struggling to find > where the problem resides. > > Each SQL server instance runs using the same domain account and all servers > are members of the same domain. This domain account is a member of the local > admin account on each server. > > We've tried executing just the 'nbtstat -a' using Query Analyser on each > server and some run without issue and some result in the following error > message: > Failed to access NetBT driver -- NetBT may not be loaded > Interestingly, executing just the 'nbtstat' (i.e. without any parameters) > using Query Analyser on each server works consistently - giving a list of the > available parameters to pass to nbtstat. > > Just wondering if anyone can give any guidance please. > > Many thanks. do have a suggestion for an alternate approach. It sounds like you're trying to audit which developers are using the servers at any given point. Have you considered running a continuous server-side trace? We do this, capturing all database activity 24x7, which we then analyze to identify our heaviest queries. It also gives us an audit trail that allows us to see exactly who did what in our databases. See sp_trace_create to get started... Hi
Talking of alternatives... using a source code control system would reduce any dependence on requiring changes to be in a specific database. Also using a log reader may help track down the culprits of unwanted changes. You may also want to reduce permissions for the developers. John Show quote "Tracy McKibben" wrote: > Jinx wrote: > > Good people, > > > > I've searched through the Newsgroups but have not been able to find a > > solution to this particular problem. > > > > We have a number of SQL servers used by developers who claim ignorance when > > things go wrong. As a consequence we now take a snap shot of who is using > > the servers every few minutes. To do this the servers execute a stored > > procedure to perform a 'netstat -an' using the xp_cmdshell command: > > exec xp_cmdshell 'netstat -an' > > - which works perfectly well on all servers. > > > > Later in stored procedure each server performs a 'nbtstat -a' against the IP > > addresses gathered from the execution of the netstat command: > > exec xp_cmdshell 'nbtstat -a' > > - which works on some of the servers. > > > > I'm sure the problem is permission-related but we're struggling to find > > where the problem resides. > > > > Each SQL server instance runs using the same domain account and all servers > > are members of the same domain. This domain account is a member of the local > > admin account on each server. > > > > We've tried executing just the 'nbtstat -a' using Query Analyser on each > > server and some run without issue and some result in the following error > > message: > > Failed to access NetBT driver -- NetBT may not be loaded > > Interestingly, executing just the 'nbtstat' (i.e. without any parameters) > > using Query Analyser on each server works consistently - giving a list of the > > available parameters to pass to nbtstat. > > > > Just wondering if anyone can give any guidance please. > > > > Many thanks. > > I'm not sure what the solution to this specific problem might be, but I > do have a suggestion for an alternate approach. It sounds like you're > trying to audit which developers are using the servers at any given > point. Have you considered running a continuous server-side trace? We > do this, capturing all database activity 24x7, which we then analyze to > identify our heaviest queries. It also gives us an audit trail that > allows us to see exactly who did what in our databases. > > See sp_trace_create to get started... > > > -- > Tracy McKibben > MCDBA > http://www.realsqlguy.com >  |
|||||||||||||||||||||||
Other interesting topics