SSPI Context error on long-running scripts
-
I have a SQL script that takes about 3 hours to run,(it's doing some batch processing). At the beginning of the script, a query is run that accesses a linked server to get a list of record keys to process, but after this query is run, only the local server is accessed in a "WHILE" loop. The linked query takes about 20 seconds to execute. Also the script does a PRINT statement every 1000 rows to show the progress.
After anywhere from 15 min. to 1 1/2 hours, I get the following message:
Server: Msg 7399, Level 16, State 1, Line 11
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Cannot generate SSPI context]This only seems to affect this long running query.
BTW...If I log in as "sa", everything works okay.
Here is the script's code:
declare
@key1 integer,
@project_id integer,
@x integer
SET NOCOUNT ON
-- SET VAR HERE
select @project_id = 10329
select distinct key1 = key1
into #work
from [LinkedServer1].db1.dbo.match
where project_id = @project_id
and ISNULL(key1,0) != 0
and match_result in ('M','E')
create unique clustered index i1 on #work(key1)
select @x = COUNT(*) from #work
PRINT 'TOTAL TO PROCESS:' + STR(@x)
PRINT 'ACT TYPE Key1'
while exists (select top 1 * from #work)
begin
select top 1 @key1 = key1 from #work where key1 = (select min(key1) from #work)
exec pro_RMT_Calc_Flags @key1,1 -- CALCULATION
set rowcount 1
delete #work where key1 = @key1
set rowcount 0
SET @x=@x-1
IF @x % 1000 = 0 PRINT STR(@x)+':RECORDS LEFT'
end
drop table #work
PRINT 'DONE.'Any ideas?
-Dan
-Dan -
I would try wrapping the linked server bit in a transaction and see how that goes.
Failing that, you may want to seek the help of someone who actually knows what they're talking about
. See the KB article: http://support.microsoft.com/default.aspx?scid=KB;EN-US;811889 (HOW TO: Troubleshoot the "Cannot Generate SSPI Context" Error Message)Cheers,
- Mark
Cheers,
- Mark -
I've had a smiliar experience after configuring a linked server to use Windows authentication (see "Security Account Delegation" in BOL). If I log into the local SQL Server and execute a link server query from Query Analyzer such as SELECT * FROM linkedServer.mydb.dbo.mytable, then wait 15 minutes and run the query again in the same Query Analyzer session, I will receive an SSPI error. I suspect this has something to do with Kerberos tickets in Microsoft's Active Directory. The tickets have an expiration time. (To view a list of cached tickets see the Windows 2000 Resource Kit utility, klist.exe). If your linked server security is setup to use security account delegation, I would suggest you map your local server Windows login to a remote server SQL login. This way you can use Windows authentication to connect to the local server and the linked server will use SQL authentication.
-
SSPI is still a minor mystery to me. Had it happen yesterday after a password change on the service account. Strange. I like the idea of the sql login, usually a good idea for batch processes anyway so that it's not dependent on running as a certain user (who might leave or have permissions changed).
Andy
. See the KB article: Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply