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.