Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
shishirkhandekar
Starting Member
23 Posts |
 Posted - 2008-06-11 : 15:09:59
|
| Hi I am facing an issue running an SSIS package on a SQL Server 2005 SP2 machine Here's more information: 1) The SSIS package that dumps data returned from a stored procedure call to an excel (CSV) file. The package owner is a SQL Login called AppAdmin. The login has user memberships/roles in two databases: 1.1) Database AppDB - Has execute and select permissions.1.2) Database msdb - SQLAgentUser role The package has one step that is run as a SSIS Proxy account PrxApp. The Proxy is based on a credential CrdApp which is based on a windows account <domain>\AppAccount. This account is part of the Local Administrators group and also has SA rights in on the server. The same account is also used to run all the SQL Server Services on this machine. The machine is part of a 2-node cluster (A/P). When I try to execute this package (as a schedule or one-off), I am getting the error mentioned in the Subject. I have checked a few posts using google and have acted upon as many suggestions that I possibly could (e.g. change this, change that...did not do any registry modification, etc). Many of these posts have been talking about changing local security policy and adding the user to several groups. Did that, but still does not work in a SQL Agent Job. The account currently has the following rights (local security policy) Act as part of OSPerf. volume maint tasksLock pages in memoryReplace process level token One thing that I did notice and don't know if that affects the issue is that the following groups (and SQL Logins) are missing from my Windows groups listing (Computer Management\Users and Groups\Groups). Note that this is a Windows 2003 machine. (a) SQLServer2005MSSQLUser$<machine_name>(b) SQLServer2005SQLAgentUser$<machine_name> Now these groups (and logins in SQL Server) do exist on some other SQL2005 machines that I have checked to see what could be wrong. Any help at all is greatly appreciated. If you need any more information please let me know. ThanksShishir Khandekar |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-06-12 : 01:46:53
|
| One thing you do not mention is whether the CSV you are going to write to is residing on the server or on a network drive?If it is on a network drive, make sure you have write permissions on the directory. |
 |
|
|
shishirkhandekar
Starting Member
23 Posts |
Posted - 2008-06-12 : 09:02:15
|
| Yes, it does reside on the network drive and all the permissions do exist. The problem is that it doesn't even reach there. I profiled and notice that it fails as soon as it starts. In the profiler I saw calls that were trying to verify the memberships of the proxy and job owner accounts etc and then a failure.-SK |
|
|
|
shishirkhandekar
Starting Member
23 Posts |
Posted - 2008-06-12 : 09:03:04
|
| Forgot to mention that the permissions checks were successful. |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-06-16 : 01:39:30
|
| Is the job owner set to the SQL Service account or a network account? Does the account used have permissions on the file system and on the database to do what you are trying to do? |
|
|
|
shishirkhandekar
Starting Member
23 Posts |
Posted - 2008-06-16 : 09:08:22
|
| Rick, yes the account had all the permissions to the file server and database. On the machine itself it was sysadmin and therefore "SA" in the database. Another interesting thing is that this kind of fixed itself, well not exactly, but the only thing that was done was to change the SQL Server Service account to "local system" and then change it back to the domain account that was being used. The jobs are running successfully now thru the sql agent. Any ideas what could have happened with this reset?ThanksShishir |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-06-16 : 10:21:45
|
| Unless you had trouble with your PDC or your network, no, I can't think of anything. |
|
|
|
|
|
|
|
|
The process could not be created for step 1 of job