June 12, 2019 at 7:19 pm
I have a store procedure that runs the xp_cmdshell with a user with sysadmin right.(it was created by someone else long time ago). For securities purposes I need to remove the sysadmin right from this user. So I created a proxy account and gave execute to this user on the xp_cmdshell (practically followed the steps how to run xp_cmdshell with a non sysadmin user ). But when I run the store procedure I am getting the below error:
error number 15466 severity 16 errorline 1 An error occurred during decryption.
I use sql server 2008
i dont know what i am missing, pls help
thanks
Denisa
June 12, 2019 at 8:00 pm
Did you Google for that error? It doesn't look like connected to xp_cmdshell. Are you using encryption?
--Vadim R.
June 12, 2019 at 8:51 pm
So I created a proxy account and gave execute to this user on the xp_cmdshell...
NEVER give a non-DBA privs to execute xp_CmdShell directly. You have the proxy account setup (hopefully, correctly). If the database is owned by a login with sys_admin privs, add WITH EXECUTE AS OWNER to the proc and give the user privs to execute THAT proc instead of xp_CmdShell.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 13, 2019 at 1:13 pm
no we don't use encryption
June 13, 2019 at 1:18 pm
I tried with execute as Owner still the same error.
proxy account was created long time ago from someone else , so I am not sure if this is created correctly, is any way I can check ?
June 13, 2019 at 9:09 pm
If you just created it as mentioned in your original post, it would be the one you just setup. You should see it if you query sys.credentials. It will have the credential name of ##xp_cmdshell_proxy_account## and will list the credential identity.
Sue
June 14, 2019 at 5:58 pm
Hi Sue, yes it is showing a Windows account user, I have seen this before. So I suppose is setup correct..??
June 14, 2019 at 6:16 pm
Yup it is set up the other considerations would be the password may not be valid. Or the Windows account could be locked out, not valid, that type of thing. You could always reset it if you wanted to try that with an account and password you know is valid. May not be a bad idea at this point. So you'd do a Run As Administrator with SSMS and execute:
--wipes out the existing proxy account
EXEC sp_xp_cmdshell_proxy_account NULL
GO
--create the proxy account
EXEC sp_xp_cmdshell_proxy_account 'YourDomain\YourDomainUser', 'DomainUserPassword';
GO
Sue
June 16, 2019 at 11:40 pm
Thought I'd throw my 2 cents in from a presentation I did nearly a decade ago and it still works to allow the secure operation of xp_CmdShell. Here's the full monty. Details are in the code and, yes... you will need to change the domain names in the code as well as the name of the command proxy user and the related password for the setup to work for you.
RTFM is incredibly important in making it work correctly or at all so READ ALL THE COMMENTS BEFORE YOU USE THIS CODE! And, for goodness sake, DON'T GIVE ANY OTHER USERS PRIVS ON XP_CMDSHELL!!! ONLY USE THIS PROXY METHOD WITH "EXECUTE AS OWNER" IN STORED PROCEDURES!!! DBA's who already have sysadmin privs will be able to continue to use xp_CmdShell directly, etc! And don't give users "Control Server" privs, either. In fact, after reading the final comment below, you'll see that users don't actually need even Read/Write privs if you adopt stored procedures over ORM generated code. You still need to worry about SQL INJECTION whether you adopt this method or not!
/**********************************************************************************************************************
This is the code that I use to demonstrate how to setup xp_CmdShell to be used safely. Please read all comments in
this header and all comments in the code in each section, especially the headers. This code is meant both as a
demonstration and guide for how to set things up and the last section also provides a security demonstration to
allow non-prived users to execute a stored procedure that contains calls to xp_CmdShell without having any privs to
execute xp_CmdShell directly.
Note that I've tried to make this code agnostic for upper/lower case but may have missed a spot or two because none
of my servers have been setup with case-sensitive collations.
1. There are 3 sections to this code. I recommend that you run them individually.
2. You find the following users in the code and you will need to change them to get this to run in your environment...
[VAIO\CmdProxyUser]
1.1 This user is the necessary command proxy user. I don't recommend that you name it so obviously. I only did so
to make it clear in the demonstration.
1.2 This user MUST be an Active Directory user setup in Windows. It should be a generic user with only basic very
low level privs.
1.3 The VAIO domain is just a test domain I setup for demonstration purposes. You'll need to change that do your
domain.
1.4 I also setup a password that's pretty obvious for demonstration purposes. You need to use a password that
meets you password policies.
1.5 Again, take note of Step 1.2 above. This AD user should NOT have DOMAIN privs or any other privs other than
a typical user.
[VAIO\TestDummy]
1.1 This user is just a common user. You can keep the name of the user as TestDummy and use it for other
security tests.
1.2 This user MUST be an Active Directory user setup in Windows. It should be a generic user with only basic very
low level privs.
1.3 The VAIO domain is just a test domain I setup for demonstration purposes. You'll need to change that do your
domain.
1.4 I also setup a password that's pretty obvious for demonstration purposes. You need to use a password that
meets you password policies.
1.5 Again, take note of Step 1.2 above. This AD user should NOT have DOMAIN privs or any other privs other than
a typical user.
**********************************************************************************************************************/
--=====================================================================================================================
-- ***** RUN THIS SECTION SEPARATELY *****
-- ***** WARNING ***** THIS CODE DROPS ANY EXISTING xp_CmdShell proxy with great prejudice!!! *****
-- This section Kills any previous test environment and setup the proxy account.
-- Again, you'll need to change the domain name from VAIO to your domain and the user name to whatever you setup
-- in Active Directory to act as the proxy user. No one else should be using the login. It's for a single purpose
-- only!
-- ***** YOU MUST BE IN THE ADMIN MODE TO EXEcUTE THIS SECTION OF CODE!!! *****
--
-- To do that, you must be logged in as someone with "sysadmin" privs, as well.
-- If you're running against a server, you may have to do a Remote Desktop Connection and log into the server
-- directly in order to execute this section of code. To be sure, it's a one-time thing necessary only for
-- setting up the proxy user.
--
-- 1. Make sure that you've changed the domain name, user names, and password in the code.
-- 2. Right click on the SSMS icon in Windows (a popup will show)
-- 3. Hold down the SHIFT KEY, right click on "SQL Server Management Studio" (another popup will occur),
-- and then click on "RUN AS ADMINISTRATOR". . You must be logged in as someone with "sysadmin" privs, as well.
--=====================================================================================================================
--===== Make sure none of the test objects I use exist ahead of time so that we can see that this all actually works
USE MASTER;
--===== Disable the proxy account.
EXEC sp_xp_cmdshell_proxy_account NULL; --Drops the cmd shell proxy just to be sure.
--===== Make sure the old xp_CmdShell proxy user doesn't exist in master or as a Login.
IF EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'VAIO\CmdProxyUser') DROP SCHEMA [VAIO\CmdProxyUser];
IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'VAIO\CmdProxyUser') DROP USER [VAIO\CmdProxyUser];
IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'VAIO\CmdProxyUser') DROP LOGIN [VAIO\CmdProxyUser];
--===== Now we build the Login and proxy account using the CmdProxyUser I built in Windows on my box at home.
-- IMPORTANT!!! A step we cannot skip is that we have to build a user from the CmdProxyUser login.
-- NOTE THAT THIS MUST BE A SINGLE USER AND NOT A WINDOWS GROUP!
CREATE LOGIN [VAIO\CmdProxyUser] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english];
CREATE USER [VAIO\CmdProxyUser] FOR LOGIN [VAIO\CmdProxyUser] WITH DEFAULT_SCHEMA=[dbo];
EXEC sp_xp_cmdshell_proxy_account 'VAIO\CmdProxyUser','CmdProxyUserPW';
--===== Very important here... we have to grant access to xp_CmdShell to the new Window's user...
GRANT EXECUTE ON xp_CmdShell TO [VAIO\CmdProxyUser]
;
--===== This just displays how limited even the CmdProxyUser is!!!!
EXEC sp_helpuser [VAIO\CmdProxyUser]
;
EXEC xp_cmdshell 'Dir C:\ ' ; --This just shows that we didn't screw anything up for those that have privs.
--Obviously, you need to have the right privs for this verification to work!
--=====================================================================================================================
-- ***** RUN THIS SECTION SEPARATELY *****
-- Setup the test envionment (Database, User, and Stored Procedure)
-- Note that you must be logged in as someone that can create the objects in the code.
-- YOU DO NOT NEED TO BE USING THE "RUN AS ADMINISTRATOR" OPTION FOR THIS SECTION OF CODE BUT IT WON'T HURT.
--=====================================================================================================================
--===== Ensure the test database doesn't exist.
USE master
;
IF DB_ID('CmdShellTest') IS NOT NULL
BEGIN
ALTER DATABASE [CmdShellTest] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [CmdShellTest];
END
;
--===== Create the test database.
CREATE DATABASE [CmdShellTest];
GO
--===== Assign the owner of the database. NOTE THAT THE "SA" LOGIN MUST BE DISABLED FOR SECURITY REASONS.
-- AND, NO... THAT'S NOT A MISTAKE... "SA" must be DISABLED to do this securely.
USE [CmdShellTest];
EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false; --THIS IS ESSENTIAL OR IT WON'T WORK
GO
--===== Create the test user
USE [CmdShellTest];
IF EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'VAIO\TestDummy') DROP SCHEMA [VAIO\TestDummy];
IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'VAIO\TestDummy') DROP USER [VAIO\TestDummy];
IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'VAIO\TestDummy') DROP LOGIN [VAIO\TestDummy];
CREATE LOGIN [VAIO\TestDummy] FROM WINDOWS WITH DEFAULT_DATABASE=[CmdShellTest], DEFAULT_LANGUAGE=[us_english];
CREATE USER [VAIO\TestDummy] FOR LOGIN [VAIO\TestDummy]; --This just maps the database for the user
ALTER USER [VAIO\TestDummy] WITH DEFAULT_SCHEMA=[dbo];
--===== Show how low the privs are for the test user (PUBLIC only)
EXEC sp_helpuser [VAIO\TestDummy]
;
--===== Create a stored procedure in the new "MyTester" database that uses xp_CmdShell.
-- Keep in mind that, right now, we're signed in as a member of "dbo".
IF OBJECT_ID('dbo.GetDirInfo') IS NOT NULL DROP PROCEDURE dbo.GetDirInfo;
GO
--===== Create the test stored procedure that uses xp_CmdShell.
-- It also returns all the different users that it runs as for comparision purposes when a low prived used
-- runs the code.
-- This always works for me because I use the ** DISABLED ** "SA" login as the owner of my databases.
-- If you have mixed tenant databases on the same instance, that might not be such a good idea but can
-- still be workable with a bit of caution (for example, never grant "Control" server to DBO's.
-- Even if you don't need to use xp_CmdShell, it's normally a much better idea to create an instance
-- for separate tenants but, of course, there are limits to that.
USE [CmdShellTest]
;
GO
--===== This does the actual stored procedure creation. Note that this also works for allowing users to execute
-- code that contains things like TRUNCATE TABLE and OPENROWSET even if you haven't setup or use xp_CmdShell
-- without having to give the users anything more than PUBLIC privs and EXECUTE on the stored procedure.
--
-- In other words, IF YOU MADE EVERYTHING DRIVEN BY STORED PROCEDURES, EVEN YOUR APPLICATIONS WOULD ONLY NEED
-- PUBLIC (PROVIDES CONNECT PRIVS) AND EXECUTE ON CERTAIN STORED PROCEDURES!
CREATE PROCEDURE dbo.GetDirInfo
WITH EXECUTE AS OWNER --THIS IS WHAT MAKES THINGS WORK IF THE OWNER OF THE DATABASE IS SETUP CORRECTLY!
--(See the "Create the test database" sub-section of this code above.
AS
EXEC xp_cmdshell 'DIR C:\ ' ;
SELECT ORIGINAL_LOGIN(), SUSER_NAME(), SUSER_SNAME(), USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER;
;
GO
--===== MAKE SURE THAT PEOPLE HAVE THE PRIVS TO EXECUTE THE PROC WITHOUT CHANGING FROM PUBLIC PRIVS.
GRANT EXECUTE ON dbo.GetDirInfo TO [public]
GO
--=====================================================================================================================
-- ***** RUN THIS SECTION SEPARATELY *****
-- Now, show that the "TestDummy" user can execute the proc but not xp_cmdshell itself.
-- YOU DO NOT NEED TO BE USING THE "RUN AS ADMINISTRATOR" OPTION FOR THIS SECTION OF CODE BUT IT WON'T HURT.
--=====================================================================================================================
--===== Make sure we're in the correct database
USE [CmdShellTest]
;
--===== Simulate logging in as a user with low privs and display who they're logged in as...
EXECUTE AS LOGIN = 'VAIO\TestDummy';
SELECT ORIGINAL_LOGIN(), SUSER_NAME(), SUSER_SNAME(), USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER
;
-----------------------------------------------------------------------------------------------------------------------
--===== Demonstrate that the low prived user can execute the stored procedure which contains a call to xp_CmdShell.
PRINT REPLICATE('=',80)
;
PRINT '********** Testing execution of dbo.GetDirInfo **********'
PRINT '********** This should work just fine. **********'
EXEC dbo.GetDirInfo
;
--===== Confirm that the privs of the low prived user have not been changed.
SELECT ORIGINAL_LOGIN(), SUSER_NAME(), SUSER_SNAME(), USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER
;
-----------------------------------------------------------------------------------------------------------------------
--===== Demonstrate that the low prived user CANNOT use xp_CmdShell directly (this will fail.
PRINT REPLICATE('=',80);
PRINT '********** Testing execution of xp_CmdShell directly **********'
PRINT '********** THIS SHOULD FAIL AS EXPECTED!!! **********'
EXEC xp_cmdshell 'DIR C:\ '
;
GO
--===== Test complete... go back to being logged in as you.
REVERT
;
-- And, if you're wondering, a low prived user that can build stored procedures CANNOT create a stored procedure with
-- EXECUTE AS OWNER! In the testing that I did on my 2016 production server, even someone with DBO privs can't do this!
-- Disclaimer... I've not tested every version/SP/CU of SQL Server. To be blunt, you're stupid if you don't test for
-- such a thing on your particular server(s)
--Jeff Moden
Change is inevitable... Change for the better is not.
June 17, 2019 at 7:48 pm
Thank you, Jeff, very useful info.
--Vadim R.
June 18, 2019 at 11:20 am
Thank you, Jeff, very useful info.
Thanks for the feedback, rVadim.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply