部署到IIS时，带有外部SQL Server DB的WCF /实体框架不起作用 [英] WCF/Entity Framework with External SQL Server DB Does Not Work when Deployed to IIS

问题描述

我有一个简单的WCF项目，该项目使用Entity Framework访问AdventureWorks数据库。它实际上出自《 Windows Communication Foundation 4：循序渐进》一书。

I have a simple WCF project that uses the Entity Framework to access the AdventureWorks database. Its actually out of a book Windows Communication Foundation 4: Step by Step.

问题在于SQL Server数据库不是本地SQL Server Express实例，而是名为 win01s305 的外部计算机上的完整SQL Server 2008 R2实例。我通过从数据库生成从外部数据库创建了数据模型。直到我将WCF服务发布到IIS为止，数据访问才能与内置ASP.NET Web服务器（Cassini？）完全一起使用。然后，远程SQL Server计算机上的凭据/帐户就会不同，并且数据访问也会停止。

The catch is that the SQL Server database is not a local SQL Server Express instance but a full SQL Server 2008 R2 instance on an external machine called win01s305. I created a Data Model from the external Database by Generate from Database. The data access works fully with the built-in ASP.NET Web Server (Cassini?) until I publish the WCF service to IIS. Then the credentials/account are different on the remote SQL server machine and the data access stops working.

如果遇到问题，本书会指定一个脚本：

The book specifies a script if you have trouble:

USE [AdventureWorks]
GO
CREATE USER [IIS APPPOOL\DefaultAppPool] FOR LOGIN [IIS APPPOOL\DefaultAppPool]
GO
EXEC sp_addrolemember N'db_owner', [IIS APPPOOL\DefaultAppPool]
GO
GO
CREATE USER [IIS APPPOOL\ASP.NET v4.0] FOR LOGIN [IIS APPPOOL\ASP.NET v4.0]
GO
EXEC sp_addrolemember N'db_owner', [IIS APPPOOL\ASP.NET v4.0]
GO

问题是这些帐户在远程SQL Server计算机上不存在。该计算机上没有IIS或.NET Framework，只有SQL Server。我该怎么做？

The issue is these accounts do NOT exist on the remote SQL Server machine. The machine does not have IIS or the .NET Framework on it, just SQL Server. How can I do this?

推荐答案

您要么必须在域帐户下运行应用程序池，然后在您的用户帐户中授予该用户权限数据库，或者在sql服务器上打开混合模式身份验证后，创建一个经过SQL身份验证的用户，并将其名称和密码放在您的连接字符串中。

You either have to run your app pool under a domain account and give that user rights in your database or, with mixed mode authentication turned on on the sql server, create an SQL authenticated user and put it's name and password in your connection string.

USE [master]
GO
CREATE LOGIN [MYDOMAIN\MyAccount] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
USE [MyDatabase]
GO
CREATE USER [MyDomain\MyAccount] FOR LOGIN [MyDomain\MyAccount]
GO
USE [MyDatabase]
GO
EXEC sp_addrolemember N'db_datareader', N'MyDomain\MyAccount'
GO
USE [MyDatabase]
GO
EXEC sp_addrolemember N'db_datawriter', N'MyDomain\MyAccount'
GO

这篇关于部署到IIS时，带有外部SQL Server DB的WCF /实体框架不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！