February 11, 2009 at 11:40 am
I am having this same error "required privilege is not held"
sql 2000 was previously running on one half of a cluster, now for various reasons we have failed it over to the partner). In the new location, many jobs will not run except under sa accounts.
I confirmed that the proxy account is set correctly (and reset it with both SQL and EM).
The proxy account is a domain account (the very same one as worked fine on the previous server) and is also the account that sql server and sql agent are running successfully under (so as I understand it, running a job under SA will still use that account).
Some critical jobs have been made usable by sa account, but this is not a good fix. What ELSE could possibly be causing this problem?
...
-- FORTRAN manual for Xerox Computers --
February 12, 2009 at 2:41 am
required privilege is not held
This sounds like a Windows rights problem. Windows rights are not cluster-aware, and have to be set for each server in the cluster.
Does the proxy account have the 'log on as a batch job' privilege on your failover server. While you are checking that, look for any other differences in Windows rights between your primary and secondary server.
One way to set Windows rights is to get your Windows admin people to build a Group Policy Object (GPO) that specifies them, and apply that GPO to all servers in your cluster. If you don't use a GPO, then they need to be set manually on each server.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 12, 2009 at 6:49 am
EdVassie (2/12/2009)[hr...
Does the proxy account have the 'log on as a batch job' privilege on your failover server. While you are checking that, look for any other differences in Windows rights between your primary and secondary server.
I just checked and it has that right. What puzzles me is that both SQL service and SQL agent are also the same account, and SA runs the jobs just fine (my understanding is that SA would run under the SQL service account) suggesting that it is something outside of windows.
...
-- FORTRAN manual for Xerox Computers --
February 12, 2009 at 7:23 am
Another place to look is if the proxy account has read & execute rights on the SQL program folders. Also, check if the proxy job is trying to save job output to a specific file (see the Advanced tab in the job) - in SQL 2000 different maintenance levels allow this to work or cause it to fail. When you look at the authorities used with different aspects of a job IMHO the security model in SQL 2000 is seriously flawed.
One way to debug this is to start with something simple. Create a new SQL Agent job that runs with the proxy authority and run a Windows DIR command. If it fails, try the same job with SA authority to make sure there are no spelling-type errors.
When you get the DIR command to work with the proxy, you know the basic proxy functionality works.
Next is to change the job to do a SELECT from a small SQL table (select * from master..sysdatabases) and try to get that working with the proxy.
When you can do a Windows command and a SQL statement using the proxy, you should change your test job to run what you really need to do. If that works, then compare your test job definition with your real job definition to find any differences and resolve them. Hopefully by then you should have fixed your proxy problem.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 17, 2009 at 11:50 am
EdVassie (2/12/2009)
....One way to debug this is to start with something simple. Create a new SQL Agent job that runs with the proxy authority and run a Windows DIR command. If it fails, try the same job with SA authority to make sure there are no spelling-type errors.
...
The problem occurs with the simple DIR. I went back and retested
* when the owner of the job is an administrator it works.
* when the owner of the job is the proxy account, it works
* When the owner of the job is anything else, it does not
* it does work fine on the other half of the cluster.
[the proxy account is, for current testing, a local admin on this machine]
...
-- FORTRAN manual for Xerox Computers --
February 18, 2009 at 3:09 am
Try some testing where the proxy account is not a local admin and where it is a local admin.
If it fails when the proxy is not a local admin and works when it is a local admin then you have a Windows permission problem. In my experience, the most likely permission problem is that
* non-admin accounts do not have access to the folder containing the SQL binaries
* non-admin accounts do not have read and execute rights to C:\Windows\System32.cmd.exe
Another possible option is that non-admin accounts do not have 'Bypass Traverse Checking' right. All accounts should have this right.
If you can get your proxy working as a non-sysadmin, you could find that things will work for any job owner.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 18, 2009 at 3:10 am
PS If it works on one node of the cluster and not on the other, this is telling you there are differences in permissions between the two nodes.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 27, 2009 at 1:01 pm
EdVassie (2/18/2009)
Try some testing where the proxy account is not a local admin and where it is a local admin.If it fails when the proxy is not a local admin and works when it is a local admin then you have a Windows permission problem. ....
Sorry for the delay in response, but I got sidetracked to other projects. Back to trying to solve this one, however.
The proxy account is a local admin (for now)
If I give ownership of the job to proxy account, it runs.
If another user owns the job it fails.
...
-- FORTRAN manual for Xerox Computers --
March 25, 2009 at 2:17 pm
Hello, I had exact same problems testing proxy on a not clustered environment.Proxy has all rights required, when the job owner is testuser that has only access to one table and cmdshell permission - it doesn't work. When I added select permissions to this table to proxy account too, the job worked, it just took 100 records from a table and put it in a file on db drive. But I am not sure if I really had to grant select permissions on this table to proxy, it was never mentioned in any of kbs.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply