account setup difficulties...

randyv

In reading how to set up for log shipping, the paper says to create a domain account.

Do you mean an Enterprise Domain account? My network admin tells me that a local domain account, even one that is a member of the domain administrators group, does not have authority to start services on a local system.

The other problem I'm having, is that I thought I'd just create a local admin account, make it a member of the sa group on SQL Server, and then clone it on the other box. In effect, this account would exist identically between the servers. Then giving the account access to the share should provide the necessary permissions. BUT...

I set SQL Server to login with this account and it works fine.
Then I set SQL Server Agent (which quits working the minute I change the log in with setting from local account to my new account) to use the same account I just set SQL Server up to use, it fails with an error to the event log - the summation of which is - user 'Unknown' must be a member of SysAdmin?!

The account is a member of SysAdmin, it is tagged for all databases. It is a legitimate SQL account (I can login with it), in addition it is a Windows Admin account.

This should work, but does not.

Anyone else having this problem?

Thanks in advance.

randyv

Well -
Just got off the phone with MSFT, and this was a real problem. Took the experts over 2 hours to diagnose... the error messages really do not indicate the root cause clearly.

This all started because the set up paper for log shipping states "Note that the SQL Server service startup account needs to be a domain user..."

Problem was, when I assigned that account to SQL Server service the Agent service broke. Trying to assign the same account to the agent service kept generating an error that the 'unknown' account was not a member of SysAdmin.

Turns out that is not the root cause at all.

Our configuration uses AWE to access memory. The group policy editor, windows security setting local user rights has an entry "Lock Pages in Memory". The account that is set as the service startup account for SQL Server needs to have permissions on this entry.

Once that permission is granted, things work fine.

Now on to setting up log shipping.

Regards.

randyv

One more thing for anyone trying to set up for this implementation and having problems...

Once you have a domain account successfully set up and working on both the primary and standby, and you want to install SQL Backup to work using these accounts, you may hit a snag with options...

Account does not have write permissions to a Red Gate HKLM registry setting.

Find the Red Gate folder, right click and give the domain account the necessary permissions.

Brian Donahue

Thanks! That should prove to be helpful.
This is the first time I've seen an account which needed the 'lock pages in memory right'.

randyv

I think it might be because we set these servers up to use AWE.