CLR stored Procedure Deployment Error

Question

CLR stored Procedure Deployment Error

Manohar-481628

SSC Rookie

Points: 46
More actions
April 14, 2009 at 12:16 am

#208320

Hi All,
I had developed an SQL Server Project for making all my stored Procedures as CLR managed stored Procedures, and I had successfully converted them and deployed it to the SQL Server 2005 Database without any errors.Now When I try to run the application it throws me an error like InvalidOperation and the error says ' ' does not exist.
If the stored Procedure does not have any input parameters it is executing fine from the application but it is unable to find the procedures which have parameters.
I tried running the procedure from back end itself and it worked well but from the application it is not able to find the procedure.
Here is the sample code which I had written for converting into CLR Procedure........
Dim sp As SqlPipe = SqlContext.Pipe
Dim strBuild As New System.Text.StringBuilder
Dim sqlConn As SqlConnection = New SqlConnection("context connection=true")
Dim sqlCmd As SqlCommand = New SqlCommand
If sqlConn.State = ConnectionState.Closed Then sqlConn.Open()
sqlCmd.Parameters.Clear()
sqlCmd.CommandText = "update parameter_master set pm_var_ParameterValue=@Ipaddress where pm_var_ParameterName='BOS_IpAddress' and pm_num_SiteId=@siteid"
sqlCmd.CommandType = CommandType.Text
sqlCmd.Connection = sqlConn
sqlCmd.Parameters.AddWithValue("@Ipaddress", Ipaddress)
sqlCmd.Parameters.AddWithValue("@siteid", siteid)
sp.ExecuteAndSend(sqlCmd)
If Not sqlConn Is Nothing Then sqlConn.Close()
Can anyone kindly help me out solve this problem??

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

Your biggest problem is that you have misused SQLCLR here. This is a standard TSQL stored procedure operation since you are doing a simple update using TSQL anyway. SQLCLR is not the correct way to do this, and you should use standard TSQL Stored Procedures for this. If you can do it with TSQL, then you don't need to use SQLCLR, and there are no benefits to using SQLCLR. It is in fact slower to use SQLCLR in almost every case that you can use TSQL alone to do the same task.

Why did you convert your TSQL stored procedures to SQLCLR? What were you expecting to get from doing so?

The solution to your problem is to not use SQLCLR where TSQL alone can do the process. Undo your conversion and stick with your TSQL stored procedures.

Manohar-481628 SSC Rookie Points: 46 More actions · Answer 2

HI Jonathan,

Thank you very much for your reply.Actually we thought to hide the stored procedure code from the client machine for security purposes, so that we've gone to this SQLCLR model.And for Deploying on client machines also we thought that its only required to deploy a single Dll rather than running each and every script.

That's why we have gone for this SQLCLR model and if you dont mind can you suggest us any other alternative for my requirement to achieve security for my stored procedures.

Your help will be very much appreciated.

Thanks and Regards

Manohar

jmuldoon Old Hand Points: 377 More actions · Answer 3

Why not just encode your stored procedures ?

Example ;

create procedure sp_Sample @param1 int

with ENCRYPTION

as

I am not sure just how "secure" your security needs are here.

Admittedly, the encryption in Sql2000 was very easy to break for most programmer types, but I believe that I've read that MS made this encryption much better in 2005 and 2008. (I could be wrong about that, but still, your average user probably would not even bother to try to decode it)

I guess it is a matter of just how secure you want it to be.

Manohar-481628 SSC Rookie Points: 46 More actions · Answer 4

Manohar-481628

SSC Rookie

Points: 46

April 15, 2009 at 11:40 pm

#978608

thank you very much for your reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 5

jmuldoon (4/15/2009)
Admittedly, the encryption in Sql2000 was very easy to break for most programmer types, but I believe that I've read that MS made this encryption much better in 2005 and 2008. (I could be wrong about that, but still, your average user probably would not even bother to try to decode it)

WITH ENCRYPTION is a really poor name for this functionality. It does not encrypt the code at all, and SQL 2005/2008 are the same as SQL 2000 in this aspect. It only obfuscates the code at best, it doesn't actually encrypt it, and anyone with Google and the internet can easily get the code back from a "encrypted" stored procedure.

The same holds true for SQLCLR as well. There is a SQL2005Browser addin for .NET Reflector on Codeplex that allows the assemblies to be reverse engineered into code.

I would posit that if you are that worried about your TSQL code, you shouldn't be deploying databases in untrusted locations. These days very few software vendors have encrypted code in SQL Server databases because there really isn't anyway to protect the code. I have on more than one occassion reversed the encryption in vendor databases and it only takes a few seconds to do.

Paul White SSC Guru Points: 150467 More actions · Answer 6

Hey guys,

I can't help but wonder what is driving the perceived need to 'encrypt' at all?

If someone could explain that clearly for me, I'd love to add my $0.02 to the discussion.

Cheers,

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

The usual reason (which also appears to apply to this case) is that they want to protect their proprietary code from scrutiny/reverse engineering by the customer. It has been discussed many times here, generally coming down to:

1) The law is your real protection, therei's nothing that can protect your executable code from scrutiny on a customer site. and,

2) If that is not good enough for you, go to an Application Service Provider model, so that they never have physical access to your executable code.

The OP's rarely listen to this though.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Paul White SSC Guru Points: 150467 More actions · Answer 8

A good summary Barry.

In my experience, most 'proprietary' code is best kept hidden because it's quality is generally so lacking! I have yet to see any truly wonderful SQL in a third-party application! 😀

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Manohar-481628 SSC Rookie Points: 46 More actions · Answer 9

Hi Jonathan,

Thank you very much for your reply.I had already tried this with ENCRYPTION method for my stored procedures and as you said it is not that much secured encryption method in my observation.

And if you can post me the SQL2005 addin for .NET Reflector to reverse engineer the assemblies.

As of now I dont find any other solution to my problem other than this.