Administrative Commands Via XP_CmdShell In SQL Server 2005
Dec 13, 2007
SQL Server 2005: Microsoft SQL Server Enterprise Edition (64-bit)
Microsoft Windows NT 5.2 (3790)
SQL Version 9.00.3175.00
I have 2 identical servers... one for DEV and one for TEST. On the DEV server, I can use xp_cmsdhell to execute "administrative commands" such as NET SHARE and AT. On the identically configured TEST server, I can not. Those command return the "System error 5 has occurred. Access is denied" error. I CAN run other commands via xp_cmdshell (DIR, NET USE, etc.). This is true regardless of the account I connect as.
The SQL Server service accounts in both instances are domain accounts belonging to the local admin group.
I "know" that I ran across this when setting up the DEV server and am "fairly sure" I found a reference to a known problem with 2005's xp_cmdshell and administrative commands. But for the life of me I can not now find any such reference, nor can I find any differences between the DEV and TEST servers in the area of users, groups, policies, or anything else security-related. (99.9% of the internet searches I do end up being about either enabling xp_cmdshell -- done -- or setting up an xp_cmdshell proxy account -- not needed).
I would like to ask a question, more like to verify something that I know myself. I have an installation in SQL Server 7.0 (SP4), that hosts a database of 70gb with an average growth of 5 gb a month, that is being 'hitted' with an average of 600 clients, (about 50 to 90 concurrent hits). My question is :
Given the fact that I wish to upgrade my server to an SQL Server 2000 Enterprise Edition, how much RAM should I fit to the new machine?
I do have my suspicion, and I would like to keep it to myself for now, I would like also to ask if there's a formula for ram calculation. Mind you I don't ask for MINIMUM RAM, but more like OPTIMUM RAM, being that this Server hosts a critical system.
I have a DTS package in a QA server (MS SQL SERVER 2000). Now I want to implement that DTS package to the Production Server (MS SQL SERVER 2000) without any administrative effort.
I just wanted to create a script which can then execute to the production server to create that DTS package.
Till now what I have done to overcome this issues are..
I have save the DTS package in QA Server as Structured Storage File. And then copied the Structured Storage file from the QA Server to the production Server And then execute the package using dtsrun utilities. But in that case it is not creating the package in the production server.So that one can update or view the package from SQL Server Enterprise Manager ->Data transformation Service->Locale Package.
Msg 15121, Level 16, State 200, Procedure xp_cmdshell, Line 1 An error occurred during the execution of xp_cmdshell. A call to 'LogonUserW' failed with error code: '1326'.
... and yes, the username and password should be valid ;-)
When I exec (makes no sense, I now...)
EXEC xp_cmdshell 'echo 123'
The following error occurs:
Msg 15121, Level 16, State 200, Procedure xp_cmdshell, Line 1 An error occurred during the execution of xp_cmdshell. A call to 'LogonUserW' failed with error code: '1326'.
I am using the xp_cmdshell to run a batch file that is trying to open the Adobe Acrobat Reader.
When I run the batch file in the command prompt, it opens perfectly.
When I run it in SQL with xp_cmdshell, it returns the contents fine, and I notice it tries to open Acrobat, but it is under the username "system". And I cannot get it to load in a visible area, and for some reason it doesnt seem to load properly under system.
I'm building a simple webform, except Visual Studio and my service provider have combined to drive me nutty.
First, I MUST use an ODBC connection to my remote SQL Server do to some unknown configuartion problem. I've been playing with Visual Studio for only a month, so normally when something goes wrong I can go look in the mirror and find the culprit. This is different. I've got a totally functional web form with a SQL Connection, but when I try to change it to an ODBC Connection, I get the following error.
An OdbcParameter with ParameterName '@CertHolder' is not contained by this OdbcParameterCollection
My coding is fine because I stole it straight from the walkthrough and it works. But the specifications that Visual Studio provide are quite suspect. Please note the failure to include some key "@" signs.
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer. <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent() Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection Me.cmdUpdate = New System.Data.SqlClient.SqlCommand Me.cmdGetAll = New System.Data.SqlClient.SqlCommand Me.cmdSelect = New System.Data.SqlClient.SqlCommand Me.OdbcConnection1 = New System.Data.Odbc.OdbcConnection Me.OdbcGetAll = New System.Data.Odbc.OdbcCommand Me.OdbcSelect = New System.Data.Odbc.OdbcCommand Me.OdbcUpdate = New System.Data.Odbc.OdbcCommand ' 'SqlConnection1 ' Me.SqlConnection1.ConnectionString = "this works fine" ' 'cmdUpdate ' Me.cmdUpdate.CommandText = "UPDATE InsuranceData SET Name = @Name, Address = @Address, Address2 = @Address2, " & _ "City = @City, State = @State, Zip = @Zip, CertHolder = WHERE (CertHolder = @Cert" & _ "Holder)" Me.cmdUpdate.Connection = Me.SqlConnection1 Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Name", System.Data.SqlDbType.NVarChar, 50, "Name")) Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Address", System.Data.SqlDbType.NVarChar, 50, "Address")) Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Address2", System.Data.SqlDbType.NVarChar, 50, "Address2")) Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@City", System.Data.SqlDbType.NVarChar, 50, "City")) Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@State", System.Data.SqlDbType.NVarChar, 50, "State")) Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Zip", System.Data.SqlDbType.NVarChar, 50, "Zip")) Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CertHolder", System.Data.SqlDbType.NVarChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "CertHolder", System.Data.DataRowVersion.Original, Nothing)) ' 'cmdGetAll ' Me.cmdGetAll.CommandText = "SELECT CertHolder, Name, Address, Address2, City, State, Zip FROM InsuranceData" Me.cmdGetAll.Connection = Me.SqlConnection1 ' 'cmdSelect ' Me.cmdSelect.CommandText = "SELECT CertHolder, Name, Address, Address2, City, State, Zip FROM InsuranceData W" & _ "HERE (CertHolder = @CertHolder)" Me.cmdSelect.Connection = Me.SqlConnection1 Me.cmdSelect.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CertHolder", System.Data.SqlDbType.NVarChar, 50, "CertHolder")) ' 'OdbcConnection1 ' Me.OdbcConnection1.ConnectionString = "This works fine" ' 'OdbcGetAll ' Me.OdbcGetAll.CommandText = "SELECT CertHolder, Name, Address, Address2, City, State, Zip FROM InsuranceData" Me.OdbcGetAll.Connection = Me.OdbcConnection1 ' 'OdbcSelect ' Me.OdbcSelect.CommandText = "SELECT CertHolder, Name, Address, Address2, City, State, Zip FROM InsuranceData W" & _ "HERE CertHolder = @CertHolder" Me.OdbcSelect.Connection = Me.OdbcConnection1 Me.OdbcSelect.Parameters.Add(New System.Data.Odbc.OdbcParameter("CertHolder", System.Data.Odbc.OdbcType.NVarChar, 50, "CertHolder")) ' 'OdbcUpdate ' Me.OdbcUpdate.CommandText = "UPDATE InsuranceData SET Name = @Name, Address = @Address, Address2 = @Address2, " & _ "City = @City, State = @State, Zip = @Zip WHERE CertHolder = @CertHolder" Me.OdbcUpdate.Connection = Me.OdbcConnection1 Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Name", System.Data.Odbc.OdbcType.NVarChar, 50, "Name")) Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Address", System.Data.Odbc.OdbcType.NVarChar, 50, "Address")) Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Address2", System.Data.Odbc.OdbcType.NVarChar, 50, "Address2")) Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("City", System.Data.Odbc.OdbcType.NVarChar, 50, "City")) Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("State", System.Data.Odbc.OdbcType.NVarChar, 50, "State")) Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Zip", System.Data.Odbc.OdbcType.NVarChar, 50, "Zip")) Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Original_CertHolder", System.Data.Odbc.OdbcType.NVarChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "CertHolder", System.Data.DataRowVersion.Original, Nothing))
I NEVER EVER TYPED ORIGINAL_CERTHOLDER IN THE SQL PREPARATION
End Sub Protected WithEvents btnSave As System.Web.UI.WebControls.Button Protected WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection Protected WithEvents ddlCertHolder As System.Web.UI.WebControls.DropDownList Protected WithEvents txtName As System.Web.UI.WebControls.TextBox Protected WithEvents txtAddress As System.Web.UI.WebControls.TextBox Protected WithEvents ddlCH As System.Web.UI.WebControls.DropDownList Protected WithEvents cmdUpdate As System.Data.SqlClient.SqlCommand Protected WithEvents cmdGetAll As System.Data.SqlClient.SqlCommand Protected WithEvents cmdSelect As System.Data.SqlClient.SqlCommand Protected WithEvents txtAddress2 As System.Web.UI.WebControls.TextBox Protected WithEvents txtCity As System.Web.UI.WebControls.TextBox Protected WithEvents txtState As System.Web.UI.WebControls.TextBox Protected WithEvents txtZip As System.Web.UI.WebControls.TextBox Protected WithEvents OdbcConnection1 As System.Data.Odbc.OdbcConnection Protected WithEvents OdbcGetAll As System.Data.Odbc.OdbcCommand Protected WithEvents OdbcSelect As System.Data.Odbc.OdbcCommand Protected WithEvents OdbcUpdate As System.Data.Odbc.OdbcCommand
'NOTE: The following placeholder declaration is required by the Web Form Designer. 'Do not delete or move it. Private designerPlaceholderDeclaration As System.Object
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init 'CODEGEN: This method call is required by the Web Form Designer 'Do not modify it using the code editor. InitializeComponent() End Sub
#End Region
Comments? Suggestions, I am not positive about how to fix this.
Hi there! I just installed SQL 2005 Express and would like to use the command prompt to interact with my database such as creating/deleting etc. How do I start the SQL command terminal from command prompt? A tutorial website would be great! Thanks!
Hi all, I'm having some trouble getting commands to be available. Certain commands are grayed out, and i'm assuming not working.
For example I copied this code from online:
SELECT h.USERID, c.KEY1, CASE srectype WHEN 'A' THEN 'Appointments' WHEN 'C' THEN 'Calls' ELSE 'Nothing' END AS ContactType, COUNT(h.USERID) AS Total_Contacts, COUNT(CASE WHEN resultcode BETWEEN 0 AND 4 THEN 1 ELSE NULL END) AS Contacts_Made, COUNT(CASE WHEN resultcode = 4 THEN 1 ELSE NULL END) AS Sales FROM CONTHIST h INNER JOIN CONTACT1 c ON h.ACCOUNTNO = c.ACCOUNTNO WHERE (h.USERID = @userident) AND (h.ONDATE BETWEEN @startdate AND @enddate) AND (h.SRECTYPE IN ('A', 'C')) GROUP BY h.USERID, c.KEY1, h.SRECTYPE ORDER BY h.USERID, c.KEY1, h.SRECTYPE
From that bit of code the following commands are gray: BETWEEN AND NULL INNER JOIN IN
I can only assume that i'm doing something fundamentally wrong. Is there a setting i'm missing in SQL Server Managment Studio EXpress to make these available? Do I need to 'import' anything?
The current way I have my asp.net 2.0 web app running reports is, based on an interface the user selects the criteria for a report. The .RDL file is created dynamically based on the user's selections. I then need to SOAP the dynamically created report to the report server and then the report runs fine. BUT it requires Adminstrative rights to do this. Can this be accomplished without giving the local machine admin rights. I am sorry if this question has been answered before but i have not been up here in a while.
We are having some unusal problems with an upgrade to SQL Server 2005. I've come across two other people who have had this type of issue but I haven't found a resolution. We are running SQL Server 2000 in production and we have installed SQL Server 2005 on new hardware. We have been working for about a week to verify that all of out existing applications will work properly with SQL 2005.
We have several old VB applications that open a connection to the database and reuse it for days on end. We can test these apps against the production 2000 server and they run fine, when they connect to the 2005 server the connections work fine for 5 - 15 minutes then we get an error trying to execute a command and are told that the connection doesn't exist. (One interesting note, there is only one line of code in the whole program that connects to the database but we have seen 3+ connections from the app when we check in Management Studio for SQL 2005.)
Just to get though the tests we put in some error handling to reconnect if the connection is lost. When we do this the application ends up re-connecting only once, the new connection never has a failure even after hours of testing and we see a 300% performance improvement.
We can reliably cause this problem by stopping the sql server, starting it, waiting a few minutes, then running our app. After one run of the app the problem seems to go away for a while and we can't re-create it nearly as consistantly. Only occasionally will the initial connection fail and every time it does it will be after 5-10 minutes of activity.
Both machines are connected to the same switch and we have ruled out networking issues. We are only seeing this problem with VB6 apps, the VS2005 apps run great. We are looking for the cause of this connection behavior and a fix for it because we don't have the time to fix code in dozens of VB apps because the connections are not stable. Granted the practice of holding a connection open isn't good to begin with but with legacy code you have to work with what you're dealt.
Any thoughts as to why ADO connections from VB6 to SQL 2005 would not be stable?
Hi, I'm the DBA of a 500 person state agency. I have 16 accounts with SysAdmin rights. That seems way too many. But the other server rights seem to be too restrictive. Even dbOwner seems restrictive. What sort of consensus is there for the rights other application developers and SAs ought to have?
Hi, I want to copy xls file from remote server to my server. i want to use xp_cmdshell to perform this operation. can anybody help me out how to pass parameter to xp_cmdshell.
I was trying to grant access for an application user for executing xp_cmdshell, but I got some error message saying that either doesn't the user exist, or I don't have the permissions to grant this. Does the user need to be a user in Master ? Or, don't I when logged in as "sa" have the sufficient permissions to grant execute on a SP in master?
I solved it by checking "Control server" under "Properties" > "Securables" for the login, but I don't actually want this login to have full control.
(And yes, I've read that allowing xp_cmdshell usage isn't recommended at all.)
I'm just moving over one of our databases from SQL2000 to 2005. Everything is working fine, and all web sites are working, however I just can't seem to get xp_cmdshell to function on the 2005 server.
I have enabled xp_cmdshell, and when I run a stored procedure, which writes the contents of one column to a text file, it says "invalid object name". This table is definitely there, and is actually updated earlier on in the same SP, so permissions for that are fine.
This is the line causing the problem (if I take it out, the rest of the SP works no problems):
--write the text file EXEC master.sys.xp_cmdshell 'bcp "SELECT newslettertext FROM mydatabase.dbo.newsletters" queryout D:ewslettertext extbody.txt -U -P -c'
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'mydatabase.dbo.newsletters'.
SQLState = 37000, NativeError = 8180 Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. NULL
Could it be to do with permissions on the master.sys.xp_cmdshell? If so, wouldn't it give me a "permission denied" error?
I've got a stored procedure called from a trigger on another database. There are two xp_cmdshell statements in the procedure. One writes a couple variables, passed from the trigger, into a text file. The second fires off an executable that uses the text file via a batch file.
Here's the code that worked on the old 32bit SQL2000 box
The new box is 64bit SQL2005. When invoked the stored procedure executes without an error. The textfile is written sucessfully. But, the executable called from the second xp_shell statement does not produce the expected result. Because there is no error I'm having trouble determining where the failure lies.
To troubleshoot the problem I've tried:
Running the batch file from the command prompt in the system32 directory... Success
Using sp_xp_cmdshell_proxy_account to be sure of the credentials of the running xp_cmdshell... Failure
My questions:
Does 64 Bit SQL2005 have a different set of parameters when invoking an executable from xp_cmdshell? ie Do I need to rewrite the batch file? Is there another way call an executable, SQLCMD maybe?
I am not able to connect to Remote Distributor from Publisher using Administrative Link Password. I have configured the Distributor on 1 m/c and Publisher on another m/c. When i use the wizard to configure the publisher using remote distributor. I have also specified the same Admin Password link (distributor_admin) in the distributor m/c. Â I am getting the following message: Â TITLE: New Publication Wizard------------------------------ SQL Server could not connect to the Distributor using the specified password.
Connection to server [SANMENON] failed.OLE DB provider "SQLNCLI" for linked server "D956CF83-AE2E-4FC5-83DD-BE90D84A3950" returned message "Login timeout expired".OLE DB provider "SQLNCLI" for linked server "D956CF83-AE2E-4FC5-83DD-BE90D84A3950" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.". (Microsoft SQL Server, Error: 21670)
Hi, I have a least privileged SQL Login €œClient€? and have granted execute rights on XP_Cmdshell SP at master db. When I execute master.. XP_Cmdshell €˜dir€™ I€™m getting the below error.
Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1 The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.
Please note it is SQL Login account and not windows account. I have checked everywhere for similar problem and no luck.
I would like to use powershell commands within my TSQL scripts. how to do that?
The reason is that up until now I have been using SQLCMD and XP_CMDSHELL to rename files before importing them into a database. Powershell is a better tool for this job.
I saw a presentation last week where the speaker created some sorta sql server "watch window" (in Sql Server Management Studio I think) where he could watch all the commands being executed on his sql server database in real-time. For example he could navigate to web pages (that hit the database) and as he pressed buttons you could see the sql commands execute in this "watch window." If other users hit the database at the same time you could see those sql queries execute as well. I didn't think at the time to ask how he did it - does anyone know how to set this up? I have a problem with my sql server right now and it would be useful to see which sql queries (etc) are being executed when. Thanks in advance,J. Shane Kunklejkunkle@vt.edu
I would like to enable users that do not belong to groups (server roles) such as sysadmin, serveradmin and don't have db permissions such as ddl_admin or db_owner to run some of the system stored procedures (such as sp_addumpdevice sp_configure sp_serveroption ...) and DBCC commands (such as DBCC CHECKFILEGROUP - requires ob_owner or sysadmin permission).
Is it possible to change permissions of SQL Server system stored procedures?
Is it possible to change permissions of SQL Server DBCC commands?
I am trying to issue an xp_cmdshell command via a guest account. I have given the guest user in the master db exec rights, and when I do something simple like
execute xp_cmdshell 'dir c:'
I just get the reply that the command completed without any returned data. However when I am on as 'sa' I get the results back as expected.
I have an external program which runs by the xp_cmdshell. This program get data from serial port and then store it back to the database (directly, by JDBC) It seems to me that when xp_cmdshell is running a program, the whole SQL server stop responding to any other request. (Is this normal?) Anyways, I insert a record into a table and this triggers an external program to run. But then the whole SQL server is hold at that stage. Logins are ok but no SQL or T-SQL commands are accepted. It seems to me like the SQL server (xp_cmdshell in specific) is waiting for the external program to terminate but the external program is waiting for the server is be ready to accept data. They are both waiting for each other. Any suggestions here?
hi,does anybody know how to enable/disable xp_cmdshell in sql server2000.i our server this sp has been disbaled for security puropse.but i needto use it in a job.so please can any body guide me?thanks for the help.-Shark.
Any list of commands that require exclusive access in order for the command to complete? I had an instance today where a DBA executed sp_changedbowner command which is the alter database command on a production database and it locked it up.
how i can clear sql server logs by run xp_cmdshell stored procedure. (my user in sql server is a admin). i don't want use enterprise manager . i want write a query and use at xp_cmdshell . befor thx.
I am using XP_CMDSHELL to run 2 Windows commands (from a SQL 2005 SP2 server) that access files on a remote server (called webserver). The two commands are:
1. cscript.exe \webserverwwwrootEur_SaveProductImages.vbs 2. dir \webserverwwwroot Up until very recently this worked fine. Now I am getting Access Is Denied errors:
For #1: Microsoft (R) Windows Script Host Version 5.6 Copyright (C) Microsoft Corporation 1996-2001. All rights reserved. CScript Error: Loading script "\webserverwwwrootEur_SaveProductImages.vbs" failed (Access is denied. ).
For #2: Access is denied.
This happens if the above commands are run through a job, or directly with Query Analyzer as SA.
I understand that XP_CMDSHELL commands are run in the security context of the SQL Server startup account. The SQL Server startup account is called SQLServer and this account exists on both webserver and the database server. On both servers the username & password are identical and the user is in the local Administrators group. The two servers are in a workgroup.
Things I have tried (with no effect):
1. Restarted the servers 2. Deleted and recreated the SQLServer login on both servers 3. Changed the startup account for SQL Server through the SQL Server Configuration Manager to another account and back to SQLServer.
The most revealing tests are these:
1. If I log into the database server myself using the SQLServer account I can run the above commands in a Command Prompt with no problem
2. I installed SQL 2005 Express SP2 on webserver using the same startup account (SQLServer). If I run the same XP_CMDSHELL commands as above but accessing files on the database server, the commands work! So, an XP_CMDSHELL command run under SQL Express using the local SQLServer account to access a remote server works, but an XP_CMDSHELL command run under SQL Server using a local SQLServer account (that is supposedly identical to the same named account on the other server) does not work !!?!?!?
It is almost as if XP_CMDSHELL commands run under SQL Server are NOT using the SQLServer startup account, but using some other weaker account. How can I tell if this is true?
How can I get this back working (as it was a week ago). The only thing I can think that happened on the servers to cause this is the installation of Windows Updates.
Hi allI have a stored procedure that has the lineEXEC master..xp_cmdshell 'dtsrun /Stestjob1 /N testdts /E'If I run the SP from an access front end as a trusted user or from ascheduled job it runs fine and exectues the dts.If I run the stored procedure using VB6 as a standard connection the dtsjobwont run. I get back Execute permissions denied on xp_cmd.. on databasemasterdb_connect_string = "Provider=SQLOLEDB.1;Persist Security Info=False;UserID=test_connect;PWD=pw1test;Initial Catalog=testdb;Data Source=" &database_name....Set cmd = New ADODB.Commandcmd.ActiveConnection = db_connect_stringcmd.CommandType = adCmdStoredProccmd.CommandText = "testStoredProcedure"cmd.ExecuteDo I need to give test_connect permisions to run the test stored procedure.I hoped that because the VB called a stored procedure and the connection hadpermissions to execute the SP then it would be the SP that called thexp_command....can anyone tell me the accepted way to do thismany thanksAndy
I have these two commands that I execute at the end of my stored procedure. I get an email every time I execute this stored procedure whether the select statement returns a value or not. But I only want to get an email if select statement returns an Error value. How can I accomplish this?
set @cmd = 'osql -S server -U user -P psswd -q "set nocount on; select distinct(rtrim(col1)) from ##table where datediff(dd,col2,getdate()) = 1 and (col1 like ''%Error: %'')" -h-1 -w 1025 -o J:MyFolderErrorLogMsg.txt'
