Calling into a C# DLL from T-SQL

Hi:
I have an encryption DLL written in C#. Is there a way I can call into it
for encrypting and decrypting data during a T-SQL query?
Thanks,
CharlieSQL Server 2000? No; there is no supported way.
SQL Server 2005 -- yes. You can create a CLR UDF and wrap the DLL. But in
2005 it would make more sense to use the built-in encryption features that
SQL Server provides.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Charlie@.CBFC" <charle1@.comcast.net> wrote in message
news:%23MyZ0rt6FHA.2576@.TK2MSFTNGP09.phx.gbl...
> Hi:
> I have an encryption DLL written in C#. Is there a way I can call into it
> for encrypting and decrypting data during a T-SQL query?
> Thanks,
> Charlie
>|||Hello Adam,

> SQL Server 2000? No; there is no supported way.
Not that I like to disagree with Adam [ :) ] but... could you generate a
COM callable wrapper then write an XP that use that to do this work?
But I do agree, with SQL Server 2005, use the built in stuff instead!
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||You -could-, but it is explicitly mentioned as not supported in BOL.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad74d0fd8c7b8c31d3bd030@.news.microsoft.com...
> Hello Adam,
>
> Not that I like to disagree with Adam [ :) ] but... could you generate a
> COM callable wrapper then write an XP that use that to do this work?
> But I do agree, with SQL Server 2005, use the built in stuff instead!
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>|||"Charlie@.CBFC" <charle1@.comcast.net> wrote in message
news:%23MyZ0rt6FHA.2576@.TK2MSFTNGP09.phx.gbl...
> Hi:
> I have an encryption DLL written in C#. Is there a way I can call into it
> for encrypting and decrypting data during a T-SQL query?
> Thanks,
> Charlie
>
There is in SQL2005, not in SQL2000|||... and here is my canned response to calling managed code from SQL Server
(not including Yukon,
which is of course a different story):
It is not supported for extended stored procedures or sp_OA procedures to ca
ll .NET code in CLR;
hosted within SQL Server's address space..
See:
http://support.microsoft.com/defaul...kb;en-us;322884
Also, below is with permission from David Browne, explaining how you can hav
e SQL Server execute CLR
code executing in its own process:
"
Short answer: Don't do it.
Calling managed code inside a stored procedure is not supported.
http://support.microsoft.com/defaul...kb;en-us;322884
At least not directly. You need some sort of unmanaged proxy to communicate
with your component running in another process.
For instance, http, or, drum roll, a COM+ Server Application.
This will cause COM+ to load an unmanaged proxy object in the SqlServer
process and will load the CLR into a COM+ surrogate process (dllhost.exe).
Which somebody here mentioned last w, and I just got around to testing.
It's all perfectly transparent to you, but you have to set up the COM+
server application.
Remember this is something different from .net remoting. With .NET remoting
you have a _managed_ proxy object in the local process, and so you load the
CLR in the local process as well as the remote process.
Anyway here's what I did:
I created this VB class
comTest.vb listing:
Imports System.Runtime.InteropServices
<ClassInterface(ClassInterfaceType.AutoDual),
ProgId("comTest.comTestClass")> _
Public Class comTest
Public Function Hello() As String
Return "hello"
End Function
End Class
build comTest.dll and registered it with
regasm /codebase comTest.dll /tlb:comTest.tlb
(complains that I haven't strong-named my assembly, which you should do.)
created an empty COM+ server application, set to run under a local
administrator account, and dragged comTest.dll into its components folder.
created an unmanaged host (vbscript will do), and invoked the component
using IDispach just like SQLServer.
test.vbs listing
Set d = CreateObject("comTest.comTestClass")
MsgBox d.Hello
Then I used the .net command line debugger cordbg.exe's 'pro' command to
list the processes hosting the CLR. And procexp.exe from
www.sysinternals.com to verify that the CLR's dll's were not loaded in my
unmanaged process. My unmanaged host did not load the CLR, although it
loaded "comsvcs.dll", and the CLR was loaded by the dllhost.exe process.
Then in sql I ran
declare @.object int
declare @.msg varchar(50)
declare @.rc int
declare @.hr int
declare @.source varchar(1000)
declare @.description varchar(1000)
exec @.rc = sp_oacreate 'comTest.comTestClass', @.object output
if @.rc <> 0
begin
EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
print 'create failed ' + @.description
return
end
exec @.rc = sp_oamethod @.object, 'Hello', @.msg output
if @.rc <> 0
begin
EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
print 'method failed ' + @.description
return
end
print 'return: ' + @.msg
exec @.rc = sp_oadestroy @.object
if @.rc <> 0
begin
EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
print 'destroy failed ' + @.description
return
end
Ran fine, and still only one CLR loaded into dllhost.exe's process. So
think we can safely conclude that COM+ server applications do not violate
the prohibition against running managed code in SQLServer's process and
provide a convenient mechanism for interoperating with managed code from
TSQL.
David
"
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OsmkX9t6FHA.636@.TK2MSFTNGP10.phx.gbl...
> You -could-, but it is explicitly mentioned as not supported in BOL.
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Kent Tegels" <ktegels@.develop.com> wrote in message
> news:b87ad74d0fd8c7b8c31d3bd030@.news.microsoft.com...
>