August 11, 2010 at 3:32 am
Good morning all,
I'm banging my head against the wall with transaction log shipping at the moment.
I have a second server configured but the jobs that run when setting up TLS are failing with access denied messages.
I've checked all the folder permissions and everything should be ok except for the primary server's SQL Server Agent account is using an account called '.\Administrator'.
This server was set up long before I took responsibility for it and I have no idea what the password for this account is and I think it is this account which is denied permissions at the folders required for TLS.
So my question is, if I change the SQL Server Agent to log on as a windows domain admin will that screw anything up, will the service cease to run and are there any other consequences I should be aware of.
If I'm right the account is the localhost admin account and so is not recognised by the domain. As this is a very important server I really do not want to change anything that will break it.
Thanks in advance,
Paul
Keep the rubber side down and the shiny side up.
August 11, 2010 at 5:37 am
which job fails, the backup log on the primary or the copy job on the secondary?
what account does SQLAgent on the secondary run under?
---------------------------------------------------------------------
August 11, 2010 at 6:42 am
Initially I was trying to backup to a folder on the secondary server and it was failing.
Since changing to a folder local to the primary server the backup completes successfully but now the restore to secondary db fails with the access denied message.
The logon running the service on the secodary server has permissions on the backup folder of the primary so I'm unsure why that would be happening.
I've also checked the surface area configuration on both machines are setup to allow local and remote connections over TCP/IP and named pipes.
Keep the rubber side down and the shiny side up.
August 11, 2010 at 7:31 am
In logshipping I usually run sql server and sqlagent services on both servers under the same domain user account, avoids a lot of access problems.
Make sure the log backup directory on the primary is shared out. Grant the SQLagent account on the secondary read access to this share. Use the permissions button forr users who access this folder over the network as well. Therefore SQLAgent on secondary needs to be a domain user.
If you have to change the account make sure you use configuration manager to do so, and grant the account log on as a service rights and password never expires.
If this is windows 2008 check User Access Controls are not hurting you as well.
---------------------------------------------------------------------
August 11, 2010 at 8:57 am
I second George's comments. Cross server authentication is a mine-field if it isnt set up with the right thinking-hat on in the first place.
Good Luck 🙂
Adam Zacks-------------------------------------------Be Nice, Or Leave
August 12, 2010 at 9:31 am
I finally nailed it!
The problem turned out to be the login account used to run the MSSQLSERVER Service. I had the account for the agent service on both machines set to the same login but once I changed the MSSQLSERVER service on the secondary server to the same account everything started working as it should.
Thanks for your help, it's been an educational experience if nothing else.
Paul
Keep the rubber side down and the shiny side up.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply