September 22, 2009 at 5:35 pm
Hi,
I need to use a proxy in order to use xp_cmdshell because the users that are going to use it are not sys_admin, but the guys from active directory are asking me for the right or the policies that they have to apply to that user.
EXEC sp_xp_cmdshell_proxy_account
N'asisaoutsourcin\carlos.salazar',
N'*******'
In this example they are asking me where to put (member of what?) the user carlos.salazar. We are new on this kind of implementations so they need to know what exactly they are doing. They want that user with the minimum access.
Thanks for your help
Carlos
September 22, 2009 at 5:49 pm
Basically it just needs to be a user, nothing special. Additional rights will be directly related to what you want it to do...
CEWII
September 23, 2009 at 9:43 am
When I,
use a normal user i got this error ->
Error = [Microsoft][SQL Native Client]Shared Memory Provider: No process is on the other end of the pipe.
But when i use an administrator every it works fine.
September 23, 2009 at 10:45 am
What is the command you are executing?
September 23, 2009 at 11:09 am
DECLARE @Fecha DATETIME --MM/DD/YYYY
DECLARE @Resultado VARCHAR(100)
DECLARE @ResultadoCasos BIGINT
DECLARE @ImagenBackup XML
--Establecer los casos que se guardaran
SET @ImagenBackup = ''
--
SET @Resultado = 'No existen casos con esta fecha';
SET @Fecha = getdate()
--Casos con fecha menor a la pasada por parametro y que contengan imagenes
SELECT
@ResultadoCasos = COUNT(*)
FROM
[WIWBancolombia].[dbo].[Caso]
WHERE
DATEADD(DD,0, DATEDIFF(DD,0,FechaCreacion)) <= (DATEADD(DD,0, DATEDIFF(DD,0,@Fecha)))
AND
Imagenes IS NOT NULL
AND
ImagenEnBackup = 0
--Validar que existan casos con esa fecha
--Consulta
DECLARE @Consulta VARCHAR(MAX)
--Ruta del documento
DECLARE @Ruta VARCHAR(MAX)
--Ruta del documento
SET @Ruta = 'C:\BKP\ImagenesBackup_' + CONVERT(VARCHAR(11),@Fecha) + '_.xml'
--Consulta
SET @Consulta = 'EXEC xp_cmdshell ''bcp "SELECT CodigoCaso,CONVERT(XML,REPLACE(REPLACE(REPLACE(CAST(Imagenes AS VARCHAR(MAX)), CHAR(9),''''''''), CHAR(10),''''''''), CHAR(13), '''''''')) AS Imagenes FROM [WIWBancolombia].[dbo].[Caso] WHERE Imagenes IS NOT NULL AND ImagenEnBackup = 0 AND DATEADD(DD,0, DATEDIFF(DD,0,CONVERT(VARCHAR(100),FechaCreacion))) <= ''''' + CAST(DATEADD(DD,0, DATEDIFF(DD,0,CONVERT(VARCHAR(100),@Fecha))) AS VARCHAR(30)) + ''''' FOR XML RAW " queryout "' + @Ruta + '" -T -c -t'''
--PRINT @Query
EXEC(@Consulta)
--Declara la variable
DECLARE @ValorImagen XML
--Establecer el valor
SET @ValorImagen = ''
--Despues de ejecutar el backup actualizo los dartos en la tabla
--Actualizo los registros que acabo de modificar
UPDATE
[WIWBancolombia].[dbo].[Caso]
SET
Imagenes = @ValorImagen,
ImagenEnBackup = 1
WHERE
DATEADD(DD,0, DATEDIFF(DD,0,CONVERT(VARCHAR(100),FechaCreacion))) <= DATEADD(DD,0, DATEDIFF(DD,0,CONVERT(VARCHAR(100),@Fecha)))
AND
Imagenes IS NOT NULL
AND
ImagenEnBackup = 0
SELECT @Resultado = 'Creado Exitosamente';
END TRY
BEGIN CATCH
SELECT @Resultado = 'Ocurrio un error';
END CATCH
ELSE
SELECT @Resultado;
END
September 23, 2009 at 12:26 pm
csalazar-607991 (9/23/2009)
--ConsultaDECLARE @Consulta VARCHAR(MAX)
--Ruta del documento
DECLARE @Ruta VARCHAR(MAX)
--Ruta del documento
SET @Ruta = 'C:\BKP\ImagenesBackup_' + CONVERT(VARCHAR(11),@Fecha) + '_.xml'
--Consulta
SET @Consulta = 'EXEC xp_cmdshell ''bcp "SELECT CodigoCaso,CONVERT(XML,REPLACE(REPLACE(REPLACE(CAST(Imagenes AS VARCHAR(MAX)), CHAR(9),''''''''), CHAR(10),''''''''), CHAR(13), '''''''')) AS Imagenes FROM [WIWBancolombia].[dbo].[Caso] WHERE Imagenes IS NOT NULL AND ImagenEnBackup = 0 AND DATEADD(DD,0, DATEDIFF(DD,0,CONVERT(VARCHAR(100),FechaCreacion))) <= ''''' + CAST(DATEADD(DD,0, DATEDIFF(DD,0,CONVERT(VARCHAR(100),@Fecha))) AS VARCHAR(30)) + ''''' FOR XML RAW " queryout "' + @Ruta + '" -T -c -t'''
I think that is the important section.. You are trying to pull the data out into a file from the local server. This leads to these questions:
1. Does the proxy user have access to the C:\BKP\ location with sufficient rights to create and write to a file there?
2. Does the proxy user have access to the local SQL server with sufficient rights to query that table?
3. Are you trying to access a default or named instance? If it is a default instance then it should be ok, but for a named instance you need to add the -S parameter and explicitly reference the named instance.
Those are the issues I can think of..
CEWII
September 23, 2009 at 2:02 pm
Hi,
Thanks for your help in effect I solved my problem giving the proxy user the corrects access to the sql server ->
use [WIWBancolombia]
GO
GRANT SELECT ON [dbo].[Caso] TO [PARIS-SQL2005\csalazar]
GO
Taking into account that [PARIS-SQL2005\csalazar] is the proxy user!!!
Again Thanks a lot.
Carlos
September 23, 2009 at 3:00 pm
Ah, so your problem was item #2 on my question list. Great. I'm glad to hear it..
CEWII
September 24, 2009 at 3:54 am
You should consider using an anonymous account for the xp_cmdshell proxy account.
This proxy is used by all non-sysadmin users who want to run xp_cmdshell. The proxy will need access rights to files that would be used by all users who run xp_cmdshell. Also, when you change the password for the proxy account, you must also update the password registered in SQL Server for the proxy.
If you set the proxy account to a person's normal account, then everybody else who runs xp_cmdshell will be using that person's credentials. Also, if that person changes their password without getting SQL Server updated the proxy account will stop working.
The FineBuild reference manual has a good description of how the various proxy accounts in SQL Server work, plus best-practice recommendations on how to use them. You can download this using the link below or direct from Codeplex.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply