Setting up SQL Server Endpoint

Question

guestMS on Thu, 27 Jun 2013 15:50:29


Hi all,

I've a named instance configured to listen on static TCP ports: 5001, 5002

I've created an endpoint in order to identify and monitor access to SQL Server between 2 SQL logins ("Test" and "Test2"): 

CREATE ENDPOINT [TestConnection] 
STATE 
STARTED 
AS TCP 
   
(LISTENER_PORT 5001LISTENER_IP=ALL) 
FOR TSQL() ;

GRANT CONNECT ON ENDPOINT::[TestConnectionTO [Test];

Though login "Test" can connect (using port 5001), login "Test2", who was able to connect before, can't anymore since the endpoint was setup ! If I delete the endpoint everything goes fine again.

PS: I'm using SQL Server 2008 R2, Firewall is off and SQL Browser is started and TCP protocol enabled.

Any idea of what is going on wrong ?

Thanks.





Replies

Rick Byham, Microsoft on Thu, 27 Jun 2013 16:02:27


From Books Online topic Configure the Database Engine to Listen on Multiple TCP Ports http://msdn.microsoft.com/en-us/library/ms189310.aspx

When you create a new Transact-SQL endpoint, connect permissions for public are revoked for the default TDS endpoint. If access to the public group is needed for the default endpoint, reapply this permission by using the GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [public]; statement.

 

guestMS on Thu, 27 Jun 2013 16:27:39


I forgot to mention I tried it already but to no avail. I also checked the PUBLIC role has indeed the connect permission in the securables in SSMS (which was the case). Also the default endpoint is started.

The only way to make it work is to specify the port number when connecting using login "Test2" which is precisely what I want to avoid...


Rick Byham, Microsoft on Thu, 27 Jun 2013 20:28:56


An incoming client connection that does not know the TCP port of the SQL Server typically uses UDP port 1434 to ask the SQL Server Browser service what TCP port is the server listening on. So first, make sure that the SQL Server Browser service is running on the SQL Server computer. And make sure that the firewall isn't blocking UDP port 1434 traffic. UDP is not a routed protocol. (The D in UDP stands for Datagram which is a broadcast to all clients on that subnet.)

I don't remember what happens if SQL Server is listening on two ports. But I can easily imagine that the wrong port might be handed out to an incoming client. When you start using your own ports like that, I think the assumption is that your client will specify the port they want to use. That is one of the reasons people setup that kind of port. (Though restricting certain ports to certain logins is another reason.)

Erland Sommarskog on Thu, 27 Jun 2013 22:03:44


So what is the exact error message you see in the SQL Server errorlog? Something about "infrastructure error"?

If I understand this correctly, you want "test" to come in through port 5001 and "test2" through 5002, and you hope to achieve this with permissions without the users specifying the port numbers. But it's not that the API tries all port numbers in a round-robin fashion , but it tries a single port number. Either one you specify explicitly, or it gets through the Browser service.

To achieve something like this, you would need to push out server aliases through GPO.

guestMS on Fri, 28 Jun 2013 06:16:31


As stated in my previous post, firewall is OFF on the server, and the SQL Server Browser is started and functional.

Indeed, seems like the wrong port is given by the Browser or more likely returns always the first one (e.g. 5001) securized by the endpoint through which "Test2" is not allowed.

Actually, "Test" is used by local applications in the LAN whereas "Test2" is used by applications in a DMZ, both local and remote applications need to connect to the same SQL Server.

Since an SQL Server connection is issued from the DMZ (I don't like it but there is no other way around), I would like to be able to monitor traffic on port 5001 and be able to block it in case of an attack without impacting connections made inside the LAN.

The problem is the moment I am setting up the endpoint, connections issued form the DMZ work but all others made inside the LAN using "Test2" login failed with the error message "Login failed for user 'Test2'. Reason: Login-based server access validation failed with an infrastructure error.".


guestMS on Fri, 28 Jun 2013 06:23:45


The exact message is "Login failed for user 'Test2'. Reason: Login-based server access validation failed with an infrastructure error."

Indeed, is exactly what I'm trying to achieve.

I came to that conclusion, the Browser is sending me back always the same port (e.g. the first one or more likely the lowest one) which is 5001 through which "Test2" could not connect.

I could play on port number : setting a lower one for LAN connection and a greater for DMZ connection. In which case "Test2" is able to connect but this is more like a workaround than a real solution.

I like your idea of server aliases to go into more details it would be an alias for accessing port 5001 and another one for port 5002 ?



Erland Sommarskog on Fri, 28 Jun 2013 21:10:29


I like your idea of server aliases to go into more details it would be an alias for accessing port 5001 and another one for port 5002 ?

Well, the aliases could be the same, but the definitions would be different depending on where the client is.

You said that you did not want to use the port numbers explicitly, so I guess that you want the aliases to be the same, so that you can use the same connection strings in both places.

This is not really my area - I'm more a developer than an admin - but many of my MVP mates seems to use DNS aliases.