Random Thunks

I had a situation occur recently where I needed to restore a DB to a new server but I didn’t have the ability the bring the Service Master Key over with it. Nor did I have the original password used to encrypt the Database Master Key (it was before my time).

What I did have was access to the box with the database still running and the associated key hierarchy still operational (symmetric keys and the like).

I’ve not done too much messing around with the database master key before, feeling it was best left well alone, however the need to migrate this DB was there and I needed to pull it across without loosing access to the encrypted data.

To prototype this activity out I created a database on my local DB server and utilized the services of another Sql Server 2008 Server elsewhere in the organization.

So, first off I needed to create the key hierarchy and a place to store my test encrypted data:

Create Master Key Encryption By Password = 'abc123!!!ORIGINAL';
Create Asymmetric Key AKey With Algorithm = RSA_2048;
Create Symmetric Key SKey With Algorithm = TRIPLE_DES_3KEY
    Encryption By Asymmetric Key AKey
Create Table dbo.ASaucerfulOfSecrets
    (
        TheSecret        VarBinary(MAX)
    )
Open Symmetric Key SKey Decryption By Asymmetric Key AKey;
Insert Into dbo.ASaucerfulOfSecrets Values
    ( EncryptByKey(Key_Guid('SKey'), N'Ssssh! This is a BIG secret!'
                                   , 1, 0x12345));
Close Symmetric Key SKey;

I confirmed that encryption was working as expected by simply pulling the data back:

Select *
From dbo.ASaucerfulOfSecrets
Open Symmetric Key SKey Decryption By Asymmetric Key AKey;
Select Cast(DecryptByKey(TheSecret, 1, 0x12345) As NVarChar(MAX))
  From dbo.ASaucerfulOfSecrets;
Close Symmetric Key SKey;

Which gave me the following results

TheSecret

0x00496C61F87C4941BF7CBE8C879146BA0100000043C4B3F8824C73EBF3C0…

(No column name)

Ssssh! This is a BIG secret!

Life so far was operating within the expected parameters. Now I backed up the DB and restored it to a different server.

Running the select again I got the expected exceptions raised due to the inability to open the Database Master Key:

(1 row(s) affected)

Msg 15581, Level 16, State 3, Line 3

Please create a master key in the database or open the master key in the session before performing this operation.

(1 row(s) affected)

Msg 15315, Level 16, State 1, Line 5

The key ‘SKey’ is not open. Please open the key before using it.

So, now came the exercise of correcting the issue. Given that we had supposedly lost the password to the original DB, it’s safe to assume that one would want to recreate it (and this time, write the bloody thing down somewhere!). So that’s the approach I took. Back on the source server I ran the following:

Alter Master Key Regenerate
    With Encryption By Password = 'abc123!!!New'

The Books OnLine warns that this can be an intensive process and I suppose it comes down to just how many keys you have defined in your hierarchy. For the purposes of this test I had but one key so I wasn’t worried. Now I had a Master Key password that I knew of so I performed another backup, moved the DB to the secondary server and restored it. Just for giggles I reran the select again and received (rather unsurprisingly) the same results.

Next I tried the select again but only after explicitly opening the Database Master Key (with the ‘new’ password):

Open Master Key Decryption By Password = 'abc123!!!New'
Select *
From dbo.ASaucerfulOfSecrets
Open Symmetric Key SKey Decryption By Asymmetric Key AKey;
Select Cast(DecryptByKey(TheSecret, 1, 0x12345) As NVarChar(MAX))
  From dbo.ASaucerfulOfSecrets;
Close Symmetric Key SKey;

This did indeed pull back the decrypted data. Now for the final action – adding the Service Master Key to the Database Master Key:

Open Master Key Decryption By Password = 'abc123!!!New';
Alter Master Key Add Encryption By Service Master Key;

This enabled the encryption to use either an explicitly opened master key, or use the service master key – which is exactly what I was after.

I was able to confirm the results by performing the select again (without opening the master key first) and this gave me my encrypted data back. Tada!

This one had me stumped for days. Fortunately MS support came to my rescue with a simple answer.

The problem I was having was getting Sql Server to load an SSL certificate to encrypt communications between client and server on a clustered instance. No matter how many times I tried, Sql refused to run issuing a 0x8009030d error code when attempting to load the certificate. I was always getting the following logged in my ErrorLog:

Server The server could not load the certificate it needs to initiate an SSL connection. It returned the following error: 0x8009030d. Check certificates to make sure they are valid.

Server Error: 26014, Severity: 16, State: 1.

Server Unable to load user-specified certificate. The server will not accept a connection. You should verify that the certificate is correctly installed. See "Configuring Certificate for Use by SSL" in Books Online.

Server Error: 17182, Severity: 16, State: 1.

Server TDSSNIClient initialization failed with error 0×80092004, status code 0×80.

Server Error: 17182, Severity: 16, State: 1. 2011-10-26 07:26:42.75 Server TDSSNIClient initialization failed with error 0×80092004, status code 0×1.

Server Error: 17826, Severity: 18, State: 3.

Server Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.

Server Error: 17120, Severity: 16, State: 1.

Server SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

I’d come across a few posts that mentioned this error and that Sql was having issues with the Private Key but they suggested using commands from the Resource Kit to fix the issue – something I could not do. To make matters worse (as my previous post described) Checkpointing kept on getting in my way of removing the certificate, just to get Sql to start. I’d even tried things like adding the Service Account in the Administrators group in the vain hope that it was a permissions issue.

So after spending several days of tearing my hair out (and running out of time to get this fixed) I came to the conclusion I needed to bring in outside help – MS Support to the rescue!

And the answer? It was as simple as deleting the previously imported certificate then re-importing it using the domain account that runs Sql. Yup, it was that simple…

Thanks Gary!

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!

Tricky lil’ bugger this one.

We hit this error when trying to run a Estimated Query Plan on a users’ query. Didn’t make any sense since we’re SysAdmin’s on the server;  doubly-so since we could run a showplan elsewhere.

Turns out the offender in question was a call to msdb.dbo.sp_send_dbmail. Apparently bad funky things happen there if one tries a showplan on it. To solve it we simply commented out the call to the sendmail procedure out and then, the sky’s did doth part and our estimated query plan presented itself unto us..

Good to know…

I like online shopping – I do it a lot. I’ll admit that Amazon.Com has me hooked and I’d spend even more money there if the wife wouldn’t cut my fingers off at the core as a result. Target.Com is a new one for me to visit though. Done the retail store (a lot) and like it a lot (beats Wally World any day of the week), but their online inventory leaves something to be desired.

They try hard hard – real hard. When an item’s in stock they not only tell you that but also which Aisle it’s in (or endcap thereof). Real nice and useful however a total FAIL when it comes to reliability.

For me if your data’s not 100% correct it’s not worth pushing. And in Target’s case they seem to have an issue with the number of items on hand bit. Depending on whom we spoke to (wither store or via the phone) that figure could be 24 hours or even more out of date ; The web site continued to proudly inform us that our local Target had a particular item in stock even when it didn’t. Even checking over 24 hours later it still told us it was available even though it wasn’t.

This effect reminds me of a crowd I did some Sql Work for late in the last c. whilst I was still in the UK. Their name shall remain known only to me and them, however the story’s true. This crowd wanted an online system to punt their wares to Joe Consumer – all wizzywzggy flash driven utilizing Sql Server on the front and back end with replication to handle to the orders. Real nice right up to the point when there order’s then ended up on a printer somewhere awaiting an operator to actually input the order to the mainframe ‘at some later point’. This of course meant the at any point in time the inventory could could vary from accurate to ‘just a stab in the dark’.

I was reminded of this antiquated hand-cranked this system with my experience at Target; if you’re going to have an inventory indicator then it’d had better be accurate to, I don’t know, the last 60 minutes at least or else it devalues the web site forcing the consumer to have to resort to the phone to double check the inventory prior to (in our case) traipsing out in the bloody snow and cold).

This post is a little on the late side, however better late than never…

We’ve been using Idera’s Sql Compliance Manager at work for some time to provide a good healthy audit trail of all user database changes, good or bad, on the production DB’s. As our use grew so did it’s unsuitability to the server it was located on (needed a TB more data than the few hundred GB currently on offer would allow!). So that led me to make the biggest move yet when I had to copy nearly a year’s worth of audit data (took about 24 hours across a 1GB pipe!), followed by a total reinstalled and repointing from the client DB Servers.

I’m extremely proud to report that throughout this downtime (nearly 2 days in total), Idera didn’t miss a beat and ALL activity that took place during the move was picked up as soon as the new server came online and within a day you’d have never known there had even been the potential of a blip.

Nice work Idera – Mark me down as extremely bloody impressed.

If you’ve just installed or upgraded to Quest Spotlight 7.0 and and getting this odd error for this counter then get Support to send you version 7.5.0.4648 of QS_IndexLatchWaitTimes.sql.

The reason for the exception is because it’s not taking Schema’s into account when inserting unique values into a temporary table, thus the 7.0 RTM version currently errors out if you have the same constraint or index name for the same table for tables under different schemas.

The same script affects both 2005 and 2008 so the patch will need to be installed in two locations – Quest will (should) provide all the details…

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:

I’ve been fiddling around with some jobs recently that required values to be kept from one job step to another. Not finding anything much exciting I went for plan Z – roll my own.

They’re simple procedures but do work nicely.

First I create a Schema that everything will be bundled under:

Now we’ll need a backing store:

Next I create the SessionDestroyer Procedure (since it can be called from the next one this keeps the dependencies happy and calm):

Along comes the SessionSaverStore next:

Finally the SessionRetriever:

Use is pretty straightforward. As an Proof of concept I created a Sql Agent Job with two steps – Store’ and ‘Fetch’

Store contained the following:

And Fetch:

Granted it’s missing some bells and whistles (like exception handling) and I’d wager I could stuff the Retriever in as a function. However as a first hit I was pretty chuffed with it!

Whilst it’s not immediately obvious, one can perform a Bulk Copy operation as part of a Sql Server CLR Stored Procedure. There are however some gotcha’s.

First off is that you need to stay away from Context Connections for the actual operation. Whilst you can technically use a Context Connection for the first part, I’d personally not recommend you do for reasons that will become apparent shortly.

The code below is taken from one of my procedures using a homebrew class that provides me with information regarding a table – including the various elements of a fully qualified 4 part name (Server.Database.Schema.Table).

The first part of the code handles the bulk import:

As you can see I’m using two SSPI connections in place of the Context based Connection that’s normally provided. I decided to use SSPI to avoid needing to store user names and passwords. Your mileage and methodology may vary.

The next section is the reason why I advise not using a Context Connection for the DataReader:

In order for this event to work correctly the Context Connection must be free – which will not be the case if you use it for the DataReader. Running the RaisError via ExecuteAndSend ensures the results are not gobbled up by the any special pipe that SqlCommand.ExecuteNoQuery creates (see Vadim Tryshev’s excellent post on RAISERROR in CLR Routines). Finally I use a 0 for both the State and Severity to avoid any subsequent messages that RaisError would otherwise produce.

Sql Server 2008, SP1