locked
Can we connect to sqlserver with out using BCS in sharepoint 2010? RRS feed

  • Question

  • Hi Friends,

           I have a scenario where i need to create SharePoint Application Page and pull some data from SQL Server and display in grid. But i do not want to use Business Connectivity services due to performance.

          On SharePoint side we are using Windows Authentication.

          On SQL Server Side we are using Windows Authentication.

           Please help me in solving this issue.

           Thanks in advance for your help.


            


    • Edited by Tech-Fun Monday, July 14, 2014 1:42 PM Correction in question.
    Monday, July 14, 2014 12:26 PM

Answers

  • You could add an AD security group for the users to SQL. That's a much less painful way of managing that access.
    • Marked as answer by Victoria Xia Monday, August 11, 2014 1:35 AM
    Monday, July 14, 2014 2:59 PM

All replies

  • You will either need to use BCS or a custom solution that includes the tooling to directly connect to SQL.
    Monday, July 14, 2014 12:37 PM
  • Thanks for the response. can you please elaborate custom solution meaning approach or point me to some resource links if possible?

    Small correction in my above question. we are using windows authentication in SQL Server Side.

    Thanks in advance for your help.
    • Edited by Tech-Fun Monday, July 14, 2014 1:48 PM
    Monday, July 14, 2014 1:42 PM
  • Hello,

    you can connect to your sql with an sqlconnection in c# (use this on your component, webpart or other)

    sample here : http://www.mattknott.com/content/blog/2008/05/HOW_TO:_Write_an_SQL_Webpart.html

    ps : here some recommendation for storing connection string :http://blogs.technet.com/b/msjimblog/archive/2012/07/31/where-to-store-your-sql-connection-strings-in-sharepoint.aspx


    Best regards, Christopher.
    Blog | Mail
    Please remember to click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if it was useful.
    Why mark as answer?

    Monday, July 14, 2014 2:17 PM
    Answerer
  • Hi Chris,

    I really appreciate your response. It seems like the sample is using SQL Authentication.

    we are use windows authentication in SQL Server and SharePoint.

    We need to get data from sql server with out using Elevated privileges due to performance impact and at the same time i dont want to create all sharepoint users in SQL Server.

    Is there any way or work around for above scenario.

    Thanks for the your help.


    • Edited by Tech-Fun Monday, July 14, 2014 2:44 PM
    Monday, July 14, 2014 2:44 PM
  • You could add an AD security group for the users to SQL. That's a much less painful way of managing that access.
    • Marked as answer by Victoria Xia Monday, August 11, 2014 1:35 AM
    Monday, July 14, 2014 2:59 PM