Login failed when a web service tries to communicate with SharePoint 2007

Question

I created a very simple webservice in ASP.NET 2.0 to query a list in SharePoint 2007 like this:

namespace WebService1
{
    /// <summary>
    /// Summary description for Service1
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class Service1 : System.Web.Services.WebService
    {
        [WebMethod]
        public string HelloWorld()
        {
            return "Hello World";
        }

        [WebMethod]
        public string ShowSPMyList()
        {
            string username = this.User.Identity.Name;
            return GetList();
        }

        private string GetList()
        {
            string resutl = "";
            SPSite siteCollection = new SPSite("http://localhost:89");
            using (SPWeb web = siteCollection.OpenWeb())
            {
                SPList mylist = web.Lists["MySPList"];
                SPQuery query = new SPQuery();
                query.Query = "<Where><Eq><FieldRef Name=\"AssignedTo\"/><Value Type=\"Text\">Ramprasad</Value></Eq></Where>";
                SPListItemCollection items = mylist.GetItems(query);
                foreach (SPListItem item in items)
                {
                    resutl = resutl + SPEncode.HtmlEncode(item["Title"].ToString());
                }
            }
            return resutl;
        }
    }
}

This web service runs well when tested using the built-in server of Visual Studio 2008. The username indicates exactly my domain account (domain\myusername).

However when I create a virtual folder to host and launch this web service (still located in the same machine with SP2007), I got the following error when invoking ShowSPMyList() method, at the line to execute OpenWeb(). These are the details of the error:

System.Data.SqlClient.SqlException: Cannot open database "WSS_Content_8887ac57951146a290ca134778ddc3f8" requested by the login. The login failed. Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.

Does anyone have any idea why this error happens? Why does the web service run fine inside Visual Studio 2008, but not when running stand-alone? I checked and in both cases, the username variable has the same value (domain\myusername).

Thank you very much.

---

Thank you very much for the replies. I'll look into the documents to see how i can change the settings related to the application pool as suggested.

I want to make clear that i wanted to build a webservice to run outside of sharepoint (but can be deployed on the same server with sharepoint).

Is there any way i can programmatically pass the credentials (another domain account instead of 'NT AUTHORITY\NETWORK SERVICE' by default) to sharepoint when invoking OpenWeb method? I believe if i'm able to do that then i can walkaround the security issue above.

Answer 1

When you create your own custom virtual folder and set it inside the IIS, it's highly possible that the user account who run the application pool of that particular IIS virtual directory is currently set to NT authority\Network Service.

You can check carefully, by looking closely of what is the actual application pool that run that particular IIS virtual directory.

From there, you can go to the "Application Pool" folder and right click, choose Properties. Select the "Identity" tab, and it will show you who is the user account that currently running the application pool.

Alternatively, you can refer to the SharePoint SDK, something similar to ExtractCrmAuthenticationToken in dynamics CRM to extract the Authentication Token ticket.

Or alternatively you can use Network Credential to embed your own custom user id and password.

Hope this helps,

hadi teo

Answer 2

I fully agree with Hadi, if this is something you want to just quickly test, for a proof of concept, you can change the credentials under what the Application pool runs, to a user that has permissions. Or you could use Identity Impersonate setting in your config file.

However resist the temptiation to do this in a production enviroment, use the proper authentication. It will come back, to bite you.

If you need to set this up for production, there is a couple of areas that you want to look at, duplicate SPN's, and deligation probably the most common areas that is not configured correctly. Your error however points to impersanation not happening.

Answer 3

Also make sure you are deploying the web service to its own web site that does not already run SharePoint. If you want the web service to run on the same web site as SharePoint read Creating a Custom Web Service.

You can check what application pool identity SharePoint is using by following the same instructions that Hadi writes, but for an app pool running SharePoint. Make sure to only change the application pool used by your web service and not SharePoint or else other permission errors could occur within SP. (There should be no reason but if you are interested in changing the app pool identity used by SharePoint follow these instructions.)

Answer 4

On solution would be to "impersonate" as the SharePoint System account using the following code:

SPSecurity.RunWithElevatedPrivileges(delegate()
{
    // also dispose SPSite
    using (SPSite siteCollection = new SPSite("http://localhost:89")) 
    {
        using (SPWeb web = siteCollection.OpenWeb())
        {
            // ...
        }
    }
});