sql 2008 DTS support problem

Question

sql 2008 DTS support problem

Mickle

Old Hand

Points: 382
More actions
July 2, 2012 at 9:26 am

#258283

Hi, I am in the process of upgrading some servers from SQl 2000 up to SQL 2008r2. At the same time I am moving hardware over to newer and better Dell servers. So far I have updgraded 3 of my 4 servers and they are in production. On each one, I've been able to add DTS support and the backward compatibility successfully with the backward compatibility packages.
For some reason on this last server, it throws an error "cannot generate SSPI context" when I attempt to save a DTS package from the old server over to the new server. I never saw this error on the other servers. I copied over a DTS package as a structured storage file and it imported successfully on the new server. However, when I try to open the package, it causes SSMS to crash with just a general windows error message. The OS on all of my servers is WIN server 2008. Has anyone else seen problems like this? Any help you can give would be greatly appreciated.
Thanks
Mike

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 1

SSPI is a security/kerberos error. Search for it and you should find a few things to check. Often it's something to do with the service account and creating an SPN in the domain when it starts.

Mickle Old Hand Points: 382 More actions · Answer 2

Thanks, I was able to find a Kerberos error in the windows logs. I've been researching the Kerberos error and possible casuses and solutions but nothing has worked yet. Everything runs under the same network account I use for other servers and its same one thats running the sql services. I'll post back when I get a solution.

Mickle Old Hand Points: 382 More actions · Answer 3

I did find out what my problem was and just wanted to share in case it helps anyone else. The group of sql servers I was working on does share the same network login for certain functions. However, during the installation of one of the servers we used that same network account for running the sql service. We should have set it up to run with the local service account as we did the others.

The SQL service came up fine but from what I can tell it could not generate the SPN correctly when trying to login since another sql serivce on an old system was running under the same account.

Changing the sql service to login under the local service account fixed it all.

Thanks for the help!

Mike