Referencing SQLDMO in UNSAFE assembly

I created a SQL Server Project in VS 2005, and tried to add a reference to SQLDMO Object dll. But the reference --> add reference, does not allow it. It does not work, even if I set the Assembly Permission Level to UNAFE/EXTERNAL ACCESS.

Reason I'm trying to do this:

We have a SQL 2000 stored procedure that uses SQLDMO using sp_OACreate to BCP files to database. We are converting to 2005 and because of SOX restrictions, I'm trying to replace the sp_OACreate part with external stored procedure written in C#.

According to BOL, I thought atleast UNSAFE should support this, but it seems not.

Probably, I'm expecting too much..

Your help is greatly appreciated.

Thanks

Thanks to Vineet Rao's posting to one of of the answers on this forum, to a similar problem.

I registered "signed" InterOp.SQLDMO.dll (which is obtained by referecing to a regular project) , as UNSAFE, and it appeared in the Reference section of SQL Server Project.

Thanks

Baskar

Referencing a dll ?

Hi,
how do i reference a dll ? i have a dll that i did in vb.net and i put the
dll in reportserver/bin and reportmanager/bin.I referenced it using report -
references. but i still get an error saying it cannot find the dll. how do i
reference a dll ?
ThanksTry browse from report paramater and reference and click ..
and get your dll
Amarnath
"RP" wrote:
> Hi,
> how do i reference a dll ? i have a dll that i did in vb.net and i put the
> dll in reportserver/bin and reportmanager/bin.I referenced it using report -
> references. but i still get an error saying it cannot find the dll. how do i
> reference a dll ?
> Thanks

Referencing a CLR DLL in a Custom Task

Is there a way you can reference a prebuilt CLR function in a custom dataflow task?

The CLR and the custom dataflow task are one in the same. The reason that I'm designing the custom task is that I find that it's time consuming to make a call to the database to retrieve a function to work on something that's already in memory. Also, another reason is that if the code needs to be updated, it doesn't have to be done in 2 places allowing for less confusion.

I tried adding a reference to the CLR function in my SSIS dataflow task, but when I try running it in SSIS, I get the error saying it cannot use a SQL CLR defined task in the package which I believe is due to this reference in the CLR code:

[Microsoft.SqlServer.Server.SqlFunction]
public static string toCase(string text, string caseType)

Is there a way to circumvent the [...SqlFunction] line of code when the DLL is referenced by an SSIS dataflow task, or do I just have to deal with having 2 copies of the same code?

Thanks in advance!

Nevermind, I guess the only way to do it is to separate the function as a core class and then create a CLR wrapper and then an SSIS wrapper.

References other assembly with error...

I have a vb.net project which compiled to CommonLibrary.dll with no error
when I add references CommonLibrary.dll on my reporting project,
it will show an error(as following) when I compile the reporting project
Error message:
"\TestReport.rdl Error while loading code module: â'CommonLibrary,
Version=1.0.1825.16800, Culture=neutral, PublicKeyToken=nullâ'. Details: ?"
Am I missing something or do something wrong'
Thx, pls helptry adding the dll again to the report by going to report properties and
selecting the dll again. this could be because the report is referencing an
old version of the dll.|||See URL: http://blogs.sqlxml.org/bryantlikes/articles/824.aspx
(thanks Bryant)
It is said that you must copy compiled dll to the folder:
C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer
"Louie" wrote:
> I have a vb.net project which compiled to CommonLibrary.dll with no error
> when I add references CommonLibrary.dll on my reporting project,
> it will show an error(as following) when I compile the reporting project
> Error message:
> "\TestReport.rdl Error while loading code module: â'CommonLibrary,
> Version=1.0.1825.16800, Culture=neutral, PublicKeyToken=nullâ'. Details: ?"
> Am I missing something or do something wrong'
> Thx, pls help

Reference for Security Nazis

Does anyone know of a quick reference I could provide to the it security
folks at my work that outlines what file extensions, ports, and dll's sql
server uses? They've gone hog wild with 'security' software here to the
point that they invariably end up shutting down one behavior or another
within SQL each time they do a 'security upgrade'. Grrrr.

TWSince no one replied, and for the sake of the group, I'll post what I've
come up *so far* as far as necessary ports, file extensions, etc that SQL
needs ...

PORTS:
1433

PROTOCOLS:
smtp

FILE EXTENSIONS:
..mdf
..ldf
..bak
..sql
..csv
..rpt

...I'll update as I come up with more

--TW

"Tech Witch" <tech.witch@.gmail.NOSPAM.com> wrote in message
news:Q_Yzd.6775$iC4.5546@.newssvr30.news.prodigy.co m...
> Does anyone know of a quick reference I could provide to the it security
> folks at my work that outlines what file extensions, ports, and dll's sql
> server uses? They've gone hog wild with 'security' software here to the
> point that they invariably end up shutting down one behavior or another
> within SQL each time they do a 'security upgrade'. Grrrr.
> TW|||Hi

In some ways your security guys should be telling you what they want to do
and what effect it should have. You should change from the standard ports,
SMTP is not needed as a default. Not sure why file extensions are needed,
you should avoid virus checking the database files, None of the extension
you mention contain executable code, restricted access to programs and
directories should be implemented, and non-essential ones removed.

The is alot of information on http://www.sqlsecurity.com
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=24

http://www.microsoft.com/security/g.../SQLServer.mspx
http://www.microsoft.com/technet/pr...n/sp3sec04.mspx
http://www.microsoft.com/technet/se...s/mbsahome.mspx
http://www.microsoft.com/downloads/...&displaylang=en

You should be working with the security guys, they may have already saved
your skin.

John

"Tech Witch" <tech.witch@.gmail.NOSPAM.com> wrote in message
news:SkCAd.4116$F67.400@.newssvr12.news.prodigy.com ...
> Since no one replied, and for the sake of the group, I'll post what I've
> come up *so far* as far as necessary ports, file extensions, etc that SQL
> needs ...
> PORTS:
> 1433
> PROTOCOLS:
> smtp
> FILE EXTENSIONS:
> .mdf
> .ldf
> .bak
> .sql
> .csv
> .rpt
> ...I'll update as I come up with more
> --TW
> "Tech Witch" <tech.witch@.gmail.NOSPAM.com> wrote in message
> news:Q_Yzd.6775$iC4.5546@.newssvr30.news.prodigy.co m...
>> Does anyone know of a quick reference I could provide to the it security
>> folks at my work that outlines what file extensions, ports, and dll's sql
>> server uses? They've gone hog wild with 'security' software here to the
>> point that they invariably end up shutting down one behavior or another
>> within SQL each time they do a 'security upgrade'. Grrrr.
>>
>> TW
>>
>>|||Thanks, John. I will check these links out. To give you a better idea of
what I'm up against, we had several incidents where our security folks
applied a series of patches that caused some undesirable effects like using
a resource-intensive application to perform virus scanning on files with
..BAK, .MDF, and .LDF extensions each time the files were written (some were
in the GIGs), then they disabled our SMTP ports on the servers with some
firewall software, causing our SQL alerts to stop being sent to DBA's (we
don't use SQL mail here). I'm getting tired of them not telling us what
they are changing and not testing the results of said actions, so I want to
come up with a list of necessary SQL functions & files for them to refer to
next time. Thanks again for your suggestions. I'll post an updated list
when I'm done.

TW

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:41d3238e$0$5263$afc38c87@.news.easynet.co.uk.. .
> In some ways your security guys should be telling you what they want to do
> and what effect it should have. You should change from the standard ports,
> SMTP is not needed as a default. Not sure why file extensions are needed,
> you should avoid virus checking the database files, None of the extension
> you mention contain executable code, restricted access to programs and
> directories should be implemented, and non-essential ones removed.
> <snip>
> You should be working with the security guys, they may have already saved
> your skin.