February 3, 2011 at 11:40 am
Hello All,
I have been researching this for awhile and I must not be googling with the right terms as I am coming up with nothing. So even if some of you nice, caring folks out there don't know the answer to this, perhaps you could help steer me in the right direction with the terminology and technology so I can continue my search via the internet?
I have a rather simple need I think: to access a SQL database's stored procedures from a remote location via http or whatever protocol that is appropriate. The consuming app is actually an outdated VBA program that will need to be able to jump out and read this data via sending a paramater(s) to a stored procedure as well as update that table remotely, too, by calling a stored procedure using a parameter, too. So, the reading/writing of data is really just the same thing, I guess. I just need to be able to hit the DB/stored procedure with parameters and get back result sets. It needs to be secure, too, if possible.
As you can probably tell, I am a novice with anything more than a select statement in SQL.
I found this neat article (click here) that showed me how to create an HTTP Endpoint and then create a simple .NET web site to use a web reference to get to that DB out on the web. It worked, but it warned me: "Creating and altering SOAP endpoints will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use it." when I created the end point in SQL Server.
This sounds like something that I would want to shy away from if it is going to be outdated soon, right?
If so, what should I look into now? I don't really want to have to keep up with a .NET web service layer project that just runs as another tier in the mix if I don't have to. I am not good at that stuff and would have to learn it.
Thoughts? If you live in Nashville then I have a case of beer for ya for answering this one! 😀
February 3, 2011 at 12:27 pm
You can expose SQL Server to the web and access it that way, but it's a really, really, really bad idea from a security standpoint.
Is there any way for you to set up the application to connect via VPN?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 3, 2011 at 2:48 pm
Good question, I forgot to mention that: No VPNs allowed out from this particular network. It is "locked down" although they still allow internet access and other junk.
I agree that it sounds like a bad idea, but in this case it is the only way in which we can share a common DB amongst different computers. We also aren't allowed to have SQL running on the internal network. That is why we have a SQL instance purchased and running out in the "cloud" somewhere.
So, about 15 computers here internally need to go out and talk to a SQL database on the internet somewhere and get info back and update that same database, too, when necessary. These computers cannot have a SQL instance locally due to restrictions on the network. :doze:
February 3, 2011 at 2:53 pm
If you really need to do this, and I recommend against it, then all you have to do is expose the database server by setting it up to have an external IP address. Your connection provider would usually be the one to do that, in collaboration with your network/server people. Just as if it were a web server. Then you connect to that IP address from your application.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 3, 2011 at 3:06 pm
So there is no reliable way to expose JUST pieces to the outside world such as with a web service?
I guess I was under the impression that this could be done. That article I linked to above shows how to do it. Its a quick read if you just scan through it and look at the screen shots for what he is doing. I even have it almost working to some degree in that example although I will have to figure it out for VBA code as well as worry about MS discontinuing it one day...
There has to be a better way than just opening up the network and exposing the DB to the world like that, right? 🙂
February 3, 2011 at 3:12 pm
palpacino (2/3/2011)
So there is no reliable way to expose JUST pieces to the outside world such as with a web service?I guess I was under the impression that this could be done. That article I linked to above shows how to do it. Its a quick read if you just scan through it and look at the screen shots for what he is doing. I even have it almost working to some degree in that example although I will have to figure it out for VBA code as well as worry about MS discontinuing it one day...
There has to be a better way than just opening up the network and exposing the DB to the world like that, right? 🙂
Well, the SQL Server itself doesn't care. It's going to talk to whoever talks to its port on the server. The server is exposed, or not exposed, period.
Once someone can 'see' the server, you're now dealing with SQL Server's security, because the server opened the door to talk to it. You need to lock the system down tightly once you're exposed that way. When I say tightly, I mean Draconian measures.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 3, 2011 at 4:08 pm
I can handle the security in such a scenario as there are no complications in this who deal. It just needs to be able to fire off pre-defined (nothing dynamic) stored procedures and get data back.
So I can literally just tell it to let this user in and only let this user do these very few stored procedures. The data here is not mission critical. The only updating to the table via the stored procedures is to set flags in a column, so any bad guys would be very limited in the damage they could do.
So, security aside, you guys are saying simply open it up to the outside world and adjust the connection string appropriately to hit it with the client app?
I guess that isn't too much crazier than a web service when you get right down to it.... hmm...
Still seems like I would need to take a cold shower after doing such a thing, though.
February 3, 2011 at 4:19 pm
palpacino (2/3/2011)
So I can literally just tell it to let this user in and only let this user do these very few stored procedures. The data here is not mission critical. The only updating to the table via the stored procedures is to set flags in a column, so any bad guys would be very limited in the damage they could do.
Yeah, on that login...
Your problem isn't that login. You need to turn off sa. You need to lock out windows grouping except to the DBA staff that absolutely needs access. Cross database ownership needs to go. System tables need to be confirmed to be un-viewable via dynamic SQL.
It opens a pandora's box. The world, literally, can see your SQL Server... and script kiddy children are bored.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 3, 2011 at 5:18 pm
Well, crap. I can see where you are right about that.
I think I am going to go the way of web services as that article outlines. If they are going to be discontinued, then it will just have to be something that is discussed 5+ years from now.
Does anyone see any flaws in that approach as it is done in that article that I linked to? It allows an SSL connection to it which is very cool...
February 3, 2011 at 5:59 pm
My only comment on that article is to make sure your firewall CLOSES the SQL Server's ports. You still have to expose that server to internet access. Lock up your SQL server.
I don't know enough about firewalling to tell you how, or even if, you can lock the port out of the internet connectivity and still allow internal access to the server.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 4, 2011 at 7:29 am
Web services aren't being deprecated, just the SOAP connections directly to SQL Server. You can still set up web services, until something better comes along. Just do them the usual way, like .NET in IIS or whatever.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 4, 2011 at 2:23 pm
Also - keep in mind with this kind of pattern, when you say "nothing dynamic", you need to be strong about that. As in - your message from the outside world would need to contain no SQL code or parameters used in SQL code, just some label which tells you which statement to run (and the service internally has a list of statements to run).
In a setup such as this one - you can't really trust that anything sent in to you has not been tampered with "in transit"
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 4, 2011 at 2:43 pm
Our service provider has our SQL port locked down via the firewall with an exception for our office IP address. That might work for you if the connection will always come from the same location.
February 8, 2011 at 12:07 pm
OK, one last idea to kick around on this one.
Does anyone know of a technology out there that allows for a decentralized database akin to bittorrent but for any kind of real time data storage across multiple systems? That'd be a cool invention, eh?
February 8, 2011 at 12:11 pm
This is a GREAT idea. I am such a dummy for not thinking of this. In this scenario, the connections do, indeed, come from one location/ip address.
Awesome, awesome, awesome.
And I can make the stored procedures "clean up" the input parameters so we can make sure that they were not tampered with in transit.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply