6

I have a SQL Server instance on AWS that I have opened to external access by altering my security group to allow access from “Everywhere".

+-------------+----------+------------+--------------------------+
|    Type     | Protocol | Port Range |         Source           |   
+-------------+----------+------------+--------------------------+
|    MSSQL    | TCP      |    1433    | Custom  0.0.0.0/0        |
|    MSSQL    | TCP      |    1433    | Custom  ::/0             | †
+-------------+----------+------------+--------------------------+

I would like to restrict this access to this database, though not via IP addresses since the service I will use to access it has no static IP.

How can I tighten inbound access to this database for use with an external service (eg Firebase function or NodeJS application)?

† AWS security group rule that is generated when "Everywhere" and "MSSQL" are selected in the Security Group inbound rules section

1252748
  • 14,597
  • 32
  • 109
  • 229
  • Cloud Functions don't have dedicated external IP ranges. You should look into using addition encryption or passing some shared secret between the code that authenticates them with each other. – Doug Stevenson Dec 25 '19 at 01:21
  • @DougStevenson can you re-open this? I’m aware of the IP limitation; I’m looking for another solution. Cheers. – 1252748 Dec 25 '19 at 01:41
  • 2
    I suggest rephrasing the question to say nothing about Cloud Functions or IP addresses, since the problem at hand for you is how to control access to **only authorized clients**. What you want is a way to perform that authorization, regardless of its point of origin. – Doug Stevenson Dec 25 '19 at 01:44
  • @Doug Stevenson thanks for re-opening and for the suggestions; I have edited to make use of your advice. Cheers. – 1252748 Dec 25 '19 at 19:29
  • i am puzzled by the question. Doesn't the service you use to access the database need to provide valid credentials to complete the connection? Provided you keep these connection credentials secure no-one else can connect. To what extent do you want to "tighten inbound access"? – JohnRC Jan 01 '20 at 18:00
  • @JohnRC the intent is to reduce the surface of the deployment. – 1252748 Jan 01 '20 at 22:36

3 Answers3

8

AFAIK, there is no direct way to achieve this without knowing the static IP or the IP range from where you need to access your EC2 instance (Where you host your SQL Server).

But...

You can include your instance behind an API Gateway and then enable IAM authentication for the API method in the API Gateway. Then use IAM policies (along with resource policies) to designate permissions for your API's users.

More: https://aws.amazon.com/premiumsupport/knowledge-center/iam-authentication-api-gateway/

Mouneer
  • 12,827
  • 2
  • 35
  • 45
  • I will have to look into all this. To be honest it seems like a lot. Is my question that uncommon a use-case? – 1252748 Dec 29 '19 at 19:59
  • 1
    No, it's a very common use-case but it seems that AWS team decided to support it through the API Gateway as in most cases apps are run under an API Gateway. @1252748 – Mouneer Dec 29 '19 at 21:33
  • @1252748 Do you need any help with the solution? – Mouneer Dec 31 '19 at 09:37
  • i don't understand how this is possible, A client application(for e.g nodejs) that want to connect to mssql via port 1433. how can you proxy that request through api gatway. how can the client application access the database using the database driver and also access the db through api gateway, can you please help me understand – Arun Kamalanathan Jan 02 '20 at 11:08
  • No, and thank you for the answer, but I think the best solution is moving my data to GCP. Which is also being troublesome, but that is another matter. Cheers. – 1252748 Jan 02 '20 at 15:07
  • @ArunK , API Gateway now supports endpoint ports 80, 443 and [1024, 65535]. So, just append the custom port to the Endpoint URL. e.i, http://x.y.z.x:1433 . My respect :) – Mouneer Jan 03 '20 at 21:27
3

There is no direct way to restrict.

AWS Cloud - solution (all services / instance in AWS)

If your NodeJS application is running on AWS or you are using AWS Lambda service you can allow access across security groups alone within same VPC. (If multiple accounts used - VPC peering can be done)

Santhosh S
  • 782
  • 5
  • 17
2

For your scenario where you would like to restrict access to a publicly accessible database, like everyone else said, I couldn't think of a way other than the plain old VPN solution. You client connects to the database through the VPN and move the database instance to private subnets. But i am not sure how feasible it is for you to implement it.

Arun Kamalanathan
  • 8,107
  • 4
  • 23
  • 39
  • I think you missed the point at the question. The question is asking about accessing the DB from an external service which for sure cannot be added to the VPC as it is not an AWS resource. So your answer cannot be valid in this situation. – Mouneer Jan 03 '20 at 21:17
  • @1252748 glad to hear that you found a way forward, just for my knowledge, can you clarify how google cloud functions may solve the problem? – Arun Kamalanathan Jan 03 '20 at 21:27