Return value from CLR Assembly

• mickyc1

  SSC Veteran

  Points: 239

  More actions

  October 17, 2012 at 8:15 am

  #263424

  I have a C# assembly that returns a string. I created an SP to call this assembly and it works. But when i try to assign the return value to a variable it does not work.

  Here is my SP:

  ALTER PROCEDURE GetDemoPass (@newpass nvarchar(max), @suppliedSalt nvarchar(max))

  AS EXTERNAL NAME Salt.[Salt2.SQLSalt].setEncPassword

  go

  when i do an exec on the stored procedure the value is returned but when i try to do something like this:

  EXEC @salt = dbo.[GetDemoPass] N'Test', N'Test'

  The value isn't held in @salt. Is there something simple i am missing?

• Jeff Moden

  SSC Guru

  Points: 1004596

  More actions

  Follow

  October 17, 2012 at 8:21 am

  #1550037

  Gosh... based on the variable name, it looks like you're trying to grow your own encryption. Unless you have a degree in cryptology, I strongly recommend that you don't grow your own. SQL Server has some reasonable encryption tools that you should look into. If those aren't good enough, then I recommend spending a buck or two on some encryption software. Another name for most people that try to write their own encryption is "hacked".

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Lowell

  SSC Guru

  Points: 323522

  More actions

  October 17, 2012 at 8:36 am

  #1550054

  I had to deploy a CLR similar to this issue once; it used a "custom" flavor/implementation of an AES-like encryption that was almost, but not the same as, a true AES encryption; the application kind of hardcoded the implementation into it, and it was easier to convert the vb6 code to vb.net to maintian compatibility.

  My "Encrypt" function is the equivilent of whatever your Salt.? class returns as a string for your scalar function.

  this works perfectly for me:

  /*

  results

  DF1F7AA5A100F38B4F29A6DB94D282D58FAB56FE7F25C15ADE2562AE83D3E5A2

  */

  declare @salt varchar(500)

  EXEC @salt = dbo.CLR_EncryptAES N'Test', N'Test'

  print @salt

  and the model for my CLR looks like this...you can run it through a VB to c# converter if you need to:

  <Microsoft.SqlServer.Server.SqlFunction()> _

  Public Shared Function CLR_EncryptAES(<SqlFacet(MaxSize:=512)> ByVal TextString As SqlString, <SqlFacet(MaxSize:=512)> ByVal Password As SqlString) As SqlString

  Dim _sResults As SqlString

  _sResults = New SqlString(Encrypt(TextString.ToString, Password.ToString))

  Return _sResults

  End Function

  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!