Tag Archives: troubleshooting

Cannot Generate SSPI Context

I woke up pretty early this morning and decided to get something in my dev environment worked out that I’ve been meaning to do for a long time now. I needed to put my main Minion dev box on a domain acct for SQL. It was currently running under local system. So I switched it to use the domain acct SQLSvc. The second I restarted SQL with the new acct I got the dreaded “Cannot Generate SSPI Context”.

Here’s my fix and I’m hoping it’ll help someone in the future.

First let me say that SSPI errors can be caused by a number of things. This one turned out to be an SPN error, but it doesn’t have to be. Among other things it could be:

  1. DNS lookup.
  2. SQL Server time out of sync .
  3. Client time out of sync.
  4. Anything else that keeps Kerberos from working.

 

Now there are also things that can keep the SPN from being created or used. The account could not have the perms it needs to create the SPN in AD, or there could be duplicate SPNs. You can only have 1. Or you could simply have the wrong SPN. Make no mistake, this issue had caused many gray hairs in many DBAs, and I personally always sigh when it comes up because it can be fixed in 5mins or it can take 4 days and 3 MS support techs getting involved. Most of the time though, it’s more straight forward than that. I’m not going to even attempt to make this a treatise on troubleshooting every aspect of this error though. I’m just going to show you how to diagnose the SPN portion of it and quite often that’s the issue so if it doesn’t work for you it’ll at least eliminate the SPN and you can concentrate your efforts on something else.

OK, that’s enough prelim stuff, let’s get to it.

First, we need to see what SPNs we have on the box. There are 2 ways you can do that depending on what rights you have in the domain. I’m going to show you both of them so you can have a visual guide as well as a cmdline guide.

To see what SPNs you have on the box, go to the cmdline and type the following:

SETSPN –L MinionDevCon

What I’m saying here is to list (-L) the SPNs registered for the SQL box I’m interested in. In this case, MinionDevCon.

See below for the query and the results.

SPN1

Also, notice that the SETSPN command isn’t case sensitive.

So above you can see that I’ve got 2 SPNs for my SQL acct on that box.  Now we just have to delete one of them.  However, before we fix this issue, I’ll show you another way to view the same info.

If you have access to AD, then you can also see the SPNs in the computer object properties.  So go to AD and navigate to the computer object for your SQL box.  Again, in my case this is MinionDevCon.  Then go into the properties of that computer object.  You’ll want to go to the Attribute Editor tab.

SPN2

Now find servicePrincipalName and either double-click it or highlight it and hit Edit.  This will show you the list of SPNs for that computer object.

Here you can see my 2 SPNs just like at the cmdline.

SPN3

Deleting the extra SPN:

Deleting the bad SPN is a fairly straight forward operation.  And again I’ll show you 2 ways.

At the cmdline, simply use the SETSPN cmd again and pass in the delete flag.

SETSPN -D MSSQLSvc/MinionDevCon.MIdnight.DBA:1433 MinionDevCon

So again, that’s SETSPN <SPN> <Computername>
The SPN in the cmd above should be exactly the way it was represented in the list when it was queried.  Here’s the command in the query window so you can see what it looks like:

SPN4

Ahhh, see there, my regular acct doesn’t have rights to do this.  I wanted you to see what happens when you don’t have rights.  You will quite often need to get your AD guys to do this for you.  So if you just prepare the cmd and send it to them they’ll be able to make it happen.  Here’s what it looks like when I log in under my domain admin acct.

SPN5

And again, that cmd is simple.  Call SETSPN with the -D flag, which tells it to delete.  Then the exact name of the SPN followed by the name of the server.

Now let’s do the same thing in AD.  So you’re still in your computer object properties like above.  Simply highlight the SPN you want to drop and then click the Remove button.  It’ll put it in the text box above, but just click OK and it’ll be gone.

SPN6

Ok, that’s it.  I didn’t have to restart my SQL service, but I’ve heard of people who have.

Now, this was just a quick tutorial on how to manage SPNs.  This hole can go pretty deep.  Here’s a decent link on MSDN for troubleshooting SPNs.  I don’t think I like their troubleshooting because they don’t really do a good job of showing you the commands, but it’s a good explanation of the problem, what an SPN is, etc.  If I remember correctly it’ll also help you choose the right SPN.

Replication Error: The ALTER TABLE statement conflicted with the FOREIGN KEY constraint.

I was initializing a transactional replication with a snapshot today and I got the following error:

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint “FK_Table_xxxx”. The conflict occurred in database “DestDB”, table “dbo.MyTable”, column ‘MyColId’. (Source: MSSQLServer, Error number: 547)

This stopped the snapshot from being applied.
I searched for that FK in the DB and it didn’t exist… not on that table, not anywhere.

I’m going to keep this short. The answer is that SQL keeps a copy of FKs in dbo.MSsavedForeignKeys.
So if you get this error, you can go to the subr DB and delete the row from that table and you should be fine.
I had the problem where it kept finding new FKs to error on.
I finally just deleted all the rows out of that table and everything was fine after that.
The actual delete stmt I ran was:
delete dbo.MSsavedForeignKeys where constraint_name = N’FKName’

I hope this saves you a lot of looking around.

Another connection is already running ‘sp_replcmds’ for Change Data Capture in the current database

I recently got this error message in a CDC scenario. There are 2 places you can discover this. You can look at the job history for your CDC capture job, and you can look in sys.dm_cdc_errors.

Since CDC uses the replication log reader repl rules apply here in that you can only have one process running sp_replcmds at once. And in the case of CDC and repl, that’ll be the log reader.

That said, how did this happen to begin with? Well in my case I think what happened was another DBA noticed that the log had gotten up to 6GB and never came down. Log backups were running every hour so over the course of a couple weeks it should have cleared out all the xact, but for some reason it didn’t. For some reason there were still 6GB in the log that remained active. So what I think happened is the other DBA stopped the CDC capture job and ran sp_repldone to clear things out. Now this is where the mistake came about. He didn’t run sp_replflush afterwards and he didn’t disconnect his session. I suspected this may be the case so all I did was disconnect his session and restart the CDC capture job and it started up again right away.

Now, to my knowledge there wasn’t anything I could have done to know which connection it was because he had run a few other things since then and his inputbuffer was misleading. The only thing that let me know which spid to kill was the fact I knew who probably did something like that.

Anyway, I don’t know how to tell you to find the offending user in a case like this where it’s just left over from a previous query in the same connection. But at least you know what you need to do now so you can make your own guesses as to who it is. If anyone has a better way to find this info I’d love to hear it.

Log Management Made Easy


Fatal error: Uncaught Error: Call to undefined function eregi() in /home5/midnigk3/public_html/DBARant/wp-content/plugins/wp-codebox/main.php:136 Stack trace: #0 /home5/midnigk3/public_html/DBARant/wp-content/plugins/wp-codebox/main.php(75): wp_codebox_is_windowsie() #1 /home5/midnigk3/public_html/DBARant/wp-content/plugins/wp-codebox/main.php(50): wp_codebox_highlight_geshi(Array) #2 [internal function]: wp_codebox_highlight(Array) #3 /home5/midnigk3/public_html/DBARant/wp-content/plugins/wp-codebox/main.php(130): preg_replace_callback('/<p>\\s*eba61421...', 'wp_codebox_high...', '\n\t\t\t\t<div class...') #4 /home5/midnigk3/public_html/DBARant/wp-includes/plugin.php(235): wp_codebox_after_filter('\n\t\t\t\t<div class...') #5 /home5/midnigk3/public_html/DBARant/wp-includes/post-template.php(240): apply_filters('the_content', '\n\t\t\t\t<div class...') #6 /home5/midnigk3/public_html/DBARant/wp-content/themes/twentyfourteen/content.php(57): the_content('Continue readin...') #7 /home5/midnigk3/public_html/DBARant/wp-include in /home5/midnigk3/public_html/DBARant/wp-content/plugins/wp-codebox/main.php on line 136