CLR and Performance...
-
We have a programmer who has coded himself into a corner...and the only solution appears to be a CLR routine to be able to correctly hash the password so that both ASP and ASP.Net version of the code generate the same hash so when the password is decrypted to be sent to the end user, it correctly dehashes.
Anyhow, he has a CLR routine that he wants to register as function. We have done this in our development and testing environments without problem and it works correctly. This CLR function would be called approximately 100,000 time per project and we would have about 20 projects running on an instance at any given time. So thing thing will be invoked a lot and it is my preference to get it the heck out of my DB.
Now my question1:
1. How can we see the performance of invoking this CLR and what performance counters have you found to be the most helpful in determining if this should be allowed or disallowed in the final release?
2. What experiences good/bad have you had with using the CLR this frequently in your SQL 2005 DBs?
Thanks
SJ
-
Also what about security implications...since this invokes most of the .Net framework, are those bugs and security holes also introduced to SQL?
SJ
-
We have a programmer who has coded himself into a corner...and the only solution appears to be a CLR routine to be able to correctly hash the password so that both ASP and ASP.Net version of the code generate the same hash so when the password is decrypted to be sent to the end user, it correctly dehashes.
Are you sure this is the way you want to go? Normally, you shouldn't be able to dehash a password and you don't want to send a decrypted password at all if you can avoid it. Traditionally, you would store the hashed value in the database and require the front end to pass the hashed value. If you can't require that of the front end you can pass a plaintext password from the front end, hash it and compare it to the stored value.
Additionally, while you didn't specify a timeframe for those 100,000 calls (x20 so 2,000,000 calls), that seems like a pretty hefty number of logins that are happening. Would it be better to store connection/permission information at the server level using some sort of permission structure and use a guid/crc32 pair at the client to identify the permission set for the connection.
As for your actual questions, I don' know.
JimFive
-
You might want to look at this page on CLR and memory usage.
http://blogs.msdn.com/sqlclr/archive/2006/03/24/560154.aspx
-
The programmer needs to find a different hammer.
-
The programmer might need a different (bigger) hammer however the fault lies in management or to be more specific project management and architects. If the folks running things were on the ball they would not let this type of a design fiasco get by the discussion stage.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
I'm confused why you can't just use the object that does the encryption in both ASP and .NET; I know in .NET you can invoke vb6 objects, and vb6 can invoke .net, all that has to happen is the .NET assembly is registerd with REGASM.exe;
in ASP:
dim myobj 'as object
set myobj=Server.CreateObject("myNamespace.Objectname.Encryptomatic")
myobj.Decrypt("somestring",somehashkey")
myobj.Encrypt("somestring",somehashkey")
if you have a vb6 object(or any other dll for that matter) you can do the same in .NET, slightly different syntax
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Accepting all invitesViewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply