November 10, 2008 at 4:26 pm
First off, I have googled and read up on this issue for two weeks now and have not yet found a solution. As an FYI, I did not build this environment as I’m new here. Unfortunately, I’m the first DBA the company has ever had (15+ production servers with 100+ production databases).
Environment:
Itanium server running SQL 2005 SP2 on Windows 2003 Enterprise Edition SP 2 connected to a SAN device. This box does have another strange security problem that I am troubleshooting and that is I am unable to use SQL Server 2005 Management Studio to view the Event View logs on the server (I get an access denied message).
Steps in process
A developer creates an SSIS package (on Windows XP 32 bit) that does file manipulation using Windows Authentication (reads, moves and creates directories), reads data from the Excel file and from two SQL 2000 databases on another server (using SQL authentication) before writing data to a database on the server where this package is running.
We are using config files to manage connection strings and passwords and the SQL Server user has full rights to that folder.
The packages goes through QA where it is run by the developer and then I, the DBA, want to deploy it into production.
I import the package into the SQL Server 2005 box, running SQL Server under a domain user (not administrator) account. SQL Server Agent and IS run under the same account. This account has full control to a share on a file server where the files live and where the file manipulation is executed. The folder is a share and at present the service account has full control on both the share and the security.
Connecting to the SQL server under my domain account (which is aliased as sysadmin), I am able to successfully run the package through IS.
So far so good.
Now when I schedule the package as a job, the job fails. At this point I’ve tried so many security configurations that I’m bleary eyed. But basically, the job is running under the SQL Server Agent security context, the package is owned by the Domain Account that is running all three services and I’ve explicitly added this user to the msdb as db_owner, dtsadmin, and SQLAgentOperator role as well as Datareader and DataWriter. I did an update query to sysdtspackages90 to set the owner sid to be the same as the SQL Agent service account (I’ve also tried it as “sa” but that didn’t work either). I even put the service account into the local Administrators group on the server.
However, if there are no files in the folder waiting to be manipulate, the job runs successfully.
So then I tried to create a proxy for SQL Server agent with a credential. I even gave the credential my domain account user and password to use (the one that works when I run it manually) just to see what would happen, but no go.
My goal is to not only fix this one issue, but make sure that the 20 other packages that are in development deploy with a lot less frustration. Please tell me I’m an idiot and am missing something stupied, ie. easy.
February 23, 2009 at 10:18 am
I don't know if you've gotten your problem solved yet or not, but just so you have something to try - consider the following:
Have you looked into the ProtectionLevel of the package? Do a search on the forum here for ProtectionLevel or EncryptSensitiveWithUserKey. The root of this problem has to do with the fact that MS set the default property to EncryptSensitiveWithUserKey, which means if the account you intend to run the package under as a job is not the same account as the person that created the package - you could have some issues. Solution here is to change every .dtsx page so the ProtectionLevel is set to DontSaveSensitive. Doing this will mean that you will need to enable Package Configurations and provide various connection string info for things like ftp passwords, Initial Catalog or ServerName, etc. There are some good examples of this here and on http://www.jumpstarttv.com (by Brian Knight).
Another thing to look at is how you are calling the job step in Agent. One of the suggestions that was put out as a Best Practice for SSIS at a recent SQL Connections conference was to run the job step as an Operating System (cmdexec) type instead of SQL Server Integration Services type. For the Run As selection - use a Proxy Account based upon the proper Credential (again look at http://www.JumpStartTv.com for examples). The final step here is the Command window. Paste in the required command line code to run the dtsx package. Example: dtexec.exe /FILE "C:\Program Files\Microsoft SQL Server\90\DTS\Packages\MyPkgName.dtsx" - followed by any additional switches, etc. This is the same code that is generated when you double click on a SSIS package in Explorer and then go through each option, making the appropriate selections. The final option, displays the command line string that you've built up from all the above selections. It is this code that you would paste into the Command window for your Job Step - preceded by the "dtexec.exe" command.
Good luck, hope this helps.
Mark
February 23, 2009 at 10:34 am
So then I tried to create a proxy for SQL Server agent with a credential. I even gave the credential my domain account user and password to use (the one that works when I run it manually) just to see what would happen, but no go.
You are on the right track but it will not run without admin level user permissions both in SQL Server and the network level because when the Agent is permforming tasks in network folder Microsoft wants such tasks going back to someone with admin level permissions. Check the thread below for details.
http://qa.sqlservercentral.com/Forums/Topic661486-148-1.aspx
Kind regards,
Gift Peddie
February 24, 2009 at 9:09 am
I had the same issue; Try http://support.microsoft.com/kb/918760 for the answer
SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply