RDS with AD

1. Create MS AD from Directory Services. DCs on private us-east-1a and private us-east-1b

2. set DHCP Options Sets for VPC

3. Configure RDS SQL Server (sql-with-ad) for AD Authentication

4. This will create IAM role necessary for using Windows authentication
–> rds-directoryservice-access-role

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Action": [
            "ds:DescribeDirectories", 
            "ds:AuthorizeApplication", 
            "ds:UnauthorizeApplication",
            "ds:GetAuthorizedApplicationDetails"
        ],
      "Effect": "Allow",
      "Resource": "*"
    }
  ]
}

5. Check RDS / Configuration / Security, Directory status is joined

6. Create an Active Directory User (user01)

7. Create SQL Server Windows Login:

• Login to SQL instance in SSMS as master user (admin)
• Create a login account for the AD user created with this query:

USE [master]
GO
CREATE LOGIN [sql\user01] FROM WINDOWS WITH DEFAULT_DATABASE = [master], DEFAULT_LANGUAGE = [us_english];
GO

• Create a database user for this login and grant the user permissions to database schemas and objects.

Conclusion

As you can see, configuring an RDS SQL Server for Windows-based authentication is simple but offers data security benefits. Security auditing can easily check log in/log off events for AD users, just like SQL standard logins.

However, it also means users can be temporarily disabled or removed from an AD group without anything done from the database side. Role-based permissions assigned to AD groups can free up DBAs from assigning roles to individual users.

Using AWS-managed Microsoft AD means system administrators don’t have to manually install, configure, and manage a separate Active Directory in EC2 instances and worry about backup, fault tolerance, etc.