April 7, 2011 at 7:22 am
We have just installed SQL server 2008 R2 and none of our jobs is running.
All our jobs used to work fine on SQL server 2005. But now with upgrade none of them are running. I am running them as sa but sql server agent has an error saying
The server principal 'sa' is not able to access the database under the current security context.
We did more troubleshooting and found out that sql agent run as sa but in the database it treated as guest.
Does anybody had the same problem?
Any insight will be appreciated.
Thanks
April 7, 2011 at 9:10 am
You probably need to go into security and enable the sa login. If you installed SQL Server with mixed authentication, sa is not enabled by default.
April 7, 2011 at 9:13 am
Login is enabled.
April 7, 2011 at 9:20 am
What does the job history say about why the jobs failed to run?
April 7, 2011 at 9:48 am
I trust you can log into the SQL Box using the sa account, and create things on the server?
Other issues could be with the Jobs, are they SSIS or T-SQL, if they are SSIS do the packages the Package encrypted property set to anything other than Dont save sensitive?
If it isnt a problem with SSIS, try creating a Credential using an account you know has access to the databases, then reference it in a Proxy, finally set a failing job to use the Proxy account, and see if that fixes the problem.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
April 7, 2011 at 9:58 am
I have tried the proxy thing also. Error is Execute permission denied. Some jobs run but some don't now.
April 7, 2011 at 10:04 am
chetnaj (4/7/2011)
I have tried the proxy thing also. Error is Execute permission denied. Some jobs run but some don't now.
I take it the ones that run are using the Proxy account?
Of the ones that dont run are they SSIS jobs? If they are check one of them to make sure that the Package Encryption is not set to one of the Encrypted options but set to Dont Save Sensative.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
April 7, 2011 at 8:52 pm
chetnaj (4/7/2011)
We have just installed SQL server 2008 R2 and none of our jobs is running.
First, did you really upgrade without testing first? :crying:
We ran into problems with jobs, especially ones that ran an SSIS package at some point, in SQL Agent because of changes around how security works in 2008 R2. We ended up changing our SSIS packages so they worked in both 2005 and 2008 R2 before we upgraded production.
I don't recall exactly what we had to do, sorry.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply