.NET Encryption in SQL Server using sp_OACreate -- sql server programming

Greg Linwood
1/8/2004 3:18:02 PM

It's hard to be certain what is actually causing your problem given the
limited information in your post, but it's not uncommon for external
applications to suffer memory starvation on a SQL Server like this because
it's SQL Server's nature to want to use as much of the available memory on a
server as possible. This is generally a good thing as you want SQL Server to
make full use of the computer's resources.

I'm not sure exactly how you've implemented your .Net library & COM wrapper,
but perhaps it's simply the design of your library or wrapper?

Windows offers CryptoAPIs which you can get at either directly from C, C++,
VB or through the .net framework (I'm not sure if .net uses the win32 crypto
apis actually - this is an assumption). If you wrote an xp_, it will be
relatively light-weight as you can avoid various overheads of the other
approaches. I once used a VB6 COM .dll in a high-usage environment to access
the Win32 Crypto APIs & it worked very well even under heavy load due to
careful design & COM+ technology.

However, there are also other possibilities.

(a) Given you've stated that the crypto library is heavily used & needs
memory, you might simply consider setting a maximum limit on SQL Server's
memory. With a bit of trial & error, you may find that there is a
comfortable setting where SQL gets enough memory & the library gets enough
memory.

(b) You might move the crypto library to a remote server. Assuming the real
bottleneck is memory, the introduction of the network round-trip may be a
good trade off.

(c) You might re-engineer the COM server to a design pattern which is more
light-weight.. Perhaps using a JIT technology such as COM+

(d) Perhaps the problem is simply that your sql code is failing to clean up
instances of the crypto library objects - maybe an adjustment to ensure that
sp_OADestroy is doing it's task properly might be a silver bullet to your
problem.

(e) Build an xp_

HTH

Regards,
Greg Linwood
SQL Server MVP

[quoted text, click to view]

Palani Perumal
1/8/2004 6:27:30 PM

Thank you very much for the idea and possibilities...Where I can find
particular .NET implemented Crypto is available in any VC++ implementation ,
so that I can think of making extended stored procedure ?

We use one of .NET implemented AES algorithm - Rijndael....

Thanks,

- Palani Perumal MCDBA
Med-I-Bank Inc

[quoted text, click to view]

Palani Perumal
1/8/2004 6:30:02 PM

Thank you very much for the article ...One question I have got after reading
this...

If I understood correctly from the article, if we have .NET class with COM
Callable wrapper ..it can't be used in SQL Server with sp_OACreate ?

If that is the case I was wondering how it works for us for these
long...Please let me know if you have any idea on this..

Ideally I see creating my crypto classes using any Win32 implementation and
creating extended procedure will be the ideal solution to resolve the
issue...

Thanks,

- Palani Perumal MCDBA
Med-I-Bank Inc

[quoted text, click to view]

yhhuang NO[at]SPAM online.microsoft.com
1/9/2004 1:57:46 AM

Hello Palani and Greg,

Thanks for posting in the group.

Do you mean some VC++ samples on Crypto API set? If so, we can refer to the
following materials:

1) MSDN link on Cryptography APIs:
http://msdn.microsoft.com/library/en-us/security/security/cryptography_porta
l.asp?frame=true

2) VC++ samples on Cryptography APIs:
We need to install Platform SDK to get it. The directory of these samples
is: \Program Files\Microsoft SDK\Samples\Security\CryptoApi
Platform SDK can be downloaded at
http://www.microsoft.com/msdownload/platformsdk/sdkupdate/

For questions on Cryptography programming, please post questions in:
microsoft.public.platformsdk.security

All security SDK programming related issues are posted in the newsgroup
above.

The reason why we recommend posting appropriately is you will get the most
qualified pool of respondents, and other partners who the newsgroups
regularly can either share their knowledge or learn from your interaction
with us. Also, this is to make sure that the responders can better track
the problem. Thank you for your understanding.

If there is any we can do for you, please feel free to post here.

Best regards,
Yanhong Huang
Microsoft Community Support

Get Secure! ¨C www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

yhhuang NO[at]SPAM online.microsoft.com
1/9/2004 1:57:46 AM

Hello Palani and Greg,

Thanks for posting in the group.

Do you mean some VC++ samples on Crypto API set? If so, we can refer to the
following materials:

1) MSDN link on Cryptography APIs:
http://msdn.microsoft.com/library/en-us/security/security/cryptography_porta
l.asp?frame=true

2) VC++ samples on Cryptography APIs:
We need to install Platform SDK to get it. The directory of these samples
is: \Program Files\Microsoft SDK\Samples\Security\CryptoApi
Platform SDK can be downloaded at
http://www.microsoft.com/msdownload/platformsdk/sdkupdate/

For questions on Cryptography programming, please post questions in:
microsoft.public.platformsdk.security

All security SDK programming related issues are posted in the newsgroup
above.

The reason why we recommend posting appropriately is you will get the most
qualified pool of respondents, and other partners who the newsgroups
regularly can either share their knowledge or learn from your interaction
with us. Also, this is to make sure that the responders can better track
the problem. Thank you for your understanding.

If there is any we can do for you, please feel free to post here.

Best regards,
Yanhong Huang
Microsoft Community Support

Get Secure! ¨C www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

Greg Linwood
1/9/2004 11:13:29 AM

imho, that article doesn't communicate it's message very clearly.

It's topic states: "INF: Using Extended Stored Procedures or SP_OA Stored
Procedures to Load CLR in SQL Server Is Not Supported" (no differentiation
of in proc / ex proc)

However, the first paragraph reduces the scope of what's not supported to:
"for any loading of the libraries that you must load to run IN THE SQL
SERVER MEMORY SPACE."

OK - there are issues with running .net assemblies IN PROCESS (tls, fibres
etc), but OUT OF PROCESS should be fine. This makes sense because an out of
process server uses it's own memory & does entirely it's own thing - it's a
central tenet of COM, black box, interface inheritance yada, yada, yada..

So, assuming your COM server's configured as out of process, you should have
no issues (other than your own design / coding) for wrappinng .Net
assemblies with COM wrappers & calling over sp_OACreate.

However, my 20c would be that this is still a bit of an un-natural fit &
you'd be at the whim of whatever commplications there are in wrapping .net
assemblies with com wrappers. However, if the design is out of process & you
can work out the memory contention issue, go with it if it works as you'll
still have the same memory contention issue with whatever other external
solution you use (although xp_ will be more light weight). The other benefit
is that you'll be coding in an environment that is arguably more up to date
& future proof than coding against the Win32 crypto apis.

Unfortunately, in response to your other question about where you could get
access to c based examples of win32 crypto api calls from an xp_, I can't
help you there as I've never bothered doing this as an xp_ b/c it was just
so easy to do with vb6 over sp_OA.

Regards,
Greg Linwood
SQL Server MVP

[quoted text, click to view]

sql server programming : .NET Encryption in SQL Server using sp_OACreate