February 21, 2009 at 9:52 am
[h5]How to fix SQL Server Agent "Cannot Generate SSPI Context" errors when offline?[/h5]
I have been having a problem for a while that I really need to get fixed. I have SQL Sever 2005 (and 2008) Developer Edition installed on my laptop. Everything seems to work fine when I am at the office. However, when I am working at home (which is around 80 hours a week) all of my normal logons/connection will start to fail with "Cannot generate SSPI context".
Now I am pretty that I know what this is, because all of the "Debugging SSPI Errors" guides say "make sure that you can communicate with the domain." Well, NO, I cannot communicate with the domain, because I am AT HOME. Sure, sometimes I can VPN in, but that is spotty at best, is not under my control, and is almost always down on the weekends. So what are my alternatives here?
What I have done for most things is to use SQL Server Logins when I am at home. That's inconvenient, but it works for most things. Unfortunately, it does NOT work for the SQL Agent, because it will not allow SQL Server Logins for 2005 & higher servers (thanks, Microsoft). This leaves me high & dry because all of my backup and maintenance jobs are supposed to run at night, from home (where my backup disks are), but the Agent can't connect to the server.
So what can I do? I am pretty desperate here?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 22, 2009 at 2:36 pm
Is there an issue with you creating a local account on your laptop and using that for your SQL Agent processes?
K. Brian Kelley
@kbriankelley
February 22, 2009 at 4:57 pm
It's set to use the "Local System Account" now (from the Services manager), is that not sufficient?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 22, 2009 at 5:32 pm
That should work. But if you're using a local account, what is generating the SSPI errors? SQL Agent trying to start up and connect or the jobs themselves when they try to execute? If the latter, set the jobs to be owned by sa. That should cause them to run as System on your laptop.
K. Brian Kelley
@kbriankelley
February 22, 2009 at 7:31 pm
Got me. I tried changing the Job owners to "sa", didn't help.
Here's the SQLAgent log from a few minutes ago:
Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
02/22/2009 21:02:07,,Error,,,,,,[382] Logon to server 'PPSLT-BYOUNG' failed (ConnAttemptCachableOp),,,,,,,
02/22/2009 21:02:07,,Error,,,,,,[298] SQLServer Error: 772 Cannot generate SSPI context [SQLSTATE HY000],,,,,,,
02/22/2009 21:02:07,,Error,,,,,,[298] SQLServer Error: 772 SQL Network Interfaces: The Local Security Authority cannot be contacted [SQLSTATE HY000],,,,,,,
02/22/2009 21:02:07,,Error,,,,,,[382] Logon to server 'PPSLT-BYOUNG' failed (ConnAttemptCachableOp),,,,,,,
02/22/2009 21:02:07,,Error,,,,,,[298] SQLServer Error: 772 Cannot generate SSPI context [SQLSTATE HY000],,,,,,,
02/22/2009 21:02:07,,Error,,,,,,[298] SQLServer Error: 772 SQL Network Interfaces: The Local Security Authority cannot be contacted [SQLSTATE HY000],,,,,,,
02/22/2009 21:02:06,,Warning,,,,,,[163] 1 operations applied (0 remaining),,,,,,,
02/22/2009 21:02:06,,Warning,,,,,,[162] Internal request (from SetJobNextRunDate [reason: schedule will not run again]) to deactivate schedule 4,,,,,,,
02/22/2009 21:02:06,,Error,,,,,,[382] Logon to server 'PPSLT-BYOUNG' failed (ConnAttemptCachableOp),,,,,,,
02/22/2009 21:02:06,,Error,,,,,,[298] SQLServer Error: 772 Cannot generate SSPI context [SQLSTATE HY000],,,,,,,
02/22/2009 21:02:06,,Error,,,,,,[298] SQLServer Error: 772 SQL Network Interfaces: The Local Security Authority cannot be contacted [SQLSTATE HY000],,,,,,,
02/22/2009 21:02:06,,Error,,,,,,[382] Logon to server 'PPSLT-BYOUNG' failed (ConnAttemptCachableOp),,,,,,,
02/22/2009 21:02:06,,Error,,,,,,[298] SQLServer Error: 772 Cannot generate SSPI context [SQLSTATE HY000],,,,,,,
02/22/2009 21:02:06,,Error,,,,,,[298] SQLServer Error: 772 SQL Network Interfaces: The Local Security Authority cannot be contacted [SQLSTATE HY000],,,,,,,
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 23, 2009 at 12:01 pm
OK, I just confirmed that when I am at work, the SQL Agent works just fine. Unfortunately, I still need a way to get it to work from home... 🙁
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 26, 2009 at 2:26 pm
Is Shared Memory enabled as a protocol for the SQL Server instance? Is it enabled as a Client Protocol and listed first in the protocol order?
K. Brian Kelley
@kbriankelley
February 26, 2009 at 2:38 pm
Yes too all three. However, I also have an Alias that forces the TCP protocol.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 26, 2009 at 2:49 pm
If the Alias is the same as the server/server+instance name, try removing that alias and see if it resolves.
K. Brian Kelley
@kbriankelley
February 26, 2009 at 2:51 pm
OK, I'll have to try it tonight since I am at work and functional now.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 2, 2009 at 8:46 am
Thanks Brian, I think that worked! 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 2, 2009 at 9:19 am
There's a couple of blog posts on it out there and it's bitten me a couple of times. If you've got to be disconnected, shared memory will only use the LSA, and that's the way to go.
K. Brian Kelley
@kbriankelley
March 10, 2009 at 8:04 am
I had a similar issue and the cause of the issue was related to:
SQL Network Interfaces: The Local Security Authority cannot be contacted
When the laptop was off domain, the inability to contact the LSA prevented SQLAgent from starting.
To work around that, I changed Agent to start as a local admin account and made that account SA on the local instance.
March 10, 2009 at 6:39 pm
Danny: If you check my posts in this thread, you will see that I had already done all of those things and it still was not working. Turned out that I was forcing it to use TCP locally, when I allowed it to use shared memory, it started working again.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply