Authentication error when using windows authentication

ToehoToeho Posts: 73
edited November 7, 2007 9:54PM in SQL Backup Previous Versions
When trying to do a restore with the gui and using windows authentication we get an error saying that the user does not have backup or restore permissions. The user is a local windows administrator with sa rights on the database server. When switching to sql authentication we can get the restore process started but can not browse to the backup files or restore paths.
· Share on Twitter

Comments

  • Jason CookJason Cook Posts: 185
    Hi,
    What is the specific error message you are getting, and what version of SQL Backup are you using?

    Thanks,
    Jason
    · Share on Twitter
  • ToehoToeho Posts: 73
    version 5.1

    When using our windows account to log in we get the following error:

    IDXJDEPD5\IDXJDEPD5: Restore - Failed
    =====================================

    Restoring full backup - Failed

    This operation failed with errors.

    Restoring test (database) on IDXJDEPD5 instance from:
    \\SCIDXSQL64MSDTC\w$\backups\pd5\HOLDING_09102007_055254_FULL_02.sqb
    \\SCIDXSQL64MSDTC\w$\backups\pd5\HOLDING_09102007_055254_FULL_01.sqb
    Error 890: RESTORE DATABASE permission denied for database: (test)
    SQL error 15404: Could not obtain information about Windows NT group/user 'CMD\bills', error code 0x5.
    SQL Backup exit code: 890
    SQL error code: 15404



    Updating SQL Server information - Pending

    Operation pending.
    · Share on Twitter
  • Jason CookJason Cook Posts: 185
    Hi,
    The error message "SQL error 15404: Could not obtain information about Windows NT group/user ..." occurs because SQL Backup is unable to query the active directory domain controller regarding the user in question.

    The most common reason for this is that the SQL Backup Agent is running as a local service, which does not have permissions to access the domain controller on the network. Changing the SQL Backup Agent to run as a domain account with sufficient permissions to access the domain controller should resolve the problem.

    Hope that helps,
    Jason
    · Share on Twitter
  • ToehoToeho Posts: 73
    I am running under a domain account, I have switch to another domain account and I am getting a different error now. Again this account is a local administrator on the server.


    DLEKAENT03\SQL2005: Backup - Failed
    ===================================

    Performing full backups - Failed

    This operation failed with errors.

    Backing up ACS (full database) on SQL2005 instance to:
    w:\backups\ACS_09102007_190838_FULL.sqb
    VDI error 1010: Failed to get configuration from server. Check that the SQL Server instance is running, and that you have the SQL Server Systems Administrator server role. Error code: (-2139684861: The api was waiting and the timeout interval had
    elapsed.)
    SQL error 3013: BACKUP DATABASE is terminating abnormally.
    SQL error 3201: Cannot open backup device 'SQLBACKUP_A74B1712-16A8-4BF2-9F22-0EC20F3D941D'. Operating system error 0x80070005(Access is denied.).
    Memory profile
    Type Maximum Minimum Average Blk count Total
    Commit 795869184 4096 2014377 892 1796825088
    Reserve 4128768 8192 274095 134 36728832
    Free 161202176 4096 1999134 157 313864192
    Private 795869184 4096 3137206 560 1756835840
    Mapped 1060864 4096 107953 59 6369280
    Image 24735744 4096 172847 407 70348800
    Warning 300: Backup failed. Retry attempt: 1
    Warning 300: Backup failed. Retry attempt: 2
    Warning 300: Backup failed. Retry attempt: 3
    Warning 300: Backup failed. Retry attempt: 4
    SQL Backup exit code: 1010
    SQL error code: 3201



    Updating SQL Server information - Successful

    This operation completed successfully.
    · Share on Twitter
  • ToehoToeho Posts: 73
    here is some more information

    I am running the red gate service and the sql service under a domain account, I have tried several even my own. If my windows account is given rights to the sql server via a group as in local administrators I get the error below. If I give my windows account right to sql individually I do not get the error. Any ideas as why this is happening as it has not been an issue on all of our installs??

    DLUCOSQL01: Backup - Failed
    ===========================

    Performing full backups - Failed

    This operation failed with errors.

    Backing up ACS (full database) to:
    w:\backups\ACS_09132007_151700_FULL.sqb
    Error 880: BACKUP DATABASE permission denied in database: (ACS)
    SQL error 8198: Could not obtain information about Windows NT group/user 'CMD\BILLS'.
    SQL Backup exit code: 880
    SQL error code: 8198



    Updating SQL Server information - Successful

    This operation completed successfully.
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi,

    I think the easiest explanation is that the BUILTIN\Administrators group has either been removed from SQL Server's security (if that's possible) or the group is not a member of the sysadmin server role.
    · Share on Twitter
  • ToehoToeho Posts: 73
    Local admin group is still in sql and has sa rights. I have gone so far as to create a new group and give it access to sql and give it sa rights, the users of this group will get the error but when their windows account are granted access individually and given sa rights they do not get the error. I would prefer to grant access via a group and not individually. This appears to be random and does not happen on every install.
    · Share on Twitter
  • Adam.BeanAdam.Bean Posts: 28
    We are having the same problem with NT groups to a different domain with in our network. Have you found any resolution to this problem?

    Thanks
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    I can't rightly say what the solution is because the problem itself isn't entirely clear. In one case it does look like a failure to get information from Active Directory for the domain accounts using SQL Server or the SQL Backup Agent Service, either because the server could not be contacted or there is insufficient permissions to query it.

    If SQL Backup Agent and SQL Server's security accounts cannot contact a domain controller to get the security ID for the SQL Backup Agent's account, you will get this error. Because SQL Backup sets up a secure communications channel between SQL Backup Agent and SQL Server Service, both processes need to access an Active Directory server in the domain to retrieve the SID for the SQL Backup Agent account.
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    You may also find this useful:

    http://support.microsoft.com/kb/834124
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    Run the following when logged in to SQL Server using the same login that the SQL Backup Agent service is running as:

    SETUSER 'CMD\BILLS'

    Note the SQL Server state for the error that's reported. Look in the document referred to by Brian for the exact cause of the error.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
Sign In or Register to comment.