Execute C# method / function from trigger in SQL Server

Question

I'm working on a project which requires execution of C# method / function (coded on a web page) using a trigger or stored procedure in SQL Server.

I found this Execute-NET-Code-under-SQL-Server

But as stated on this link, I have to change the database property TRUSTWORTHY to ON.

Is there any way I can do this without changing database properties?

Thanks in advance

You most definitely don't have to change this database property. You need it only when your assembly is unsigned and needs to access external resources — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Apr 24, 2015 at 15:39
@PanagiotisKanavos Since OP need to somehow invoke external webpage (if I got his point correctly) - he need TRUSTWORTHY ON and assembly with EXTERNAL_ACCESS permission. The only alternative - is running external program, as I've stated in my answer. — Andrey Korneyev
– Andrey Korneyev, Commented Apr 24, 2015 at 15:44

Andrey Korneyev · Accepted Answer · 2015-04-24 15:28:37Z

1

Well, if you don't want (or can) use SQLCLR - you can create some standalone console application containing all c# code you need and then execute this application with some parameters using xp_cmdshell.

But note: xp_cmdshell is disabled by default, you have to enable it using

EXEC sp_configure 'xp_cmdshell', 1

Since xp_cmdshell is server option - you don't need to change database properties, but you have to change server options to enable it in the case it is disabled. Choose yourself - what to change. ;)

answered Apr 24, 2015 at 15:28

Andrey Korneyev

26.9k16 gold badges73 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Panagiotis Kanavos Over a year ago

This is far worse from a security viewpoint than changing a database option - xp_cmdshell can easily execute malicious scripts using SQL Server's service account

Michael Petito · Accepted Answer · 2015-04-24 16:21:12Z

Do you need to execute the external code synchronously? In other words, must the external code start and run to completion before your trigger ends?

If the answer is yes, then I suggest changing your design. You're using triggers incorrectly and making whatever DML operation fires the trigger tremendously slow.

Assuming the answer is no then, you have options to queue up whatever work needs to be done and have that work executed asynchronously. For example, your trigger could create a record in a work-queue table when it needs this external code to run. Some other process (e.g. a scheduled task, SQL Agent, something in your application, SQL Server Broker) can monitor this table for new records and fire off the appropriate calls as needed.

Collectives™ on Stack Overflow

Execute C# method / function from trigger in SQL Server

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related