Certificate Keyset does not exist called from CLR

Question

Certificate Keyset does not exist called from CLR

paul.dixon

SSC Enthusiast

Points: 151
More actions
September 4, 2008 at 3:57 am

#193120

Hello,
I have a problem making a HTTPS web request via the CLR in SQL server 2005. The code was written in visual studio (vb.net) and works fine when I test it before creating it as a clr.
The problem is with the digital certificate I have to send. It has a private key (which I must send). When I execute the request in CLR I get a message saying, 'Keyset does not exist'.
signon to SQL server is via windows authentication. I am the dbo and I installed the certificate into the local machine store (in windows vista). I have admin authourity here.
I've given myself all authority to the cert and I EXECUTE AS my windows profile. I think my problem is to do with the SQL login not having the correct authority to use the private key. I've run out of ideas - does anyone have any suggestions?

Viewing 8 posts - 1 through 7 (of 7 total)

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

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

How are you reading the cert back into your CLR code? Can you post an example of your code that does this? You can try using Windows Identity Impersonation in CLR which is different than EXECUTE AS and provides OS level credentials to the execution context of the CLR object.

http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/34/Default.aspx

paul.dixon SSC Enthusiast Points: 151 More actions · Answer 2

Hello!

Thanks for the reply.

The code to extract the cert is:

dim cert as x509certificate

dim store as x509store = new x509store(storename.trustedPeople, storelocation.localMachine)

certs = store.certificates.find(x509findtype.findbysubjectname, subjectname, false)

cert = certs(0)

If I break the software at this point I see the keyset does not exist error. This code works fine outside of CLR.

Thanks for the pointer to the article - i will try add the permissions impersonation illustrated here and see how I get on.

I did 'Execute as' my windows profile with full permission on the cert but no joy. I also run the CLR with permission level set to EXTERNAL.

When the cert comes back to the program, its just not got the private key.

The other thing i'm wondering about is the fact that ther certificate states my windows account has inherited the certificate permissions. I'm not sure if this is a problem.

I originally loaded the cert into my personal store on the current machine. Then when I set up the CLR I copied it to the local machine store.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

The EXECUTE AS is SQL Based, not CLR Based Impersonation. You would still be operating as the SQL Service account exterior to SQL Server. Making the windows identity impersonation call in the CLR should fix your issue I would think.

paul.dixon SSC Enthusiast Points: 151 More actions · Answer 4

Thanks for your help Jonathan. I think your right. I didn't realise that about the Execute as.

I'll let you know how i get on.

Paul

paul.dixon SSC Enthusiast Points: 151 More actions · Answer 5

Hello Jonathon,

Impersonating my windows profile works. Thanks. I still have a problem. I think the private key is still being stripped out, but I think its happening during the HTTPS Post - do you think this is possible?

I check the cert and it is definately added to the HTTP header in the CLR. I do the post and then get an error message saying the 'Requested Key container was not found'. I checked it before the post and its their.

What do you think? Thanks

Paul

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 6

Paul,

did you happen to get this working, or are you still having trouble?

paul.dixon SSC Enthusiast Points: 151 More actions · Answer 7

Hello.

No I haven't sorted the issue. I suspect the problem is 'outside' of the structure of what i'm attempting to do. At this moment I am developing the application on my local machine.

This has the SQL server database loaded along side visual Studio. Both are running on Vista which I don't like. Although I adopt my windows profile that with admin rights (as you suggested - works perfectly by the way. Thanks) and i've explicitly given this profile authority to the certificate I'm just not happy with the setup.

In the next couple of weeks i'll shift the database to a proper server configuration (Windows Server 2003). I will run the clr under a profile that is configured explicitly for the prupose of making the https certificate requests. I half expect (and hope) the problem to disappear at this point.

Whilst there are other work arounds I could use I want to get this sorted because using the clr is so neat and tidy.

Thanks for your inquiry - i'll let you know how I get on.

Paul