August 21, 2008 at 3:18 pm
I just came across this post which might put some light in the darkness...
http://www.mssqltips.com/tip.asp?tip=1567
http://www.mssqltips.com/tip.asp?tip=1557
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 22, 2008 at 9:10 am
Does the problem go away if you use SA or SQL Server Account as the owner? If so, the issue is not due to case sensitivity, it is because of the owner not having proper permissions to execute xp_logininfo or the other msdb stored proc.
August 22, 2008 at 9:23 am
It does go away with SQL or SA account. But the AD user is a SYSadmin account also so permissions is not the issue.
August 24, 2008 at 7:17 pm
xp_logininfo needs permissins at the server level (if NT group) or the domain level (if domain group) to read the group. The SQL account probably has that. The other account needs the same privilages.
August 26, 2008 at 3:58 am
Cath has a good idea... Get the Windows admin folk to rename your login so it uses only upper case characters.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
August 26, 2008 at 8:16 am
can you create a proxy account for the job?
August 29, 2008 at 7:10 pm
Harold,
My jobs with an Active Directory account owner also failed with the identical error message, but they worked with a local domain account as owner. I finally got the AD owned jobs to work after following all the steps in this document:
http://technet.microsoft.com/en-us/library/bb735885.aspx
I set up a Service Principal Name and then used configuration manager to change the service account from a local domain account to an Active Directory account.
August 30, 2008 at 7:13 am
My issue was due to the AD name in active directory and the case SQL was authenticating. Again this is a non default set up and the server is case sensitive.
After changing the AD name to lower case ( the pre-2000 name) everything has been working so far. The xp_logininfo seems to be using a function that lowers the case or maybe the AD name was stored in lower case in a Kerberos ticket or something. But whenever it tried to authenticate the job owner it failed.
December 3, 2008 at 11:30 am
Hello,
Did anyone ever find a solution?
Thanks
December 3, 2008 at 11:59 am
Only solution I found was to make the owner a SQL Account instead of an AD account. This works for this one instance but I know this will not work for some instances that are dependant on the Job owner.
April 7, 2009 at 3:17 pm
can I join in...
We have a job whos owner is domain\user.
Everything worked fine till today. Our security team decided that they would remove the ability to logon interactively from the user and the job is failing.
I don't understand why.
The user has a login in SQL. SQL Agent is starting under a different service account. So when the job runs why does it fail?
Before the job runs does it try to log in as the job owner?
Any input would help.
Thanks
April 7, 2009 at 4:11 pm
Just some more info
Looking at the job history for the job that failed I see the following message:
'EXECUTE AS LOGIN' failed for the requested login . The step failed.
This error starting coming showing up after 'login interactively' was taken away from ).
If I look at the job history for the runs prior to the change I see the message
Executed as user: . The step succeeded.
I just changed the job owner back to and I see the follow message in the job history.
Executed as user: PROD1\sqla. The step succeeded.
I still don't understand whats going on but I wanted to share
April 8, 2009 at 6:23 am
I had something similar happen with a linked server on a 64-bit SQL 2005 connecting to a 32-bit SQL 2000 (error 10054: communication link failure). The NT account in SQL server was all lower-case but in AD it was UPPER\Mixed-case so the linked server didn't work. I couldn't drop and re-add the NT account because it was used for the service account and SQL is a cluster. I ended up creating a Credential and then a Proxy with the same case like AD and it fixed the linked server problem.
I'm not suggesting that you do the same, but my guess is that something may have changed in AD. Try dropping an NT account and then re-adding it by browsing AD instead of typing it in. Maybe a security identifier was changed so there might be a problem with authentication. Not sure though. 🙂
April 8, 2009 at 6:37 am
I have found linda's suggestion to work but it worked only short term for me. It would last untill the cached account creditials or whatever is causing this, expired again. I ended up having to go with SQL accounts to make it work the best.
April 8, 2009 at 6:58 am
SQL may be caching the user account info. Can you drop an account, stop / restart SQL and then add it back again? If that doesn't work then maybe Windows is doing the caching but you'd need a server restart to find out.
Let us know if you find a fix. 🙂
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply