CLR Integration with Third Party DLL

Question

CLR Integration with Third Party DLL

Satheesh Mohan

Old Hand

Points: 375
More actions
December 11, 2008 at 3:32 am

#196908

Hi,
Help me to solve the issue.
I created wrapper class that used Microsoft Enterprise Library - Cryptography dll
and when i try to create assembly in sql server, it asks that supported assembly is not in the same location, then I copied cryptography library to the current bin folder and tried once again.
there was an error saying that 'system.management' assembly is not in the current database.
at last there was an problem with 'system.runtime.serialization.formatter.soap' has nameCache as a static member, and that has to be in readonly
thanks in advance.
---
Satheesh Mohan
http://www.satheeshmohan.com

Viewing 14 posts - 1 through 13 (of 13 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

To even attempt to get it working, you would have to set trustworthy on, grant unsafe assembly to the database owner, and then try manually registering the DLL's as assemblies with UNSAFE access. Even then, certain CAS and HPA's can prevent SQL from loading the assemblies.

My question is why are you not using SQL Servers native encryption and decryption?

Satheesh Mohan Old Hand Points: 375 More actions · Answer 2

Thanks Jonathan

Issue has been solved.

Have a new issue.

In my assembly i have used Enterprise library - cryptography, that required app config.

when I run my function it throws an error that app configuration is not found.

I have copy my configuration to sqlservr.exe.config in \Binn folder under the root path of the SQL Instance(C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn) but still i have problem.

I already used enterprise library encryption to encrypt data that stored in my db. so not interested to use sql server native encryption.

Thanks in advance

---
Satheesh Mohan
http://www.satheeshmohan.com

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

How was it solved?

[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]

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

Satheesh Mohan (12/12/2008)
Thanks Jonathan
Issue has been solved.
Have a new issue.
In my assembly i have used Enterprise library - cryptography, that required app config.
when I run my function it throws an error that app configuration is not found.
I have copy my configuration to sqlservr.exe.config in \Binn folder under the root path of the SQL Instance(C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn) but still i have problem.
I already used enterprise library encryption to encrypt data that stored in my db. so not interested to use sql server native encryption.
Thanks in advance

SQL Server is not like a standard .NET application. To add a .config file, or for changes in an existing one to be picked up, you have to restart the SQL Server Service. This is why it is often better to store the information in a configuration table, that the CLR assemblies can read from over using an app.config file.

Satheesh Mohan Old Hand Points: 375 More actions · Answer 5

I solved this problem by

1. setting set trustworthy on and

2. signing my assembly a strong name (adding snk key).

---
Satheesh Mohan
http://www.satheeshmohan.com

Satheesh Mohan Old Hand Points: 375 More actions · Answer 6

In which name I should have app.config file?(app.config or sqlservr.exe.config),

in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn path

or any other path?

---
Satheesh Mohan
http://www.satheeshmohan.com

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

It should be sqlservr.exe.config, and it should be under the \Binn folder for the root path of the SQL Instance.

http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/33/Default.aspx

Satheesh Mohan Old Hand Points: 375 More actions · Answer 8

My config setting is not picking up from that location even I restarted by sql server

what could be the problem?

---
Satheesh Mohan
http://www.satheeshmohan.com

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

Without seeing your code, I couldn't begin to say. Try creating the simple configuration file and function from the article I linked to above, and see if it will pull the demo setting.

Satheesh Mohan Old Hand Points: 375 More actions · Answer 10

Thanks

That is my next step.

---
Satheesh Mohan
http://www.satheeshmohan.com

Satheesh Mohan Old Hand Points: 375 More actions · Answer 11

Hi,

I have an issue with security.

Error Message:

System.Configuration.COnfigurationErrorException:

An error occured creating the configuration section header for securityCryptographyConfiguration: That assembly does not allow partially trussted callers(C:\program...\MSSQL.1\Binn\sqlservr.exe.config line 6) ---> System.SecurityException: That assembly does not allow partially trusted callers. :crying::crying::crying:

I added in assemblyinfo.cs but i does not help

My assembly is strong named

Kindly help....

---
Satheesh Mohan
http://www.satheeshmohan.com

v.sreelekha SSC Veteran Points: 266 More actions · Answer 12

Hi All,

I am trying to access the configuration file from sql clr sp,I am able to do that in win xp os,sql server 2005 SE.

But when i deployed the same sp on sql server 2005 EE and win2k8 os,i had a problem in reading the connection string from the configuration file.

Please let me know whether this is supported.

Appreciate your help in advance.

Thanks & Regards,

Sreelekha

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

v.sreelekha (1/20/2012)
Hi All,
I am trying to access the configuration file from sql clr sp,I am able to do that in win xp os,sql server 2005 SE.
But when i deployed the same sp on sql server 2005 EE and win2k8 os,i had a problem in reading the connection string from the configuration file.
Please let me know whether this is supported.
Appreciate your help in advance.
Thanks & Regards,
Sreelekha

No, the use of a configuration file in SQLCLR is not a supported operation. It works, but that doesn't mean it is supported by Microsoft.

Thanks,