Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
RobMarsh
Starting Member
28 Posts |
 Posted - 2002-08-06 : 13:47:41
|
| Hi guys,I recently got a modified version of log shipping to work on a couple of servers here, and being quite pleased with the results decided it was time to start rolling out a nice standardised set of scripts to other boxes that needed it doing on them.On one particular box (curse it) I get the following error during a job that performs a backup and then tries to copy the backup file to the standby machine:The job failed. Unable to determine if the owner (domain\username) of job testjob has server access (reason: Could not obtain information about Windows NT group/user 'domain\username'. [SQLSTATE 42000] Error 8198)). Now, the user running this is a dedicated SQL user that is used to run the SQL Services as well as jobs. It has Domain Administrator as well as Local Admin rights.After a bit of hunting I discover that this seems to be a known bug. The 2 servers in question are W2K boxes with SQL 7.0 on them. The network however is an NT4 domain and so all users used to run jobs are from this NT4 domain. It is also only an 'occasional' bug, which explains why it only happens on some servers...The big problem I have is that whilst the workarounds given on the Microsoft Knowledge Base Article (http://support.microsoft.com/default.aspx?scid=kb;en-us;q241643) are valid, they may not be suitable for me (typical really).The first workaround is to run the job as 'sa'. Can't do this - the job uses an OS command and need an NT user to perform it properly.The second is to install Active Directory on the server, and add the user to the Pre-Windows 2000 Compatible Access Group. Well, upon mentioning this to Network Support, they nearly threw a wobbly - claiming it would mess up our Novell Network.So here I sit wondering what the next step is.. anybody know of any other workarounds? Or do I need to shout at someone to get Active Directory put on?Rob |
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-08-06 : 15:03:54
|
| Set the job owner to saThe job will run under the context of the SQL Service account ( a valid NT user) however the job will not call xp_logininfo when run (as it would if run under a domain account) and will thus run fine.When a job is run with a domain account owner SQL Agent uses an extra stored procedure(sp_sqlagent_has_server_access - you can check with profiler) to check server access. This sp calls xp_logininfo hence the job fails.Jobs run with sa job owner do not execute this sp so they workHTHJasper Smith |
 |
|
|
RobMarsh
Starting Member
28 Posts |
Posted - 2002-08-06 : 15:31:42
|
| I tried this, but it still doesn't work - It performs the Backup fine,but falls over when it tries to copy the file from one server to the next in an OS Shell.It claims an invalid drive specification when it tries to perform the OS command (Xcopy). It isn't, as I can run it in a DOS shell quite happily, and its using absolute paths in the command.The Service account does have access to the other server, so this command should work...Am I missing something blindingly obvious? |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-08-06 : 16:48:39
|
| What do you mean by absolute paths ?You should use UNC paths - and also have you tried using a plain copy or robocopy from the NT resource kit? I seem to recall there can be issues with xcopy - something to do with it expecting input handles which the SQL Agent environment does not provide There is a very short KB article [url]http://support.microsoft.com/default.aspx?scid=KB;EN-US;q152134&[/url] that refers to SQL 6.5 however I don't know if this was fixed - I thought it was. Here's something to try to provide the input it needsexec master..xp_cmdshell 'echo F | cmd /c xcopy source destn /S'assuming you are copying a fileHTHJasper Smith |
|
|
|
RobMarsh
Starting Member
28 Posts |
Posted - 2002-08-07 : 08:59:36
|
| Sorry, by absolute I meant UNC paths.I know the scripts are sound in themelves, as they are working fine on 2 other sets of servers. This is what is so annoying.If the job runs under the context of the SQL Service account, then its trying to use the same account (called 'SQLssa') as I was setting the job to run under manually anyhow. This is a dedicated SQL Server account used for all the SQL Servers services that we use on our network, as such it has both local & Domain administrator rights.Interestingly enough, if I set the xcopy to be copy, the error becomes'access is denied'.One thing I have just noticed is that when using the SQLssa account to run the job, the original error ('Unable to determine...' etc) actually occurs before the first step of the job is run.I think I may have to go the Active Directory route... |
|
|
|
|
|
|
|
|
Server access problems with a jobs owner...