account setup difficulties...

randyvrandyv Posts: 166
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.
What we do in life echoes in eternity <><
Randy Volters

Comments

  • 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.
    What we do in life echoes in eternity <><
    Randy Volters
  • 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.
    What we do in life echoes in eternity <><
    Randy Volters
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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'.
  • I think it might be because we set these servers up to use AWE.
    What we do in life echoes in eternity <><
    Randy Volters
Sign In or Register to comment.