How to use SQL Web Service Endpoints for TSQL

Question

I'm really having a hard time finding information on how to use SQL Server Web Service (Endpoint for TSQL). I found some articles on how to create endpoints which seems to be simple enough. From my understanding, an SQL web Service listens on a specific port for TSQL (TSQL is the one that I want to try). I haven't been able to find how to call this endpoint with a query or a stored procedure...

Is there something I'm missing? Any article or path I can take to understand how this works would be greatly appreciated.

Any help is greatly appreciated! Thank you

• Aaron Bertrand: "Call the endpoint"? What exactly do you expect the endpoint to do in response to a query or stored procedure? Maybe you could describe the problem you're actually trying to solve, instead of asking how you can use an endpoint to solve it. My hunch is that there is a better solution.
• Hannah Vernon: As Aaron pointed out, using SQL Server as a web endpoint is not the best way to provide access to SQL Server data. Let IIS or some other http server host that service.
• JohnG: @Aaron Bertrand From what I have read, I was under the impression that like an Http Service the SQL WEB Service FOR TSQL (endpoint) would be a safe service that you send TSQL commands to and get the info back. The intent was to replace an APP tier by an SQL Web Service with Stored Procedures. But our main concern is Security since PCI compliance says that no DMZ should have access to an internal network.
• Aaron Bertrand: Where have you read that? I think if you want a data layer / middle tier, you're going to have to actually write a data layer / middle tier.
• JohnG: @Aaron Bertrand I assumed from my understanding of what I have read... Obviously I was totally off.

Answer 1

The MSDN documentation for CREATE ENDPOINT notes the following:

Native XML Web Services (SOAP/HTTP endpoints) was removed in SQL Server 2012.

So, unless you are using an unsupported version of SQL Server prior to SQL Server 2012, you cannot create an endpoint for http or xml.

If you are considering create a T-SQL endpoint, every SQL Server already has a T-SQL endpoint built in by default and there is absolutely no need to create another one. By default, the T-SQL endpoint listens on port 1433, although that is configurable.

• JohnG: The big picture is that we have an Application (over 20 year of code and close to 1 million lines of code) that accesses a local DB. Unfortunately the queries are in the code. we have recently created a web service to act as the application does... The problem is that they realized that with a DMZ that can't access the DB that's on the internal network, they were hoping that the SQL Web Service FOR TSQL can act as a local DB does with the Security aspect of it
• Hannah Vernon: When you say "accesses a local DB", are you saying it is hard-coded to connect to a SQL Server LocalDB database, or? Please clarify. Thanks!
• JohnG: it is set in an INI to connect to a DB, usually locally or on the same network. not LocalDB that you are referring to.