locked
Unable to connect to database from custom webpart using "Integrated Security = true" RRS feed

  • Question

  • Hi,

    I need to retrieve data from sql server database from my custom webpart.
    While using sql server authentication, the webpart is able to retrieve data.
    "Data Source=servername;Initial Catalog=dbname;user id=testname;password=testpwd;"

    But when using windows authentication, it is giving login failed error.
    >>>Cannot open database "dbname" requested by the login. The login failed. Login failed for user 'DOMAIN\.username.'.

    Following is the connection string used.
    "Data Source=servername;Initial Catalog=dbname;Integrated Security = SSPI;"

    Both sharepoint server and the database that I am trying to access are on the same server.
    If I add the logged in user to the database and give access rights, windows authentication works fine. But it is not possible to explicitly give all the users access to the database.
    In the web.config of the sharepoint site, <identity impersonate="true"> is given.

    What is it that I should do to make windows authentication work?

    When I use a username explicitely which has access to database, windows authentication is working
    ie  <identity impersonate="true" username="userHasDBAccess" password="testpwd">
    Also,
    The identity username used by the app pool does have access to the database.

    What is it that I am missing?
    Could somebody help?
    My objective is to use a connection string like "Data Source=servername;Initial Catalog=dbname;Integrated Security = SSPI;"

    Thanks
    Monday, June 15, 2009 12:45 PM

Answers

  • SPSecurity.RunWithElevatedPrivileges(delegate()
                    {
    
    //code to run under pool identity
    
    });
    • Marked as answer by spc100 Wednesday, June 17, 2009 4:42 AM
    Monday, June 15, 2009 1:39 PM

All replies

  • SPSecurity.RunWithElevatedPrivileges(delegate()
                    {
    
    //code to run under pool identity
    
    });
    • Marked as answer by spc100 Wednesday, June 17, 2009 4:42 AM
    Monday, June 15, 2009 1:39 PM
  • If you are conform your users having access to the database, and you just don't want to give permissions one by one on the SQL server, then you can use an AD group to assign permission on the SQL database, and drop users to the group as requested. This aproach can be OK for your single server environment but won't work without implementing Kerberos when the SQL and SharePoint servers are on different machines (typical on a well-scaleble and fault tolerand architecture).

    If you would like to go with a dedicated user, then you can apply SPSecurity.RunWithElevatedPrivileges as Oleg suggested, use SSO (if you have MOSS), or store the user credentials of the technical user in an encrypted section in your web.config.

    Peter

    Monday, June 15, 2009 1:45 PM
  • Thank you oleg_ab.
    Wednesday, June 17, 2009 4:44 AM
  • Thanks Peter for your suggestions
    Wednesday, June 17, 2009 4:45 AM