View DTS package Permissions and Linked Server Access
-
I have a requirement to grant one of our consultants permissions to run a job which in essence runs a DTS package. I had to lock down everything else, since we did not want to him to have access to anything else other than what the DTS package does.
He can see the job when he logs in (he cannot see other jobs as expected), but when he runs the job, it fails as I had feared. It is due to the 'non-sysadmins not having access to xp_cmdshell', and SQL Server is not configured with the SQL Agent Proxy account. I am looking into other ways to enable him to run the job without getting into the SQL Agent proxy account. Making a procedure wrapping sp_start_job did not work either (fails with the same error). The other option was to let him run the DTS package directly. However, there are several DTS packages, and we do not feel comfortable granting him even view permissions to the DTS packages. So, I revoked sp_enum_dtspackages permissions from him. Then I made him the owner of the DTS package we wanted him to run with sp_reassign_dtspackageowner. But, this does not help and he cannot see the DTS package. So, I am stuck. I wonder why the MS did not implement the granular security on the DTS packages. Before I start working on the SQL proxy thing, I would like to see if members have any idea how this can be done, namely
a) How to let a user only view access only to certain DTS packages (like on the jobs, where they can only see what they own)
A corollary to the above setup, on this particular server, there are a couple of remote servers defined, with the "catchall" setup of "for login not defined in the list the connection be made using this security context". We revoked the consultant permissions to sysservers and sp_helplinkedsrvlogin procedure so he cannot see the linked servers. Though he cannot see the linked servers defined, he can still connect to them using the four part syntax (if he know the server name). Is there any way to exclude some users from using the remote servers. I cannot change the linked server security setup, since there are too many other users that use the "catchall" setup and would be too much work to setup a remote login for each user. Once again, a lack of granular security setup issue. Any work arounds?
Thanks.
-
On the First Case:
1. Save the Package to a structured File
2. give permissions to the share.
3. Remove all permisissions from the server
On your Second.
I learned the Hard way that lesson you should grant those permissions on a user by user Level because not even groups can be used!
My $0.02
* Noel -
Noeld:
Was the fourth step in the first case to make batch file that runs DTSRUN of the .dts file?
In the second case, I think I have hit a brickwall and found a security hole. We use trusted connections for SQL Server and these credentials cannot be passed to the linked server unless delegtation is setup (linke attached).
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_2gmm.asp
Now my linked server, though Win2k/SQL2k server, is in a NT domain, so delegation cannot be setup, since it needs AD. The other option is to dump windows integrated security, go back to SQL standard security to make the linked servers work, but I faint when I think about it. I guess it makes a case for upgrading my other NT domain servers to Win2k AD.
I still think MS should by default turn off remote server access to users, and should be explicitly turned on by the DBA. The way they left it is a security hole.
Thanks for your response.
-
1. He could have either that or dtsrun on his client. Still the package, when runned from the client, will have the permissions that his account is allowed!
2. Yep, Kerberos and AD is the only solution for delegation using Integrated security if you are not using standard SQL (I can feel your PAIN) that is the best way unless you can't upgrade
* Noel -
Noeld:
Have you used delegation? Would like to get feedabck or gotchas from folks who already used it.
Thanks.
-
I can tell you a low tech way of doing this.
In our situation clients are sending us data to import. They drop of the file, then drop off a "Trigger" file with the same name (only a ".TRIG" extension). I have a DTS package that looks for trigger files in the DTS directory, and if it finds one it loads the corresponding file. Notification that the process is complete is done via email.
Not that elegant, but it works pretty well and is super simple to set up.
cl
--------------------------------------------------------------
create Proc cmd_GetImportFile @Dir varchar(255)
as
set nocount on
declare @cmd varchar(275),
@FileName varchar(255)
set @cmd = 'dir "' + @dir + '" /A-D /B'
Create Table #Output([FileName] varchar(255))
Insert #Output ([FileName])
exec master.dbo.xp_cmdshell @cmd
delete #Output
where [FileName] is null or charindex('.', [FileName]) = 0
select top 1 @FileName = o1.[FileName]
From #Output o (nolock)
JOIN #Output o1 (nolock) on
LEFT(o.[FileName], charindex('.', o.[FileName])-1) =
LEFT(o1.[FileName], charindex('.', o1.[FileName])-1)
where RIGHT(o.[FileName], charindex('.', reverse(o.[FileName]))-1) = 'TRIG'
and RIGHT(o1.[FileName], charindex('.', reverse(o1.[FileName]))-1) = 'TXT'
set nocount off
select isnull(@FileName, 'NoFile') as ImportFile,
@dir + @FileName as ImportFilePath,
Case
When isnull(@FileName, 'NoFile') = 'NoFile' then 0
Else 1
END as toImport
Signature is NULL
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply