January 27, 2009 at 12:57 pm
Hi there,
I have setup (tried to) replication on one of our servers. Going from one database to another, using transactional replication.
I have a windows username specifically for replication. After going through the wizard the jobs fail to run; exhibiting the following message:
[font="Courier New"]Executed as user: [MyDomain]\[RepUser]. (unknown security error). The step failed.[/font]
The user is db_owner on distribution, and also the 2 other databases being used in the replication. I made the user sysadmin and also a local administrator, however this didn't make any difference.
Initially the jobs were created with my username as the owner. I changed this to being the replication user, and then I got the following message:
[font="Courier New"]Unable to start execution of step 2 (reason: JobOwner [MyDomain]\[RepUser] doesn't have permissions to use proxy 1 for subsystem LogReader). The step failed.[/font]
So I gave the replication user permission by including their user in the proxy account principals list for the proxy; and then the job started giving me the original message (unknown security error).
I did a bit of a search and came up with nothing except for an issue that talked about changing the sqlagent logon account through the SQl config util rather than the windows aplet. I carried out the steps to overcome the problem , just in case this was a similar issue (although knowing that nothing similar had happened to our server). But no difference.
Incidently these jobs run fine if I run the step as the SQL Agent Service Account rather than the proxy created during the replication setup.
Any ideas as to what could have happened here or at least some things to check?
Thanks in advance for any assistance you can offer.
Regards
Troy
January 27, 2009 at 4:25 pm
The First step is to make the Log reader run in verbose mode.
See if that gives you more info.
Is this what you are talking about? http://support.microsoft.com/kb/911305
* Noel
January 27, 2009 at 11:55 pm
Hi,
Thanks for the response; one dumb question though ---> how do I get the log reader doing it's thing in verbose mode?
That whitepaper is about the only thing I found with others who had the same error (unknown security error), so I carried out the workaround mentioned in there, just on the off chance that something had gone a little haywire, but it didn't make any difference.
Thanks again for the post; it would be great if you (or anyone else) can assist me getting to the bottom of this issue.
Cheers
Troy
FYI - it is incidently on a 64bit Windows 2003, running 64bit SQL Ent Ed
January 28, 2009 at 8:28 am
how do I get the log reader doing it's thing in verbose mode?
You open the SQL Agent Job that is running the LogReader and then add the Following parameters to it:
-Output output_path_and_file_name -OutputVerboseLevel 4
Don't forget to Stop and Restart the Job!
* Noel
January 28, 2009 at 1:23 pm
Hmm okay well I put those bits and peices into the job step and tried to start the job, but the "unknown security error" jumps up and the job doesn't start. *groan*
Haaaaallllp!!:crazy:
January 28, 2009 at 2:18 pm
TroyG (1/28/2009)
Hmm okay well I put those bits and peices into the job step and tried to start the job, but the "unknown security error" jumps up and the job doesn't start. *groan*Haaaaallllp!!:crazy:
That means that the "Account Running the Job" Permissions are NOT right to run LogReader.exe.
To troubleshoot this:
1. Make sure you can log in the SQL Server instance using that account.
2. Make sure this account is member of the db_owner role of the publication AND distribution databases
We'll take from there. Let me know after you do that!
* Noel
January 28, 2009 at 2:36 pm
noeld (1/28/2009)That means that the "Account Running the Job" Permissions are NOT right to run LogReader.exe.
To troubleshoot this:
1. Make sure you can log in the SQL Server instance using that account.
2. Make sure this account is member of the db_owner role of the publication AND distribution databases
We'll take from there. Let me know after you do that!
Yeah that is what I had guessed, but everything that I looked at appeared correct.
The user is db_owner in the publication databases as well as the Distribution database, and the user can log onto the SQL instance and see the necessary db's.
I notice that when connected via SSMS to the instance I cannot see SQLAgent; could this be part of the issue?
Thanks again for your assistance!
Cheers
Troy
January 28, 2009 at 3:10 pm
I notice that when connected via SSMS to the instance I cannot see SQLAgent; could this be part of the issue?
No that just indicates that the user Does not have permissions on msdb.
Next step is to make sure SQL Agent has the permissions it need to impersonate the account.
Can you temporarily make SQL Agent Service account member of the local admins on that machine ?
And try again ?
* Noel
January 28, 2009 at 3:31 pm
noeld (1/28/2009)
Next step is to make sure SQL Agent has the permissions it need to impersonate the account.
Can you temporarily make SQL Agent Service account member of the local admins on that machine ?
And try again ?
Added the SQLAgent user to Administrators group on local machine.
Restarted the SQLAgent in case it had to pickup new group membership or something
Tried to start job
Same error
The error specifically says
Executed as user: [Domain]\[ReplicationUserName]. (unknown security error). The step failed.
So given that the error is specifically saying the Replication username, this would suggest to me that those permissions are set okay....maybe *shrug*....
oh and also just to confirm in active directory the user is setup to trusted for delegation.
Cheers
Troy
January 28, 2009 at 3:42 pm
Let's try now to use replication tools to verify that all credentials and proxy stuff are setup properly.
Open Replication monitor
Then Right Click on the Publication and select "Properties"
From there go to the Agent Security page and Re-enter the "ReplicationUser" Credentials.
Hit Ok and restart the agent.
What gives ?
* Noel
January 28, 2009 at 3:56 pm
Okay completed those tasks.
Restarted SQLAgent
Checked the job; and nope. Still the same (Unknown Security Error)
January 29, 2009 at 5:23 pm
Is anyone else able to assist with this at all? Any thoughts/ideas/etc...
We are going to be migrating onto this server in the next few weeks and need to have this replication going :ermm:
Thank you!
February 16, 2009 at 6:52 pm
Well in the interests of completness in case someone else experiences this issue here is the resolution we had to do:
After getting confirmation from Microsoft it would appear as though the following need to be set (for BOTH the SQLAgent username and the username that is setup as a proxy) in the Local Security Policy are required:
· Adjust memory quotas for a process
· Bypass traverse checking
· Create global objects
· Impersonate a client after authentication
· Log on as a batch job
· Log on as a service
· Lock pages in memory
· Replace a process level token
· Act as a part of the operating system
They have linked to the following documents as evidence of this requirement:
http://msdn.microsoft.com/en-us/library/ms189064.aspx%5D
and
http://msdn.microsoft.com/en-us/library/ms178142(SQL.90).aspx
Basically the short version of this (and reading between the lines) is that when SQLAgent uses a proxy for kicking off a job that proxy will need to have the same access permission's as the SQLAgent account in order to execute jobs in the same context; this doesn't extend to the database permission's, only at the operating system level - hence the LSP change requirement.
Sort of makes sense actually; but I vented my concern for the lack of documentation around this requirement, so who knows if anything will come of it *shrug*.
Anyhow my world is a some what happier place now.
Cheers
Troy
February 18, 2009 at 8:26 am
TroyG (2/16/2009)
Well in the interests of completness in case someone else experiences this issue here is the resolution we had to do:After getting confirmation from Microsoft it would appear as though the following need to be set (for BOTH the SQLAgent username and the username that is setup as a proxy) in the Local Security Policy are required:
· Adjust memory quotas for a process
· Bypass traverse checking
· Create global objects
· Impersonate a client after authentication
· Log on as a batch job
· Log on as a service
· Lock pages in memory
· Replace a process level token
· Act as a part of the operating system
They have linked to the following documents as evidence of this requirement:
http://msdn.microsoft.com/en-us/library/ms189064.aspx%5D
and
http://msdn.microsoft.com/en-us/library/ms178142(SQL.90).aspx
Basically the short version of this (and reading between the lines) is that when SQLAgent uses a proxy for kicking off a job that proxy will need to have the same access permission's as the SQLAgent account in order to execute jobs in the same context; this doesn't extend to the database permission's, only at the operating system level - hence the LSP change requirement.
Sort of makes sense actually; but I vented my concern for the lack of documentation around this requirement, so who knows if anything will come of it *shrug*.
Anyhow my world is a some what happier place now.
Cheers
Troy
THANK YOU very much for the answer.
It is really a shame that you had to do that manually and there is no UI or documented method about it.
It will definitely help us in case we decide to change SQL Agent Account and the proxy "remains".
* Noel
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply