September 19, 2014 at 8:43 am
Cannot seem to get by the Msg 15137 error. Help sooooo appreciated. I've looked about every blog Google will show. Tried "Run as administrator" when running SSMS and many other things found. No avail.
I've already done:
CREATE CREDENTIAL [##xp_cmdshell_proxy_account##] WITH IDENTITY ...
This is the test version of the procedure we're trying to move from a working server (dev) to this new one (prod). System Admins won't give me, the DBA, access to the box, so I don't know a whole lot more about what might be blocking this.
CREATE PROCEDURE [DBATools].[KDSTestCmd]
AS
BEGIN
Declare
@ProxyUser nvarchar(255),
@ProxyPassword nvarchar(255),
@BackupFolder nvarchar(255),
@BackupDatabaseName nvarchar(255),
@DestinationFolder nvarchar(255),
@ZipPath nvarchar(255),
@DataFolder nvarchar(255),
@BackupPath nvarchar(255);
Declare @databases Table (
DatabaseName nvarchar(255),
ServerBAKFolder nvarchar(255),
BAKDatabase nvarchar(255),
LocalBAKFolder nvarchar(255),
DataFolder nvarchar(255),
BAKDataFile nvarchar(255),
BAKLogFile nvarchar(255),
AutoFixUser nvarchar(255),
ProxyUser nvarchar(255),
ProxyPassword nvarchar(255),
ZipPath nvarchar(255),
ZipOnly bit not null default(0),
Processed bit not null default(0));
SET @DataFolder = '';
SELECT @DataFolder = SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 2)
FROM master.sys.master_files
WHERE database_id = 1 AND file_id = 1
--SET @BackupPath = SUBSTRING(@DataFolder,1,Len(@DataFolder) - 4) + 'Backup';
SET @BackupPath = 'D:\MSSQL\Backup\PRODSQL'
SET @ProxyUser = 'MyDomain\SVC_Acct';
SET @ProxyPassword = '1Tw034551x78n1n310!';
IF (ISNULL(@ProxyUser,'')<>'')
BEGIN
PRINT '-----Setting proxy account credentials';
/* Errs right here */
EXEC sp_xp_cmdshell_proxy_account @ProxyUser, @ProxyPassword;
EXECUTE as Login = @ProxyUser;
END
SELECT @BackupPath;
DECLARE @Command nvarchar(100) = 'dir "' + @BackupPath + '"';
PRINT '----- Shell cmd -----'
exec xp_cmdshell @Command;
If (IsNull(@ProxyUser,'')<>'')
Begin
PRINT '-----Removing proxy account credentials';
REVERT
EXEC sp_xp_cmdshell_proxy_account NULL;
End
END
Results:
-----Setting proxy account credentials
Msg 15137, Level 16, State 1, Procedure sp_xp_cmdshell_proxy_account, Line 1
An error occurred during the execution of sp_xp_cmdshell_proxy_account. Possible reasons: the provided account was invalid or the '##xp_cmdshell_proxy_account##' credential could not be created. Error code: '5'.
(1 row(s) affected)
----- Shell cmd -----
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.
-----Removing proxy account credentials
(1 row(s) affected)
September 23, 2014 at 1:42 pm
Ok, I got it to work but don't really know the explanation. I can explain what I did, but not why it worked. I had to not make the call to the proxy as I wanted to, but Create Credential only. Every example I'd come across showed setting the CREATE CRED in addition to the normal call to sp_xp_cmdshell_proxy_account as it had been doing.
Any comments, input, enlightening? Again, this was working on other boxes, just not the new box that the network admin setup (security policy won't let me on it).
/**/
-----------
SET @ProxyUser = 'MyDomain\SVC_Acct';
SET @ProxyPassword = '1Tw034551x78n1n310!';
IF (ISNULL(@ProxyUser,'')<>'')
BEGIN
PRINT '-----Setting proxy account credentials';
/* Errs right here */
--EXEC sp_xp_cmdshell_proxy_account @ProxyUser, @ProxyPassword;
/* DID THIS INSTEAD */
DECLARE @sqlProxy VARCHAR(255)
SET @sqlProxy = 'CREATE CREDENTIAL [##xp_cmdshell_proxy_account##] WITH IDENTITY = ''' + @ProxyUser + ''', SECRET = ''' + @ProxyPassword + ''''
EXEC (@sqlProxy)
-- Change
EXECUTE as Login = @ProxyUser;
END
SELECT @BackupPath;
DECLARE @Command nvarchar(100) = 'dir "' + @BackupPath + '"';
PRINT '----- Shell cmd -----'
exec xp_cmdshell @Command;
If (IsNull(@ProxyUser,'')<>'')
Begin
PRINT '-----Removing proxy account credentials';
REVERT
/* Not THIS */
--EXEC sp_xp_cmdshell_proxy_account NULL;
/* But THIS */
DROP CREDENTIAL [##xp_cmdshell_proxy_account##]
End
END
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply