Permissions issue???

swjohnson · May 5, 2007 2:25AM

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:

\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

petey · May 6, 2007 11:28PM

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.

astreet · May 10, 2007 12:17PM

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?

Surinder · May 16, 2007 2:35AM

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.

petey · May 16, 2007 2:49AM

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

Surinder · May 21, 2007 5:58AM

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.

astreet · May 22, 2007 10:03AM

The link for error 8198 fixed our problem. Bottom line is to make sure that your service account matches your collation.

petey · May 22, 2007 9:27PM

Could you pls elaborate on what you mean by '... service account matches your collation'? Thanks.

astreet · May 23, 2007 12:32AM

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.

michellem33 · May 24, 2007 12:51AM

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.

petey · May 24, 2007 1:54AM

What are the results when you run SETUSER and xp_logininfo, using your domain account as the parameters to these functions? Thanks.

michellem33 · May 28, 2007 1:15AM

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.

petey · May 28, 2007 2:10AM

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.

michellem33 · May 29, 2007 12:15AM

Hi,

Yes I do get the same error

Michelle

petey · May 29, 2007 5:26AM

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.

richard · June 13, 2007 5:32AM

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

Permissions issue???

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk