1

I'd like to get some data from my SQL database. But the problem is I can't use the port number in a connection string like I have to avoid it normally due to a firewall. Currently, my SQL server is like a black box that only my sharepoint Server can query. due to IT security, only sharepoint servers are allowed to submit queries to my DB.(Like Stored procedure)

I think I have to create a web service which collects the data from my DB and return it, but how could I do that on Visual Studio?

I'm totally lost, after some searches, I think I have to do a WCF. So if I understand well, I have to use my sharepoint server to hosted my WCF and after that my server could request my Database and send the information to my client (C# application). Is it right? So if someone could have some tips to help me, It would be appreciated.

2
  • 2
    Are you aware of BCS in SharePoint? Mar 26, 2018 at 15:13
  • No, I'll do some searches about that
    – freedumz
    Mar 26, 2018 at 20:21

1 Answer 1

1

Create a Secure Store Target App, Central Administrations> Application Management > manage service Application> Secure store service, if it doesn't exist, create the Service Application.

on the service application create the new service store target application, (recomended to use group instead of individual)

on the secure store service set the credentials that has the access to the sql server.

Open SharePoint designer, go to external content types, create a new external content type, Name it, on the connection select your database and for credentials use the Secure Store Service App ID,

select your desired table create the actions you need for your connection (create, read, update, delete) save changes

go to your sharepoint site > Site contents > add an app > external list

select the external content type you created

Boom! , List connected to your database

to change permissions of the Data: go to Central Administration > Application Management> manage service applications > Business connectivity services >

select your external content type, click set object permissions, add the users that need the access and the permission level required

the access to the list is managed from the external list >list settings>permissions.

3
  • Thank for your completed answer but with this way could I execute some stored procedure?
    – freedumz
    Mar 26, 2018 at 20:20
  • Yes, but you need to implement several different functions (Like finder, specific finder, id enumerator etc) Mar 27, 2018 at 0:24
  • if its possible i would recommend triggers instead of store procedures. For more programmed communication to the database i would suggest writing a custom webpart, and write all your required code, in he webpart you can do "impersonation, this way anyone can execute those pieces of code
    – Mike
    Mar 27, 2018 at 14:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.