What are the challenges you face when working across database platforms? Take the survey
Options

Permissions issue???

swjohnsonswjohnson Posts: 30
edited June 13, 2007 5:32AM in SQL Backup Previous Versions
I am having a permission issue in v5 that I didn't have in 4.0

I just installed v5 and the service is running under the same domain account as v4.

I have an automated process that reads the log backup files on the local disk and replays them. This has worked since it was designed with 4.0 and using my SQL Login count (sjohnson) which is in the SA role in SQL.

However when I use this same code with v5, I get the following error:

Restoring AOA05 (transaction logs) from:
D:\LogShipping\TLogs\AOA05_Log_20070504_130002.sqb

Error 890: RESTORE DATABASE permission denied for database: (AOA05)
SQL error 4604: There is no such user or group 'sjohnson'.

SQL Backup exit code: 890
SQL error code: 4604

(8 row(s) affected)

name value
exitcode 890
sqlerrorcode 4604

(2 row(s) affected)

I am confused becase I am in the SA role. I should be able to do anything in the DB.

Now if run this same process and same code logging in with a domain account which happens to be in the machine's administrators group and thereby in the SA role, then it works.

The same thing happens with the GUI when I register with my SQL account, the restores fail but with Windows Authentication the restores will work.

So how has the security changed?

SJ

Comments

  • Options
    peteypetey Posts: 2,358 New member
    In version 5, SQL Backup checks that the login user has rights to create backups/restores of the selected databases.

    However, a flaw in the logic causes the checks to fail if the login user is granted the sysadmin role, without also granted explicit access to the 'master' database.

    To work around this, you need to explicitly grant the login user access to the 'master' database.

    This will be fixed in the next release of SQL Backup. Thank you for bringing this to our attention.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    I am receiving the following errors when running a restore:

    Ex.

    5/10/2007 11:00:48 AM: Error 890: RESTORE DATABASE permission denied for database: (Test)

    5/10/2007 11:00:48 AM: SQL error 8198: Could not obtain information about Windows NT group/user 'domain\user account'.


    Have followed the instructions per this posting of granting my domain\user account access to the master database

    Changed service account to user\domain

    Any ideas to what can is causing and work around for the sql error 8198
    and Error 890?
  • Options
    SurinderSurinder Posts: 44 Bronze 2
    Hi,

    I am also getting the error
    "SQL Error 15404: Coul not obtain information about Windows NT Group/User..."

    The account that it's complaining about, is an local admin on the server (windows 2003), DBO on the database (SQL 2005), and has been explicitly given DBO access on all databases.
    Shin..
  • Options
    peteypetey Posts: 2,358 New member
    Are the issues discussed in the following posts applicable to you?

    SQL error 8198 - http://support.microsoft.com/kb/834124
    SQL error 15404 - http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1450867&SiteID=1
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    SurinderSurinder Posts: 44 Bronze 2
    Thanks for that Peter,

    Problem was being caused because the SQL account was not a AD/Domain account. Switched the service to use an AD/Domain account and it works ok now.

    I guess v4 was less picky about this.
    Shin..
  • Options
    The link for error 8198 fixed our problem. Bottom line is to make sure that your service account matches your collation.
  • Options
    peteypetey Posts: 2,358 New member
    Could you pls elaborate on what you mean by '... service account matches your collation'? Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    For the 8198 error message, make sure that your domain account name for the SQLBackup service exactly matches your what is in the windows domain. Ex. if the account is XXXX\sqlusr in the windows domain, make sure the account for the service mathes the exact syntax.

    We discovered this issue since some of our servers use the Latin 1 General Bin collation. We had entered xxxx\SQLUSR for the sqlbackup service which due to our sql server collation was not matching the account in the windows domain. The end result was the sql error 8198.

    Changing the service account to exactly match the account in the windows domain solved the issue.
  • Options
    Hi,

    I am experiencing the same error: SQL error 8198: Could not obtain information about Windows NT group/user 'domain\user account' when I attempt to backup/restore a database using SQL Backup V5.

    I have been able to perform the same processes using SQL Backup 4 (previously installed on the same server under the same windows domain account as SQLServer service) My domain account is in a different domain (the parent domain that the SQLServer and SQLBackup Service Account use (they use the same account)

    My account is also a member of the sysadmin server role and my login has been explicitly mapped to the master database.

    Unfortunately this doens't allow me around the problem. If i login as the service account I can run the restore etc. using SQL Backup V5 but this isn't practical. I used to be able to use my account with SQL Backup 4 and can use my own account when using SQL Server native backup and restore.

    Is the next release also going to include a fix for this as well? When will it be available.

    My SQL Server runs a case insensitive collation.
  • Options
    peteypetey Posts: 2,358 New member
    What are the results when you run SETUSER and xp_logininfo, using your domain account as the parameters to these functions? Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    Hi Peter,

    Using my own account I ran the xp_logininfo and returned the following result:

    Server: Msg 8198, Level 16, State 24, Procedure xp_logininfo, Line 58
    Could not obtain information about Windows NT group/user 'domain/username'.

    but

    I added my account to the Domain Admins group and found the proc returned the correct results.

    Unfortunately I do not wish for all of the SQL database administrators in our enviroment to be a member of the domain admins group.

    Is there a way around this?

    With my login; i used to be able to use SQLBackup V4 commands to backup/restore databases without experiencing the problem.
  • Options
    peteypetey Posts: 2,358 New member
    In version 5, SQL Backup checks to see if the user running the extended stored procedure has rights to backup/restore the selected database. In version 4, this check was not performed, so anyone with rights to execute the sqlbackup extended stored procedure could backup/restore any database.

    Did you receive the same error (8198) when you used SETUSER 'domain\user'? Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    Hi,

    Yes I do get the same error

    Michelle
  • Options
    peteypetey Posts: 2,358 New member
    I do not know enough about Active Directory to advise you further, sorry. What I've found out is the following:

    Looking at this document (http://support.microsoft.com/kb/834124), it would appear that the 'user' has inadequate rights to retrieve information re the domain user (see description for SQL Server state 24). In the same document, under 'Network issues', there are some other suggestions to resolve the 8198 error.

    Also, xp_logininfo, at line 58 (as reported in the error), suggests that SQL Server is using the NetUserGetGroups Windows API to retrieve information about domain groups. This document (http://msdn2.microsoft.com/en-us/library/aa370653.aspx) provides additional information on that function.

    I am unsure of which 'user' is it that requires the 'rights', the SQL Server service startup user (since that's the one that's running the functions), or the logon user (since you mentioned that addiing it to the Domain Admin group resulted in the xp_logininfo function succeeding).

    Please do let me know of any new findings. Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    I have had the exact same problem since upgrading to version 5.1 (from ver 4.6)

    In this case, the server was running with 2 different service accounts for the SQL Server service and the SQL Agent service (reason is that this server also doubles as a repl distributor, so I have the agent running under the repl AD account)

    Originally the SQLBackup service account was running under teh same account as the SQL Server service. Note that both these accounts are low privilege domain accounts and are not local admins on the server.

    Had the permissions error. as in the messages above. eventually changed the SQLBackup account to run the same as the SQL Agent account.

    now got this error

    "Warning 402: Failed to open local data store: Access to the database file is not allowed. [File name=C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Data\(LOCAL)\data.sdf]"

    So then gave the Service account full security permissions on the folder
    "C:\Documents and Settings\All Users\Application Data\Red Gate"

    and this fixed it.

    hope this helps
Sign In or Register to comment.