Credentials and Proxy's

Last Post 18 Jul 2007 06:09 AM by Rhinodba. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Rhinodba
New Member
New Member

--
17 Jul 2007 01:00 PM
My customer who is migrating to SQL Server 2005, will be making heavy use of Credentials and Proxy accounts that they can grant non-system administrators the ability to execute jobs that use dts and ssis that call subsystems.

My Windows user account is member of the SA fixed server role.

I login and run:
DECLARE @cmd sysname, @var sysname
SET @var = 'Hello world'
SET @cmd = 'echo ' + @var + ' > F:\DBA\Test_OutPut.txt'
EXEC master..xp_cmdshell @cmd

And it is successful.

So I
USE [master]
CREATE LOGIN SqlCredUser
WITH PASSWORD = 'password',
DEFAULT_DATABASE = [master],
DEFAULT_LANGUAGE= us_english,
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF

And then I
USE DatabaseName
GO
CREATE USER SqlCredUser
FOR LOGIN SqlCredUser
GO
EXEC sp_addrolemember 'db_datareader', 'SqlCredUser'
GO
EXEC sp_addrolemember 'db_datawriter', 'SqlCredUser'


And then I
CREATE CREDENTIAL DevCredential
WITH IDENTITY = 'NORTHAMERICA\kewarren',
SECRET = 'sdrlk8$40-dksli87nNN8'
GO


And then I
USE msdb
GO
EXEC dbo.sp_add_proxy
@proxy_name = 'Zurich Developer Proxy',
@enabled = 1,
@description = 'This proxy has been created so that developers can execute xp_cmdshell.',
@credential_name = 'DevCredential'
GO


And then I
ALTER LOGIN SQLCredUser
WITH CREDENTIAL = DevCredential


I even do
EXEC dbo.sp_grant_login_to_proxy
@login_name = N'SqlCredUser',
@proxy_name = N'Zurich Developer Proxy'
GO


And the I
USE msdb
GO
EXEC dbo.sp_grant_proxy_to_subsystem
@proxy_name = N'Zurich Developer Proxy',
@subsystem_name = N'CmdExec' ;
GO


The proxy shows up under the right ‘bucket’ in SSMS under SQL Agent.


But surprisingly
USE msdb
EXEC sp_help_proxy
@name = 'Zurich Developer Proxy',
@subsystem_name = 'CmdExec'

Even though this was specifically granted above it generates this error
Msg 14516, Level 16, State 1, Procedure sp_verify_proxy_permissions, Line 113
Proxy (5) is not allowed for subsystem "CmdExec" and user "Zurich Developer Proxy". Grant permission by calling sp_grant_proxy_to_subsystem or sp_grant_login_to_proxy.


I log in as SqlCredUser and am Not able to run

DECLARE @cmd sysname, @var sysname
SET @var = 'Hello world'
SET @cmd = 'echo ' + @var + ' > F:\DBA\Test_OutPut.txt'
EXEC master..xp_cmdshell @cmd


I get this message
Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1
The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.


Can you tell me what is happening or what I am missing?

I could use sp_xp_cmdshell_proxy_account
• But that requires that I use a Windows User account
• It requires that I know that Windows Users accounts password, enter it, and run it.

Many of the jobs that use dts and ssis to call xp_cmdshell are SQL accounts.

Any work arounds?

Thanks
Rhinodba
New Member
New Member

--
18 Jul 2007 06:09 AM
Thanks for your response and I agree. This is a very large customer that makes heavy use of contractors that roll in and out frequently. There is not really a long term DBA that has ownership of this. As a result, it is what it is. I have several recommendations for them and they are open.

I will ask if they can create a Windows account for this purpose. Beyond that, any ideas on how to get a SQL account to be able to successufully do this?

Thanks,

RhinoDBA
You are not authorized to post a reply.

Acceptable Use Policy