May 16, 2008 at 4:51 am
Hi
I have written a library of stored procedures in C# that p/invoke a dll written in Delphi for Win32. The reasone for this is that the dll contains license key validation routines. I.e. the stored procedures check that the license is valid before executing.
The reason that the code is written in delphi for win32 is due to the nature of the license key, and how it is built, etc.
But when I go to deploy the Stored procediure library I get the following error:
CREATE ASSEMBLY failed because type "KMS.Interop.CheckLicense" in safe assembly "KMS_StoredProcedures" has a pinvokeimpl method. P/Invoke is not allowed in safe assemblies.
But when I set the database permision level to EXTERNAL I get the following error:
CREATE ASSEMBLY for assembly 'KMS_StoredProcedures' failed because assembly 'KMS_StoredProcedures' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission. If you have restored or attached this database, make sure the database owner is mapped to the correct login on this server. If not, use sp_changedbowner to fix the problem.
I have tried searching MSDN but the documentation is vague as to how to resolve this. I have tried the adventure works example by using the Visual Studio SN command line tool and signing my library with the key generated.
Any help would be greatly apprieciated!!! as I do not want to write all the insert/deletes/updates into the application.
TIA
Graham Harris
May 16, 2008 at 3:42 pm
Found this article in the Help files:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/denet9/html/2111cfe0-d5e0-43b1-93c3-e994ac0e9729.htm
Note
This might only be specific to SQL Server 2008 but hopefully the information will be will help me!!! I will be posting my thoughts here later.
Graham Harris
May 16, 2008 at 3:57 pm
OK That article was V.V. V. interesting short of setting the code as UNSAFE you can not use P/Invoke. This is not an option as Security in SQL Server will be reduced to an uneceptably low level. I.e. if you mark your C# code as SAFE or EXTERNAL_ACCESS you can not use P/Invoke.
I cannot move the License key code to the .NET world due a) the fact that it is easier to decompile a .NET app that a native app, and b) the encoding mechanism can not be ported.
Graham Harris
May 16, 2008 at 9:02 pm
Suggestion: Put a service wrapper around your license key validation DLL and call it with LPC.
[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]
May 17, 2008 at 4:43 am
Cool Thanks!!! I will investigate it further!!
Graham Harris
May 17, 2008 at 7:00 pm
Let us know how it works out...
[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]
May 21, 2008 at 4:26 pm
Could not use LPC calls - again will use interop calls, plus if the API fails it could cause the server to blue screen, and there is a limitation in the amount of data passed between the processes. Found that I can call SOAP Services!!! So wrapped the validation routines in a web service. Which wraps the interop calls.
Graham Harris
May 22, 2008 at 9:50 am
You could use Service Broker to indirectly call the DLL. I've used Service Broker before for similar purposes (accessing serial ports), it works as advertised but has a steep learning curve.
First sp: composes a message, sends it using service broker
App: receives message, checks license key, calls 2nd stored proc
Second sp: takes the desired action
By using Service Broker, the CLR is free of the SQL Server security requirements. When the Service Broker app and SQL Server are on the same machine, there is no noticeable speed lag.
The app can be written in any language that can call SQL Server. The app can be running as a service. You could write the app in C# .NET and use a p/invoke to the native DLL with no security issues.
May 22, 2008 at 9:55 am
I'll have a look into it.
Thanks
Graham Harris
May 23, 2008 at 12:25 pm
Now that you wrapped your p/invoke into a web service, can't you just call it via SQLCLR. you will need to set to your SQLCLR function(s) to use External Access. But since you wrapped in a service its now out-of-process and knows nothing about your p/invokes.
-tom
May 23, 2008 at 1:42 pm
Yes that is true. But I always feel that there is a better way. Partly because I was not very familiar with web services.
This to my mind highlights a problem with .NET in that you can decompile the code and reverse engineer. If you want proof of this download the following application: http://www.aisto.com/roeder/dotnet/Download.aspx?File=Reflector
Graham Harris.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply