March 4, 2011 at 11:37 am
I have been killing myself over this for a couple weeks now and cannot find a viable solution. Here's my scenario:
I have a DTSX package that imports user data from an external database. It then creates user accounts and profiles in the local database. Users can also be created via a custom ASP.NET Membership Provider. The provider must be able to authenticate both types of users.
This was all fine and dandy during development because passwords were stored as clear text. However, now that we're ready for release the passwords format of the provider must be set to encrypted and so the users created via the DTSX must be created with an encrypted password. (I'd prefer the passwords were hashed but the client's requirements are that passwords be recoverable). The real problem seems to be creating an encrypted password within the DTSX that will be decryptable by the ASP.NET Membership Provider.
My original solution was to have the DTSX call a CLR stored procedure that had access to the same encryption logic as the provider. I got it working but our client wouldn't enable CLR integration. So that's out the window. Now I'm investigating alternatives. One alternative is to simply use the built-in encryption methods of T-SQL. But how do I share the keys used for the encryption/decryption?
My question is, is it possible to generate a password in T-SQL, say using EncryptByKey, that will also be decryptable by my provider? This means that the key in SQL must match the key in my machineKey configuration. I have no idea if this is possible.
Any insight/help is tremendously appreciated.
March 7, 2011 at 7:57 am
The short answer is no. You have two alternatives, write a .net assembly that implements encryption and use it from SQL server as a CLR function and externally via the assembly.
Or, most people just store the hash of the password instead of the actual password itself. I _believe_ you can create an external has function that is the equivalent of the SQL version ... if not you will have to use CLR.
The probability of survival is inversely proportional to the angle of arrival.
March 7, 2011 at 8:20 am
This is the answer I was anticipating. I posted this same question on about 5 different sites and either no one replied or no one knew. And I really tried everything possible, so it certainly seemed as though there was no way to share the implementations.
Some alternatives that I have thought of, but not really investigated yet:
- Use a common certificate. You'd have to override the provider's Encrypt and Decrypt methods and provide a custom implementation utilizing the certificate, but at least you could share keys this way (I think).
- Override the provider's Encrypt and Decrypt methods and have them use an SQL implementation to encrypt/decrypt/hash. This, of course, will require sending the plain text over the wire to the database so you'd want a secure connection between your application and your database.
- The most viable option does seem to be CLR stored procs. Perhaps one day our client will enable this feature, but there's much red tape to get through first.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply