locked
passing app pool credentials to sql server RRS feed

  • Question

  • Hello,

    I have a web service in which i am making connection to sql server to get data..This web service is placed in the layouts folder...now i want that the app pool account should be used to make the connection and not the current user's credentials..can somebody tell me step by step what changes do i need to make like setting  web.config change etc...If i set impersonation=false in the web.config then sharepoint site stops  and also  i cannot enable anonymous access...i am really confused how to set it up so if somebody can guide me step by step I would really appreciate it.
    I am using moss 2007 ,.net 3.5,iis 7.0 and sql server 2008.



    Thanks
    Tuesday, June 15, 2010 6:39 PM

Answers

  • Is your web method getting called at all from the web page? If so, try the code below. If the connection opens you are in the clear. The Data Source should have the form 'serverName\instanceName'. The AppPool account should have db_owner permissions to database "test1".

     

    SPSecurity.RunWithElevatedPrivileges(delegate() {
      using (SqlConnection connection = new SqlConnection("Data Source=test;Initial Catalog=test1;integrated security=sspi"))
        {
          connection.Open();
          //
        }
     
    });
    

     

    Wednesday, June 16, 2010 2:00 AM

All replies

  • Hello,

    I don't want anonymous access..and is this run with elevated priviledges runs the code using the app pool account ?

     

    Thanks

    Tuesday, June 15, 2010 10:28 PM
  • Yes, RunWithElevatedPrivileges elevates the security context to the AppPool account of that web application. Make sure you 'new' the site and the web inside the RunWithElevatedPrivileges delegate:

    SPSite site = SPContext.Current.Site;
    SPWeb web = SPContext.Current.Web;
    SPSecurity.RunWithElevatedPrivileges(delegate() {
     using (SPSite elevatedSite = new SPSite(site.ID))
     using (SPWeb elevatedWeb = elevatedSite.OpenWeb(web.ID))
       {
         //your code here
       }
    });
    
    
    Tuesday, June 15, 2010 11:49 PM
  • Hello Dan,

    I have a web service which i have placed in layouts folder..this web service makes a connection to sql server and gets the data..below is the code of the web service..i don't know that much of MOSS API ..can you tell me where should i place the above code ...so that whenever this web service is called it runs with ..i tried copying ur code but it gives error when i pasted it ..i also did add a reference to microsoft.sharepoint...if you can merge your code with the web service then it would really be of great help.

     

     public class Connection1 : System.Web.Services.WebService

    {

    [WebMethod]
            public DataSet employees()
            {
                SqlConnection conn = new SqlConnection("Data Source=test;Initial Catalog=test1;integrated security=sspi");
                SqlDataAdapter da = new SqlDataAdapter("select distinct employees from Employees1 ", conn);
                DataSet ds = new DataSet();
                connOpen();
                da.Fill(ds, "test1 ");
                conn.Close();
                conn = null;
                da = null;
                return ds;

            }

    }

     

    Thanks

    Wednesday, June 16, 2010 1:01 AM
  • Is your web method getting called at all from the web page? If so, try the code below. If the connection opens you are in the clear. The Data Source should have the form 'serverName\instanceName'. The AppPool account should have db_owner permissions to database "test1".

     

    SPSecurity.RunWithElevatedPrivileges(delegate() {
      using (SqlConnection connection = new SqlConnection("Data Source=test;Initial Catalog=test1;integrated security=sspi"))
        {
          connection.Open();
          //
        }
     
    });
    

     

    Wednesday, June 16, 2010 2:00 AM
  • Hello Dan,

    Thank you so much for the code..really helped me..just have a quick question...the sql server  and moss are on different boxes...so now when  a client accesse the web service to moss then will moss pass the credentials of the app pool account  to sql server if kerberos is not enabled..

     

    Thanks

    Wednesday, June 16, 2010 1:20 PM