Limiting External Access to AWS SQL Server Instance

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

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/

Microsoft SQL Server on Amazon RDS, Amazon RDS does not allow direct host access to a DB instance via Telnet, Secure Shell (SSH), or Windows Remote Desktop Connection. When you create a� The first step is to create a new Windows server. Most of the instance launch process is pretty basic, however, we want to create a new security group which I have named “RDP Jump Box” and allows RDP from anywhere. Once this server is up and running, we can change the IIS security group to restrict RDP access from only the jump box.

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)

Controlling Access with Security Groups, server running in an Amazon EC2 instance in the same VPC, which is accessed by a client application outside the VPC. For this scenario, you use the RDS� An unlimited number of end users can access SQL Server on a license included instance. Customers bringing their own SQL Server licenses to Amazon EC2 through license mobility or bring your own licenses (BYOL), will continue to follow the licensing rules they have in place on-premises.

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.

Scenarios for Accessing a DB Instance in a VPC, Microsoft SQL Server. Connecting to a DB Instance Running the Microsoft SQL Server Database Engine. MySQL. Connecting to a DB Instance Running the� By default, only the built-in local administrator account can access a SQL Server instance launched from an Amazon Web Services (AWS) Windows AMI. You can use SQL Server Management Studio (SSMS) to add domain users so that they can access and manage SQL Server.

Identity and Access Management in Amazon RDS, policies to manage access to Amazon RDS. To view example Amazon RDS identity-based policies that you can use in IAM, see Amazon RDS Identity-Based � Connecting to Your DB Instance with Microsoft SQL Server Management Studio. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/ . In the upper-right corner of the Amazon RDS console, choose the AWS Region of your DB instance. Find the Domain

Using Windows Authentication with an Amazon RDS for SQL Server , your SQL Server DB instance) uses SQL Authentication. Because the master user account is a privileged credential, you should restrict access to this account. Login to AWS -> Go to EC2 Dashboard -> Click “Running Instances” -> Select the EC2 instance -> Click “Connect” -> Click “Download Remote Desktop File” and save the file -> Click “Get Password” -> Select your keypair file -> Click “Decrypt Password” -> Copy and kept the password. 2. Logon to EC2 and Install SQL Server Express.

Security in Amazon RDS, Security in Amazon RDS � Run your DB instance in a virtual private cloud (VPC) based on the Amazon VPC service for the greatest possible network access control� The rule of thumb for the number of tempdb data files a SQL Server instance should have is one for each CPU core or virtual processor the instance as access. This means that for a EC2 Windows server that has two virtual processors, there should be two tempdb data files available for SQL Server to use.

Comments
  • 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.
  • @DougStevenson can you re-open this? I’m aware of the IP limitation; I’m looking for another solution. Cheers.
  • 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 thanks for re-opening and for the suggestions; I have edited to make use of your advice. Cheers.
  • 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"?
  • I will have to look into all this. To be honest it seems like a lot. Is my question that uncommon a use-case?
  • 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
  • @1252748 Do you need any help with the solution?
  • 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
  • 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.
  • 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.
  • @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?