Credentials and extended stored procedures

I have a database script that uses the extended stored procedures sp_OACreate and sp_OCMethod to execute an .exe file. The .exe file is located on the same machine as the SQL Server. At this time it does nothing but log the name of the user calling it.

When I execute the script from Management Studio (logged in as myself) the user being logged as the caller of the .exe is still NT AUTHORITY\SYSTEM. I don't know why NT AUTHORITY\SYSTEM is the caller, cause the SQL Server service runs under another domain account.

I have tried playing around with EXECUTE AS USER but no matter what, the caller of the .exe is always logged as NT AUTHORITY\SYSTEM.

Are there any way I can pass my credentials to the executable that I am calling from the T-SQL script?

Have you given the NT AUTHORITY/SYSTEM privileges on SQL Server?|||

No, not intentionally. It is a default SQL server installation.

I switched the user that runs the SQL service to a domain user (it was local system account before), and that is basically all that has been changed, apart from giving some domain users access to some databases on the server.

creating extended stored procedures in c sharp ?

Is it possible to create an extended stored procedure in C Sharp.
This is for Sql Server 2000.

Books online mentions that you have to use c / c++ to create an extended stored procedure.
However have Microsoft added any support so that the same thing can be done through a simpler language like C Sharp.

Thanks,
Alok.Sorry, not in SQL 2000.

-PatP

creating extended stored procedure using vs.net 2005 for sql2005

Hi,

web searches give no end of how extended stored procedures can only be written in C++ ( or maybe vb also) .

And that extended stored procedures should be abandonded in favour of CLR framework procedures.

And how most articles explain how to convert ESPs to CLR procedures!!!!!

But I need to pass a non-discript block of binary data, extract pieces of data identified by its offset into the block, data type inferred by offset; into data to be written to the SQL database. These offsets are determinede by mapping (C UNION) to C typedef structures.

This cannot be done by managed code, therefore cannot be done by C++ CLR.

It is also ill suited for C# .

Sounds like a job for C++ extended stored procedure.

But how does one create and deploy an ESP with Visual Studio 2005? All wizards seem to insist on CLR.

Help!?

Boyd

This is an interesting problem, why are you sending in a binary block of data like that instead of disassembling it on the client side?

Thanks,

John

|||

Hi,

The binary block could have in the order of 50 different fields concatinated one byte after another. This binary structure maps to a "c typedef struct " definition.

Legacy systems take such a structure, do a "memcpy" out a communication port, a machine at the other end receives the bytes, assigns a pointer of the appropriate "typedef struct" and by inference knows exactly where the data as and the type ( integer, real, character array of fixed length, .... )

Disembling this stuff on the client side requires round-trips to the server for each and every field - very time consuming - especially when I could easily have 400 such blocks (8000 fields) per second from another machine on the network. Then write each field back if it changes (16000 transactions per second over the network). Might be a performance issue. Much more efficient to forward the block to the sql server once and let it do the ripping and shredding.

Creating Extended Procedure using a COM DLL

Hi
How can we create an extened stored procedure using a COM DLL?
Thanks
LalitLalit,
this doesn't answer your question, but I'm wondering why you don't use the
sp_oa... procedures to instanciate the COM object directly. Please have a
look at this for more info:
http://msdn2.microsoft.com/en-us/library/aa172783(SQL.80).aspx
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Creating Extended Procedure using a COM DLL

Hi
How can we create an extened stored procedure using a COM DLL?
Thanks
Lalit
Lalit,
this doesn't answer your question, but I'm wondering why you don't use the
sp_oa... procedures to instanciate the COM object directly. Please have a
look at this for more info:
http://msdn2.microsoft.com/en-us/library/aa172783(SQL.80).aspx
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Creating Extended Procedure using a COM DLL

Hi
How can we create an extened stored procedure using a COM DLL?
Thanks
LalitLalit,
this doesn't answer your question, but I'm wondering why you don't use the
sp_oa... procedures to instanciate the COM object directly. Please have a
look at this for more info:
http://msdn2.microsoft.com/en-us/library/aa172783(SQL.80).aspx
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Creating DLL file for Extended Stored procedure in C#

Hi All,

Is there any way that I can create a dll file for Extended stored procedure(SQL server) in C# ?

I an able to create that in VC++. in VC++ there is DLLMain which serve as entry point and a paramter can be passed to it using Extended stored procedure. But since C# does not have any such entry point. Hence is there any way that I can achieve this?

Any pointers to this will be highly appreciated.

Thanks

Sid

No you can not write an xp in C#, but why not write a SQLCLR method in C# instead and upload that dl to SQL Server? Look for .NET programming in BOL to get more about what you can do.

Niels
|||

If you use SQL Server 2005 then you can use the .NET CLR inside your SQL Server.

If you use SQL Server 2000 still you have to use the COM dlls.

In SQL Server 2005 you need not to have the Extended SPs, the CLR integration helps you to overcome this issue, the deployment and maintaining the CLR assemblies are much better than the legacy COM dlls.

If you use SQL Server 2005, use the .NET CLR integration...

|||

Thanks manivannan,

Your comments helps. I finally ended up using VC++.NET to create the DLL for extended stored procedure.

Creating DLL file for Extended Stored procedure in C#

Hi All,

Is there any way that I can create a dll file for Extended stored procedure(SQL server) in C# ?

I an able to create that in VC++. in VC++ there is DLLMain which serve as entry point and a paramter can be passed to it using Extended stored procedure. But since C# does not have any such entry point. Hence is there any way that I can achieve this?

Any pointers to this will be highly appreciated.

Thanks

Sid

No you can not write an xp in C#, but why not write a SQLCLR method in C# instead and upload that dl to SQL Server? Look for .NET programming in BOL to get more about what you can do.

Niels
|||

If you use SQL Server 2005 then you can use the .NET CLR inside your SQL Server.

If you use SQL Server 2000 still you have to use the COM dlls.

In SQL Server 2005 you need not to have the Extended SPs, the CLR integration helps you to overcome this issue, the deployment and maintaining the CLR assemblies are much better than the legacy COM dlls.

If you use SQL Server 2005, use the .NET CLR integration...

|||

Thanks manivannan,

Your comments helps. I finally ended up using VC++.NET to create the DLL for extended stored procedure.