September 26, 2012 at 4:56 pm
We have an automated deployment system that allows us to button-press deploy code to dev, qa, staging or production. In addition, some developers use the same system to build a development environment on their own box. Update scripts are placed in a folder and run against the appropriate database to bring it up to a specific version.
I need to write a sql script that creates an asymmetric key from a .snk file. The .snk file was used to sign a CLR assembly that is also being loaded into the database. The SQL to create the asymmetric key looks something like this:CREATE ASYMMETRIC KEY KeyName FROM FILE = 'PathToFile\filename.snk' ENCRYPTION BY PASSWORD = 'thepassword'
My problem is that the path is different on each server. The Dev/QA/Staging/Prod environments are easy enough - I know what the path is going to be and I can use an IF statement and @@SERVERNAME to change the path for each one. But the individual developer environments can be set up in any number of ways and the update process probably won't even be running on the SQL server, so it wouldn't have access to the right "C:\" (or whatever drive) anyway. GA!
The CREATE ASSEMBLY syntax allows you to use a binary hex string to create the assembly so I don't have to have access to the original files for those, is there any way to do something similar for an asymmetric key? My only thought is to use a common wide-open share that could be accessed from anywhere by anyone... hardly seems the best security for a .snk though 🙁
Any ideas?
Chad
September 26, 2012 at 8:31 pm
An EKM system would solve this problem for you I think. If it supported Windows Auth all the better. You could secure the key so only your devs and relevant service accounts could create that key.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 27, 2012 at 12:33 pm
Thanks! I did some research on EKM today because I'm not too familiar with it. It sounds like we would need some 3rd party software or hardware to implement it? In your experience is there a steep learning curve or is it pretty straightforward?
Thanks,
Chad
September 27, 2012 at 12:40 pm
As far as I know adding an EKM system would still be a third-party solution. I looked a while back and Microsoft had no offering in the space. There are infinite variations, some hardware, some software, there are appliances, some in-built options that sit in a drive bay in a server and participate via the server bus, etc. From reading about it all SQL Server does is provide the plumbing and hooks in T-SQL to get you there and back. I have heard that some EKMs can even take over some of the encryption/decryption workload from the SQL Server box.
The EKM system I used was not from within SQL Server, it was from within a .NET context, but it's just an interface and the T-SQL seems pretty straightforward. From a 50K view it seemed to fit your situation.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply