Posted by Tejashwini on 09 June 2023

Are you struggling to create a reliable inventory of SQL servers running on AWS? If so, don't worry! You're not alone.

The problem

From an operational standpoint, it can be difficult to accurately list the inventory of SQL servers running on AWS infrastructure. But why is this task so challenging?

Firstly, without access to tools like AWS Systems Manager (SSM) or agents that provide access to the operating system, gathering detailed information about what's running inside an EC2 instance, including whether it's running a SQL server, is virtually impossible.

Secondly, relying solely on tags to identify SQL servers isn't reliable. While tags can provide information about the purpose or function of an EC2 instance, many customers don't consistently or accurately tag their resources. This inconsistency makes it unreliable to solely use tags to identify SQL servers.

Overall, the lack of direct access to the operating system and the unreliability of tagging practices make it difficult for AWS operations to obtain a precise and complete inventory of SQL servers running on EC2 instances.

The Solution

Fortunately, there is an alternative approach to determine whether a server is running SQL DB, which may offer improved accuracy:

Since all instances are associated with Security Groups (SGs), we can leverage this information to create a bash script that can be executed from the AWS CloudShell.

The script will list all SGs in a specific region that have an inbound rule allowing connectivity to Port 1433, which is commonly used by SQL DB servers. It will then identify the instances that are attached to these SGs. By doing this, the script will generate a list of EC2 instances configured to accept network communication on Port 1433, thus providing a potential list of SQL DB servers.

This approach increases the likelihood of accurately identifying SQL DB servers by focusing on the specific inbound rule related to Port 1433 and associating it with the instances in question.

The Script

#!/bin/bash # Set the desired region   region="eu-north-1"   # Set the SQL port sqlPort="1433"   # Get all security groups with inbound rules allowing access to SQL port sgIds=$(aws ec2 describe-security-groups --region $region \   --filters "Name=ip-permission.to-port,Values=$sqlPort" \   --query "SecurityGroups[].GroupId" --output text)   # Loop through each security group and list associated instances for sgId in $sgIds; do   echo "Security Group: $sgId"   instanceIds=$(aws ec2 describe-instances --region $region \                --filters "Name=instance.group-id,Values=$sgId" \                --query "Reservations[].Instances[].InstanceId" --output text)     # Check if any instances are associated with the security group   if [ -z "$instanceIds" ]; then                echo " "   else                echo "Associated Instances:"                echo "$sgID $instanceIds"   fi    echo done 

The Drawback

While the alternative approach can be highly effective, it's important to recognize its limitations. The script assumes that security groups adhere to the rules and that a reasonable level of caution is exercised when creating them. However, it may not detect a security group that allows all traffic to EC2 instances, even if it includes SQL DB communication. Additionally, if there have been any modifications in a particular environment, such as a customized port number for SQL DB, the script may need to be rerun with the updated information.

Conclusion

In conclusion, creating a reliable inventory of SQL servers running on AWS can be challenging, but it's not impossible. By acknowledging the limitations and challenges of the previous approach, it's possible to fine-tune the script and create a more accurate inventory of SQL servers in your AWS environment.

By incorporating additional checks and considerations, such as exploring alternative methods beyond relying solely on Security Groups and port numbers, you can enhance the accuracy of the script. This might involve leveraging other available resources, like instance metadata, AWS Systems Manager, or agent-based solutions to gain deeper insights into the running services.

By taking these factors into account and making appropriate adjustments to the script, you can develop a more reliable and precise inventory of SQL servers within your AWS environment. This improved approach will enable you to obtain a clearer understanding of the SQL server instances running and better meet your requirements for capacity planning, licensing, or identifying any unwanted SQL servers. So, start using these tips and tricks to create an accurate inventory of SQL servers in your AWS environment today!