调用从SQL CLR存储过程MVC3行动 [英] Calling an MVC3 action from a SQL CLR stored procedure

问题描述

我正在调用在ASP.NET应用程序MVC3使用Windows身份验证（一切是禁用）的控制器操作CLR存储过程。 Web服务器IIS是7.5的Windows Server 2008 R2标准版上运行。数据库服务器的SQL Server 2008 R2企业版。下面是CLR PROC的code：

I'm working on a CLR stored procedure that calls a controller action in an ASP.NET MVC3 application that uses Windows Authentication (everything else is disabled). The Web server is IIS 7.5 running on Windows Server 2008 R2 Standard. The database server is SQL Server 2008 R2 Enterprise Edition. Here is the code of the CLR proc:

[SqlProcedure]
public static void UpdateModels()
{
    Uri uri = null;
    HttpWebRequest rq;
    HttpWebResponse rsp;

    Uri.TryCreate("http://testserver/RPM/Configuration/UpdateModels", UriKind.Absolute, out uri);
    rq = (HttpWebRequest)HttpWebRequest.Create(uri);
    rq.Method = WebRequestMethods.Http.Get;
    rsp = (HttpWebResponse)rq.GetResponse();
}

我能够没有问题部署大会SQL Server中，但是当我运行它，我得到以下错误：

I was able to deploy the assembly to SQL Server without problem, but when I run it, I get the following error:

Msg 6522, Level 16, State 1, Procedure UpdateModels, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "UpdateModels": 
System.Net.WebException: The remote server returned an error: (401) Unauthorized.
System.Net.WebException: 
   at System.Net.HttpWebRequest.GetResponse()
   at StoredProcedures.UpdateModels()
.

当我打电话从SQL代理作业（在我的凭据运行）的SP，当我从一个SSMS查询窗口（同样，我的凭据）EXEC如果出现这种情况，或任何其他方法，我想试试。我的Web应用程序，能够调用控制器方法从应用程序本身蛮好的授权用户。

This occurs when I call the SP from a SQL Agent job (run under my credentials), when I EXEC it from a SSMS query window (again, my credentials), or any other method I've thought to try. I am an authorized user of the Web application and able to call that controller method just fine from the application itself.

如果我更改URL是公正的http：// TESTSERVER，它运行良好（当然，它不会做任何事情），但如果我加上转到URL，它失败了，所以它看来问题是应用程序本身，而不是与服务器或它的默认站点。另外，如果我允许匿名身份验证，它的工作原理，所以在某种程度上Windows凭据没有被传递到网站。检查日志证实了这一点：

If I change the URL to be just "http://testserver", it runs fine (of course, it doesn't do anything), but if I add "RPM" to the URL, it fails, so it seems the problem is with the application itself, not with the server or its default site. Also, if I enable anonymous auth, it works, so somehow the Windows credentials are not being passed to the site. Checking the log confirms this:

2012-02-23 12:51:33 10.1.1.1 GET /RPM/Configuration/UpdateModels - 80 - 10.2.2.2- 302 0 0 218

凭据没有被通过。我已经使用CLR SP code内模拟审判，但是这并没有帮助。正如我敢肯定是显而易见的，我是pretty新在这个东西和我可能做一些愚蠢的。如果有人可以把我在正确的道路，我会很感激！

The credentials are not being passed. I've tried using impersonation within the CLR SP code, but that has not helped. As I'm sure is obvious, I'm pretty new at this stuff and am probably doing something daft. If someone could set me on the right course, I'd be grateful!

TIA，

杰夫

推荐答案

嗯，因为我担心，我是做一些愚蠢的。原来模拟的已的答案，但我只是需要来设置模拟级别：

Well, as I feared, I was doing something daft. Turns out impersonation was the answer, but I simply needed to set the impersonation level:

rq.ImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;

调用的GetResponse 方法之前。这样做之后，提供的凭证传递给我刚刚把家原先预计的MVC3控制器。

before calling the GetResponse method. After doing that, the credentials supplied were passed to the MVC3 controller just as I'd originally expected.

想我会我做的方式张贴此万一别人斗争吧。

Thought I'd post this just in case anyone else struggles with it the way I did.

杰夫

这篇关于调用从SQL CLR存储过程MVC3行动的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！