sp_OACreate and sp_OAMethed Heelllllpppp Please

Question

sp_OACreate and sp_OAMethed Heelllllpppp Please

pcplayer

Mr or Mrs. 500

Points: 539
More actions
March 21, 2008 at 7:25 pm

#184419

I have a question and I hoping to not sound to lame. I am trying to use the sp_oacreate and the sp_oamethed to run my dll from sql sever 2005
It looks some thing like this
EXEC @hr = sp_OACreate 'ActiveDirectoryCom.Adcom', @object OUT,5
IF @hr <> 0
BEGIN --Not able to create the object
Return @hr
END --Not able to create the object
EXEC @hr = sp_OAMethod @object, 'AuthenticateUserFullPath', @FunctionReturn OUT, strUser, strPassword, strDoman
IF @hr <> 0
BEGIN --Not able to execute Com function
Return @hr
END --Not able to execute Com function
I have created my dll in .net and placed it in the root drive of the server. How does OACreate know where to locate my dll?
One executing this fucntion I am returning -2147221005
-214721148 error code.
Have I created my dll incorrectly? The name of the dll is ActiveDirectoryCom and the Class with in the dll is called Adcom and the function that need to run is called AuthenticateUserFullPath. Have I correctly Written the sp_Oa's

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

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

Johan Bijnens SSC Guru Points: 135259 More actions · Answer 1

As you know sql2005 starts with a closed configuration.

If you want to be able to use sp_oa... you'll have to enable "ole" using sp_configure or SAC-features (surface area configurator).

Check out if SQL2005 has a "ligit" alternative for whatever you want to do.

btw there are come articles/forum threads regarding AD using CLR. Maybe that's a better way to do your stuff.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

pcplayer Mr or Mrs. 500 Points: 539 More actions · Answer 2

I tried to run

EXEC sp_configure 'Ole Automation Procedures';

and recieved this error.

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51

The configuration option 'Ole Automation Procedures' does not exist, or it may be an advanced option.

John Beggs SSCommitted Points: 1921 More actions · Answer 3

You'll need to enable Advanced Options first:

EXEC sp_configure 'show advanced options', 1

RECONFIGURE

Then you can use Ole Automation Procedures.

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 4

A better question would be... what is it that you're actually trying to do using OLE Automation?

--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)

pcplayer Mr or Mrs. 500 Points: 539 More actions · Answer 5

Okay I am trying to update a function that has been implemented by the my company for several years what it currently does is authenticate user using LDap (prior to my employement here). We are currently migrating to a new network. What they have set up is used by many applications all over the plant which all call this one stored procedure , which the uses sp_oaCreate and sp_oaMethod using ole automation to execute an authentication application on the server. Since our company spands many countrys we are useing a web service to authenticate based on username, password, and domain.

Writing this application and running as a stand alone works perfectly however when I try to run it from the stored proceedure I get -2147221164 (sp_oaCreate) and -2147211483 (sp_oaMethod) back.

I have tried to use CRL integration in SQL Server 2005 manage it with CREATE ASSEMBLY statement. However when I try to execute:

CREATE ASSEMBLY ActiveDirectoryCom from 'C:\ActiveDirectoryCom\bin\Release\ActiveDirectoryCom.dll' WITH PERMISSION_SET = SAFE

Msg 6213, Level 16, State 1, Line 6

CREATE ASSEMBLY failed because method "add_LookupUserInfoFromIDCompleted" on type "ActiveDirectoryCom.com.cshPeopleFinder" in safe assembly "ActiveDirectoryCom" has a synchronized attribute. Explicit synchronization is not allowed in safe assemblies.

cshPeopleFinder is a webreference to a webservice. I how that there is many other ways to do this however It is requested that I keep this functionality runing with this stored procedure and that the dll remain on the the secure server.

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 6

Great description, Pcplayer... that one is a bit out of scope for things I've done, but between the description and the error messages you've shown, someone is bound to help... sorry I don't know more about that particular area alond with LDAP.

--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)

Jacob Luebbers SSCarpal Tunnel Points: 4834 More actions · Answer 7

@pcplayer:

I'm by no means a developer however two thing strike me:

- OLE Automation is only for code that exposes a COM interface. Unless you've put a COM wrapper around your dotNET assembly and registered it in the COM store on the SQL Server forget about using sp_OACreate, etc. Even if you did, bad idea if your code is all managed - why bother with COM?

- Any dotNET code that tries to access a network resource can't be added as SAFE. I think UNSAFE is the minimum permission set you can use. Since you're using the network (LDAP/AD) you'll need to relax the permissions on that CLR assembly. Attack this from that angle...

EDIT: Hang on - I've just re-read your explanation. You've got a COM DLL haven't you (eg not managed code)? In that case you have to use OLE Automation - but you need to make sure that your DLL is registered on the server first. Run regsvr32 [path to your DLL] in a command prompt on the server (where [path to your DLL] is "C:\ActiveDirectoryCom\bin\Release\ActiveDirectoryCom.dll" or whatever). Forget the CLR integration unless this is managed code.

Regards,

Jacob

Johan Bijnens SSC Guru Points: 135259 More actions · Answer 8

did you check this ad-clr forum thread ?

http://www.sqlservercentral.com/Forums/Topic452981-386-1.aspx

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

pcplayer Mr or Mrs. 500 Points: 539 More actions · Answer 9

Hey thanks for your help. I bailed on the old ole automation that the company had in place and am using CLR Assemblies. I am also checking into this thing that I found where you can link straight to active directory from SQL on http://snippets.dzone.com/posts/show/3800

Happy coding 🙂

pcplayer Mr or Mrs. 500 Points: 539 More actions · Answer 10

SSC Journeyman

Thanks for the info. I rewrote the dll in managed code, whew, and now am using CLR Intergrate. It is working great.