http://www.sqlservercentral.com/blogs/brian_kelley/2006/05/27/two-things-i-wish-theyd-change-about-endpoints-in-sql-server-2005/

Printed 2014/08/20 02:34PM

Two things I wish they'd change about endpoints in SQL Server 2005

2006/05/27

I've been playing around with endpoints in SQL Server 2005 looking at how they can be used to enhance security for SQL Servers servicing web servers based in the DMZ. Figure 1 shows a traditional architecture for just such a web server / database combination. The web server exists in the DMZ, outside of the trusted network. The SQL Server exists inside the trusted network and the appropriate ports are opened up in the firewall to allow the web server to connect back to the SQL Server. Best practices would say only open the necessary TCP port SQL Server listens on and no more. Since the port can be specified in the connection string or through the use of an alias, opening up UDP port 1434 isn't necessary for named instances.





Figure 1: Typical Architecture for web server in DMZ


In SQL Server 2005, the introduction of endpoints seems like a promising way to add another layer of security for this sort of architecture. I can create an endpoint which listens in on a specific port different from what is normally used to connect. For instance, the following code allows me to tell SQL Server to create an endpoint which listens on TCP port 19999:

 

CREATE ENDPOINT DMZ_SERVER

AS TCP (LISTENER_PORT=19999) FOR TSQL()

GO

 

SQL Server Configuration Manager also has to be touched to tell SQL Server to listen on this port. After that SQL Server has to be restarted and it'll begin to listen for TSQL commands on both the normal port SQL Server is configured (the endpoint corresponding to 'TSQL Default TCP') and port 19999. A reason I might want to do this is to be able to put an Intrusion Detection System or Intrusion Prevention System in-line between the web server and the SQL Server. By having the web server connect on port 19999, I can tell the IDS/IPS to watch traffic on this port. Everything else can be dropped by the IDS/IPS (and if my firewall is configured correctly, this is all I should see coming from the firewall). Figure 2 is a conceptual architecture with the IDS/IPS on the same network segment. This figure doesn't show the IDS/IPS in-line, but rather where it can monitor network traffic inbound to SQL Server.





Figure 2: Architecture with IDS / IPS listening on the network


Change #1

 

This sounds great. I've got the second TCP port, I can assist the IDS/IPS by giving it a filter for TCP port 19999 on the SQL Server and that gives me more security (or at least, more visibility) on the SQL Server. All is well, right? Well, not exactly. The CREATE ENDPOINT statement will return the following:

 

Creation of a TSQL endpoint will result in the revocation of any 'Public' connect permissions on the 'TSQL Default TCP' endpoint.  If 'Public' access is desired on this endpoint, reapply this permission using 'GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [public]'.

 

In other words, SQL Server 2005 creates the endpoint on TCP port 19999 for TSQL but when it does so it removes access to the default TCP port. I don't have any overrides on the CREATE ENDPOINT statement to prevent the permission change on the default TCP port. This isn't a big deal since in order to use the second TCP endpoint I'll have to restart SQL Server anyway, I don't like the fact that in order to define additional TCP ports means I'll have to execute the following command every time I create a new one:

 

GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [public]

GO

 

This leads me to the first thing I'd like to see changed:


Creating a new TSQL endpoint should not affect permissions on the TSQL default port. At the very least, I should have an override to prevent that from happening when creating a new endpoint.

 

Change #2:

 

One of the reasons I would love to have a second TSQL endpoint is I'd like to lock it down where if SQL Server sees a connection coming in from anyone in the sysadmin role on port 19999, it automatically rejects the connection. MySQL has a similar capability because I can tell MySQL which IP addresses a given user is allowed to connect from. For instance, I can tell MySQL that root (the equivalent to sa) can only connect from the local server, or 127.0.0.1 (localhost). If someone tries to connect with a client as root from any computer other than the local one, they'll get an error. This is a typical hardening step for MySQL installs. I'd like to get this kind of functionality for SQL Server.

 

Unfortunately, I can't specify within SQL Server what IP addresses user accounts can access the server from. The next best thing would be able to create a second TCP endpoint for TSQL and use SQL Server's ability to lock down what user accounts can connect on that endpoint. When coming from a server in the DMZ, I could then ensure that an attacker who managed to compromise the web server could not logon to the SQL Server from that web server using a sysadmin privileged account.

 

But there's a problem. SQL Server doesn't allow us to restrict access to sa. For instance, the following query looks like it should work:

 

DENY CONNECT ON ENDPOINT::DMZ_SERVER TO sa

GO

 

Executing it, however, returns the following error:

 

Cannot grant, deny, or revoke permissions to sa, dbo, information_schema, sys, or yourself.

 

Okay, let's try sysadmin. There are ways to do integrated security even with systems where a trust relationship doesn't exist. Microsoft KB articles like 247391, Authentication methods for connections to SQL Server in Active Server Pages, detail how to do so. If I configure my SQL Server to only perform Windows authentication, I eliminate any connections with sa, period. But I can't get around a login which is a member of the sysadmin fixed server role. Therefore, I'd execute the following query:

 

DENY CONNECT ON ENDPOINT::DMZ_SERVER TO sysadmin

GO

 

Only this doesn't work either. I get a different error from when trying to deny access to sa:

 

Msg 4617, Level 16, State 1, Line 1

Cannot grant, deny or revoke permissions to or from special roles.

 

In other words, a dead end. I can create the TSQL endpoint which will benefit my IDS/IPS, but I can't lock it down to where only the user account coming from the web server has access. I can ensure any non sysadmin level user can't use the endpoint, but I can't block sa and I can't block sysadmin. However, those are the very accounts I'd want to block.

 

That leads me to change #2:


Allow sa and sysadmin to be blocked on endpoints other than the Dedicated Admin Connection and the TSQL Default TCP connection.

Of the two changes, #2 is certainly the highest on my priority list. I can deal with having to grant connect back to public each time I create an TSQL endpoint as that would tend to be a rare occurrence. However, not being able to prevent sa or sysadmin from connecting to a given endpoint other than the default ones I think is something that definitely needs to change.


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.