Unrestricted MsSQL Access

01 Sign in to the AWS Management Console.

02 Navigate to Amazon EC2 console at https://console.aws.amazon.com/ec2/.

03 In the navigation panel, under Network & Security, choose Security Groups.

04 Click inside the Filter security groups box located under the console top menu and select the following options from the Properties dropdown menu:

1. Choose Protocol and select TCP from the protocols list.
2. Choose Port range and select MSSQL from the port ranges list.

05 Select the security group that you want to examine and choose the Inbound rules tab from the console bottom panel to access the inbound rules created for the selected group.

06 Check the configuration value available in the Source column for any inbound/ingress rules with the Port range set to 1433. If one or more rules have the Source value set to 0.0.0.0/0 or ::/0(i.e. Anywhere), the selected Amazon EC2 security group allows unrestricted traffic on TCP port 1433, therefore the Microsoft SQL Server (MSSQL) access to the associated EC2 instance(s) is not secured.

07 Repeat steps no. 5 and 6 for each EC2 security group returned as result at step no. 4.

08 Change the AWS cloud region from the navigation bar and repeat the audit process for other regions.

01 Run describe-security-groups command (OSX/Linux/UNIX) with predefined and custom query filters to expose the ID of each Amazon EC2 security group that allows unrestricted inbound access on TCP port 1433 (MSSQL):

aws ec2 describe-security-groups
  --region us-east-1
  --filters Name=ip-permission.from-port,Values=1433 Name=ip-permission.to-port,Values=1433 Name=ip-permission.cidr,Values='0.0.0.0/0'
  --output table
  --query 'SecurityGroups[*].GroupId'

02 The command output should return a table with the requested security group ID(s):

--------------------------
| DescribeSecurityGroups |
+------------------------+
|  sg-01234abcd1234abcd  |
|  sg-0abcd1234abcd1234  |
+------------------------+

03 Change the AWS cloud region by updating the --region command parameter value and repeat steps no. 1 and 2 to perform the audit process for other regions.

01 CloudFormation template (JSON):

{
	"AWSTemplateFormatVersion": "2010-09-09",
	"Description": "Configure security group to restrict inbound MSSQL database access to trusted entities only",
	"Resources": {
		"EC2SecurityGroup": {
			"Type": "AWS::EC2::SecurityGroup",
			"Properties": {
				"GroupName": "cc-instance-security-group",
				"GroupDescription": "Allow MSSQL database access",
				"VpcId": "vpc-01234abcd1234abcd",
				"SecurityGroupIngress": [
					{
						"Description": "Allow inbound MSSQL database traffic",
						"IpProtocol": "tcp",
						"FromPort": 1433,
						"ToPort": 1433,
						"CidrIp": "0.0.0.0/0"
						"CidrIp": "10.0.0.30/32"
					}
				],
				"SecurityGroupEgress": [
					{
						"Description": "Allow all outbound traffic",
						"IpProtocol": "-1",
						"FromPort": 0,
						"ToPort": 65535,
						"CidrIp": "0.0.0.0/0"
					}
				]
			}
		}
	}
}

02 CloudFormation template (YAML):

AWSTemplateFormatVersion: '2010-09-09'
	Description: Configure security group to restrict inbound MSSQL database access to trusted entities only
	Resources:
	EC2SecurityGroup:
		Type: AWS::EC2::SecurityGroup
		Properties:
		GroupName: cc-instance-security-group
		GroupDescription: Allow MSSQL database access
		VpcId: vpc-01234abcd1234abcd
		SecurityGroupIngress:
			- Description: Allow inbound MSSQL database traffic
			IpProtocol: tcp
			FromPort: 1433
			ToPort: 1433
			CidrIp: '0.0.0.0/0'
			CidrIp: '10.0.0.30/32'
		SecurityGroupEgress:
			- Description: Allow all outbound traffic
			IpProtocol: '-1'
			FromPort: 0
			ToPort: 65535
			CidrIp: '0.0.0.0/0'

01 Terraform configuration file (.tf):

terraform {
	required_providers {
		aws = {
			source  = "hashicorp/aws"
			version = "~> 4.0"
		}
	}
	required_version = ">= 0.14.9"
}
provider "aws" {
	profile = "default"
	region  = "us-east-1"
}
resource "aws_security_group" "ec2-security-group" {
	name        = "cc-instance-security-group"
	description = "Allow MSSQL database access"
	vpc_id      = "vpc-01234abcd1234abcd"
	ingress {
		description      = "Allow inbound MSSQL database traffic"
		from_port        = 1433
		to_port          = 1433
		protocol         = "tcp"
		cidr_blocks      = ["0.0.0.0/0"]
		cidr_blocks      = ["10.0.0.30/32"]
	}
	egress {
		description      = "Allow all outbound traffic"
		from_port        = 0
		to_port          = 0
		protocol         = "-1"
		cidr_blocks      = ["0.0.0.0/0"]
	}
}

01 Sign in to the AWS Management Console.

02 Navigate to Amazon EC2 console at https://console.aws.amazon.com/ec2.

03 In the navigation panel, under Network & Security, choose Security Groups.

04 Select the Amazon EC2 security group that you want to reconfigure (see Audit section part I to identify the right resource).

05 Select the Inbound rules tab from the console bottom panel and choose Edit inbound rules.

06 On the Edit inbound rules configuration page, change the traffic source for the inbound rule that allows unrestricted access through TCP port 1433, by performing one of the following actions:

1. Select My IP from the Source dropdown list to allow inbound traffic only from your current IP address.
2. Select Custom from the Source dropdown list and enter one of the following options based on your access requirements. Normally, this represents the IP address, IP range, or security group of the server that needs to connect to your Microsoft SQL Server instance:
   • The static IP address of the permitted host in CIDR notation (e.g. 10.0.0.5/32).
   • The IP address range of the permitted network/subnetwork in CIDR notation, for example 10.0.5.0/24.
   • The name or ID of another security group available in the same AWS cloud region.
3. Choose Save rules to apply the configuration changes.

07 Repeat steps no. 4 – 6 to reconfigure other EC2 security groups that allow unrestricted MSSQL access.

08 Change the AWS cloud region from the navigation bar and repeat the remediation process for other regions.

01 Run revoke-security-group-ingress command (OSX/Linux/UNIX) using the ID of the Amazon EC2 security group that you want to reconfigure as the identifier parameter (see Audit section part II to identify the right resource), to remove the inbound rules that allow unrestricted access on TCP port 1433 (Microsoft SQL Server – MSSQL):

aws ec2 revoke-security-group-ingress
  --region us-east-1
  --group-id sg-01234abcd1234abcd
  --ip-permissions IpProtocol=tcp,FromPort=1433,ToPort=1433,IpRanges=[{CidrIp="0.0.0.0/0"}],Ipv6Ranges=[{CidrIpv6="::/0"}]
  --query 'Return'

02 The command output should return true if the request succeeds. Otherwise, it should return an error:

true

03 Run authorize-security-group-ingress command (OSX/Linux/UNIX) to add the inbound rule removed at the previous step with a different set of parameters in order to restrict access on TCP port 1433 to trusted entities only (IP addresses, IP ranges, or security groups). To create and attach custom inbound/ingress rules to the selected Amazon EC2 security group based on your access requirements, use one of the following options (the command does not produce an output):

1. Add an inbound rule that allows traffic from an authorized static IP address via TCP port 1433, using CIDR notation (e.g. 10.0.0.5/32):
   aws ec2 authorize-security-group-ingress
     --region us-east-1
     --group-id sg-01234abcd1234abcd
     --protocol tcp
     --port 1433
     --cidr 10.0.0.5/32
   

2. Add an inbound/ingress rule that allows traffic from a trusted IP address range via TCP port 1433, using CIDR notation (for example, 10.0.5.0/24):
   aws ec2 authorize-security-group-ingress
     --region us-east-1
     --group-id sg-01234abcd1234abcd
     --protocol tcp
     --port 1433
     --cidr 10.0.5.0/24
   

3. Add an inbound rule that allows traffic from another security group (e.g. sg-01234123412341234) available in the same AWS cloud region via TCP port 1433. This can be the security group of the server that needs to connect to your Microsoft SQL Server instance:
   aws ec2 authorize-security-group-ingress
     --region us-east-1
     --group-id sg-01234abcd1234abcd
     --protocol tcp
     --port 1433
     --source-group sg-01234123412341234
   

04 Repeat steps no. 1 – 3 to reconfigure other EC2 security groups that allow unrestricted MSSQL access.

05 Change the AWS cloud region by updating the --region command parameter value and repeat steps no. 1 – 4 to perform the remediation process for other regions.