Random Thunks

Not my find this but such a useful post I thought I’d repost it.

In my situation I had added a certificate’s thumbprint to the SuperSocketNetLib key on a new cluster which, for some reason Sql took a total and utter dislike to.

I thought (in my apparent naïveté) that I could simple remove the thumbprint from the registry, restart Sql and I’d be off and running.

Not so fast though. Every time I’d restart Sql Server it would repopulate the thumbprint back into the Certificate value then fail to start as a result. No matter how many times I rebooted, took the value out of the registry and twisted on my left foot whilst swinging a chicken in the air, Sql Server stubbornly and abjectly refused to stop repopulating the value with the bad certificates thumbprint.

User baby_cheeses on SqlServerCentral.com was able to find the magic formula for stopping Sql Server from magically repopulating the registry.

I amended baby_cheeses answer slightly to fit my situation, and it is…

  1. Remove the Certificate’s thumbprint from the registry.

  2. Start Sql Server using a Net Start MSSqlServer.

  3. Check the registry again and ensure thumbprint hasn’t returned.

  4. Start up Sql Server from within Cluster Administrator

  5. Check registry a third time and remove the thumbprint from the Certificate value.

  6. Move the cluster to another node and watch it start normally.

Like I say, I can’t take credit for this discovery.

Original post is here.

UPDATE

Apparently what is happening here is known as Checkpointing, where a copy of the registry is stored on the Quorum. MS gives an official method of avoiding the problem here, although I still think baby_cheeses solution is simpler and involves far less hacking around!

I’ve been writing a set of routines to control my database backups recently and one part of this was a CLR User Defined Data Type I’d created that allows some rudimentary file handling (with the appropriate security). By it’s very nature this assembly required the EXTERNAL ACCESS privilege and this is where the head-scratching came about.

Liking clean install scripts with minimal payloads (single scripts are easier to check into Source Code and deploy across multiple environments) I needed some method of encapsulating the deploy and ensuring the appropriate asymmetric key, users etc. were created. However normally this would require either the key file or the assembly DLL to be copied along with the .Sql script – and this would break my 1 script rule! So next idea was to create the asymmetric key from the assembly directly within Sql server – but I couldn’t do that until I’d installed the assembly which would fail because it required the EXTERNAL ACCESS privilege; Thus leading to another game of Chicken and Egg.

The solution was remarkably obvious – use a temporary SAFE assembly to create the asymmetric key then dispose of it directly afterwards – after all the assembly is only required at this juncture to create the asymmetric key.

The first section of the script deals with previous execution cleanups etc:

And the second part deploys the assembly with the requisite privilege:

This was an odd one and much akin to a previous recent incident I’d had with the configuration manager (albeit this time on a totally different server); this last weekend we had one of our regular ‘let’s all work through the night’ maintenance windows. Such events are never fun to do but made even worse when the strangest of things happen.

This weekend it was the turn of the Sql Configuration Manager and it’s stubborn refusal to validate a new account password. No matter how many times I tried it would not take the new password, regardless if I pasted it in or typed it in. Each time I’d see an entry in the Security log telling me the attempt to logon with the credentials had failed.

Given that it was now getting close to 3AM I decided another approach based off the prior experience; I fired up the Services applet and pasted the password directly in there.

Well, would you Adam and Eve it – it only bloody worked.

Don’t have a good reason or explanation for this one other than – WTF?

Still, whatever it takes.

Sql Server 2005 SP2 No CR’s.

I was doing a round of mass service account password changes today on one of our development servers and came across this bit of strangeness; despite making several attempts to update the account and password in the Configuration manager the SSIS service refused not only to start up, but also failed to give any indication as to why (the application logs were horribly silent on anything from either SSIS or even the service manager).

So I went into the back door and fired up the Services applet and refreshed the password in there. One message informing me the account had been granted the ‘Logon as a service’ privilege and the service was up.

What beats me is why the configuration manager refused on multiple times to accept the change. Not saying we should all ignore the config manager from now on but I myself will bear this in mind for future weirdness.