Open MSSQL to the internet or implement a web service on the SQL server

I am moving forward with my Cashless Catering system, and am now planning the Sharepoint WebPart for it.

Now, the problem I have is this - creating one which is installed on a local sharepoint install, within the local network is easy. There's no issues there. However, our sharepoint install isn't local. It is provided by our LEA and as such has its own set of usernames and passwords etc...

This means I have to maintain a link within my software between the LEA provided account names and our parent database. Relatively easy to do, yes.

The big question comes to accessing the data on our network. As far as I can see it, I have a couple of choices.

1. Use a web service - which can be limited to exactly what I want to expose. Disadvantage is adding an extra layer of complexity, and therefore an extra thing to go wrong/maintain.
2. Open the MSSQL server to the internet, and using a limited user account to restrict what that user can do. Disadvantage - potentially easier to 'hack' the server, as it is a direct link to MSSQL rather than via IIS.
3. Hire teams of racing pigeons to carry the data...

Ideas?

Bump...

I'm surprised you have to ask - a web service is nearly always safer, because it's controllable and it can keep detailed logs.

Best practise would be to implement this as a web service - the added layer has a couple of benefits which I think out-weigh the disadvantage of extra maintenance. Web service:

1. ensures that data is always in provided in the correct format, regardless of DB changes behind the scenes.
2. can easily be manipulated and interrogated at a later date by other software (also ties in with #1)
3. security - goes without saying that this is more secure than opening up SQL to outside, even with IP and user-based access in place

The web service layer also allows you to do anything you want - cache, log, format, and add custom security where appropriate.

Quote:

---

Originally Posted by localzuk

This means I have to maintain a link within my software between the LEA provided account names and our parent database.

---

Is there no approved method of getting two Sharepoint installs to synch usernames/passwords? I thought that was kind of the point of using Sharepoint - it would deal with all the tedious backend stuff for you?

My preference would be for a web service, but I don't really know the exact situation you're trying to sort out so that's just my completly un-informed opinion.

--
David Hicks

Quote:

---

Originally Posted by dhicks

Is there no approved method of getting two Sharepoint installs to synch usernames/passwords? I thought that was kind of the point of using Sharepoint - it would deal with all the tedious backend stuff for you?

---

The system I have has parents stored in it with SIMS external ID's only as their reference - the issue is linking them to the sharepoint install, not working between 2 sharepoint installs.

Also, I think I may end up biting the bullet and building a web service. They're not hugely complex to build, and as people have said, they have some advantages.

I would try to keep it as simple as possible - SOAP with WSDL can get mega-complex. Stick with the most native REST/JSON/XML implementation that is available to both platforms/systems.

Quote:

---

Originally Posted by webman

I would try to keep it as simple as possible - SOAP with WSDL can get mega-complex. Stick with the most native REST/JSON/XML implementation that is available to both platforms/systems.

---

I'm building everything in C# so, creating a web service is simple - declare structs, declare methods, declare exception handlers and publish.

Ah, easier than I anticipated. Sounds like you have your solution sorted then :)