March 18, 2011 at 4:32 pm
I been having problems with a job where a domain account is the job owner. Here are the specifics:
SQL Server 2005 w/SP 4
Domain account is valid and has sysadmin privileges.
Ownership of job transferred to local SQL account will cause job to run fine.
All other domain accounts on this instance have stopped working.
Same domain accounts on other SQL instances on other machines on the same network are unaffected.
I can open Enterprise Manager and interact with server using my domain account. I can perform all sysadmin functions.
SQL Server Agent verifies server access by running system stored procedure: msdb.dbo.sp_sqlagent_has_server_access. The return results are:
has_server_access 0; is_sysadmin 0, actual_login_name (unknown)
Checking xp_logininfo and passing domain user returns nothing, but running xp_logininfo shows complete list of local and domain users; e.g EXECUTE xp_logininfo '{domain\user}' vs EXECUTE xp_logininfo. Passing local account will return details about the local account.
I am completely stumped. I thought there might be a problem with ActiveDirectory but other SQL instances on the same network would have been affected, which they are not. Any thoughts?
March 21, 2011 at 1:05 pm
Did you try recreating domain accounts on the server and re-adding them inside sql server?
March 21, 2011 at 2:22 pm
I think I figured out the problem. We have a Windows group that we designate for read-only access to the SQL server. That Windows account is added to Logins. Then we define a database user (same name as the Logins account) and give that group read-only access to that database. I go back to Logins, then Deny permission for that group to connect to the database engine, but leave the Login enabled (I can't disable the Login). This has the effect of everyone with {abc_domain\Windows User Account} the inability to connect to the database and is not limited to the {abc_domain\Windows read-only group}. This is strange because I only want to deny access to members of {abc_domain\Windows read-only group}, not everyone within the {abc_domain}.
March 21, 2011 at 2:30 pm
For some reason, that instance of SQL Server (or that system) is having problems communicating with the domian controller.
When an agent job starts up and is owned by a domain user, the domain user is authenticated in the domain. If the domain user does not exist, or cannot be found - you will get this error.
One thing to note is that for these kinds of operations, the login MUST match the domain account exactly. It is case-sensitive, and LOGIN <> Login <> login and will also generate this error.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
March 21, 2011 at 3:19 pm
Case structure is what I thought too, but I tested the "msdb.dbo.sp_sqlagent_has_server_access system" stored procedure that the job uses to validate server access with all variations of upper/lower case names but still got the same response. Even so, the existing domain accounts previously defined as individual server logins and database users (associated with a Windows user account, not a Windows group account) should not have been affected since I only denied access to the Login account that was mapped to the 'read-only' Windows group account.
March 21, 2011 at 7:12 pm
This is not about the server having access, but the login matching the domain account. If the domain account is all uppercase, and the login is not - you will get this error.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
March 22, 2011 at 5:35 am
Jeffrey Williams-493691 (3/21/2011)
This is not about the server having access, but the login matching the domain account. If the domain account is all uppercase, and the login is not - you will get this error.
I've had this happen and been baffled. Thanks for the observation, Jeffrey.
Sounds like a SQL bug to me.
Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply