I desperately need help with this problem. I've spent an entire week, tried another server, new install of SQL Server, you name it. I think it is permissions but I can't be sure and am lost on what to try anymore, I feel I've tried everything:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=189199&SiteID=1
nothing has changed, I don't know why any agents I create or even recreate the package won't run this package or any other packages I have created.
Dba123, is the package stored location in MSDB ok?
i.e. does the account that the SQL agent runs under have access to MSBD/sysdtpackagefolders and sysdtpackages90 tables? Specifically does the SQLAgent domain account (or local account) have the connect permission (under db properties) to MSDB and is a member of the SQLAgentOperatorRoll, SQLAgentUserRoll, SQLAgentReaderRoll rolls (under login properties)?
Try setting up a new domain or local account and assign it as the SQLAgent account.
MikeC
|||>> i.e. does the account that the SQL agent runs under have access to MSBD/sysdtpackagefolders and sysdtpackages90 tables?
how do I check this? It says in my agent job history that the account is servername\SYSTEM but I don't see that account anywhere in my sql logins section
>>>>Specifically does the SQLAgent domain account (or local account) have the connect permission (under db properties) to MSDB and is a member of the SQLAgentOperatorRoll, SQLAgentUserRoll, SQLAgentReaderRoll rolls (under login properties)?
yes, as far as I can see
>>>Dba123, is the package stored location in MSDB ok?Dba123, is the package stored location in MSDB ok?
what do you mean by OK? If you mean does it run, I can run it yes from Integration services in Managemenet Server or run it fine in Debug mode from VS 2005
|||There are so many security areas and permissions areas. Are we talking about the Server Properties / Permissions or Security / Logins, or Database / Security / Users & Roles sections?
I still am not sure why I can't see my servername\SYSTEM account in any of these areas. I only see NT AUTHORITY\SYSTEM, is that the same?
What's the difference also between GRAND and WITH GRANT in the Server Properties?
I also checked the permisions for SQLTEST\SQLServer2005SQLAgentUser$SQLTEST$MSSQLSERVER and gave it all access to everything just to see if that would work..not sure if this is the right item to look at but seems ovious....but for some reason when I try to give it rights by clicking on the properties of this login to one of my databases...the database my SSIS package runs against, I save it but then I go back to the properties again later and it's unchecked again...so the checkboxes aren't sticking when I specify the rights to that database in this login's permissions tab (section)
|||Dba123, when I said OK I mean is the security access ok for the account that the SQLAgent is running under.
NT AUTHORITY\SYSTEM is the same as "Local System", this account by default does NOT have access to MSDB and the correct rights for SQLAgent work.
M$ is making security easer ;) isn’t it? The local system account is very powerful but crippled in SQL05 - From books online (12-05 update).
| Important: |
|---|
| Local System is a powerful account; it may not be appropriate for all Service settings. For more information, see Security Considerations for a SQL Server Installation. |
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/309b9dac-0b3a-4617-85ef-c4519ce9d014.htm
This is why I recommend that you use a domain or local account for the server. Preferably the account that was specified during SQL05 install. Use the SQL Configuration Manger to change the account:
| Important: |
|---|
| Always use SQL Server tools such as SQL Server Configuration Manager to change the account used by the SQL Server or SQL Server Agent services, or to change the password for the account. In addition to changing the account name, SQL Server Configuration Manager performs additional configuration such as setting permissions in the Windows Registry so that the new account can read the SQL Server settings. Other tools such as the Windows Services Control Manager can change the account name but do not change associated settings. If the service cannot access the SQL Server portion of the registry the service may not start properly. |
I hope that the SQL Config Mgr also sets the appropriate rights on the myriad of SQL objects necessary for the account to function correctly.
The "Grant with" or GRANT OPTION allows that principal to grant the grant right to other principals.
Also, make sure that the SSIS service account security setup is ok - i.e. that the account is a local or domain account (not local system) and that the rights were specifically setup for this account with SQL setup or SQL config mrg.
Somewhere I would hope that there is a definite list of all security settings applied during install for all SQL accounts and roles. I have not found this list in SQL05...
MikeC
|||
alright, our network guy figured it out, I just needed another set of eyes after my brain is fried by now.
It ended up being one of 2 things:
1) The context user specified to run the job did not have rights to a shared directory that my SSIS package was spitting out a flat file to via flat file connection manager
2) I did not have a UNC path to one of my flat file connection managers, I had mapped it to a mapped drive which is stupid but I didn't see that. Therefore the agent job was failing because there was no H drive on the PC we were trying to run this from because the profile had no H
simple yet overlooked.
Thanks so much for all the help you guys, it ended up not being SQL permissions locally in Management Server, rather the shared folder permissions or the lack of UNC in my SSIS package defined in the flat file connection manager.
No comments:
Post a Comment