Error: IAM role ARN value is invalid or does not include the required permissions for: SQLSERVER_AUDIT | Cloudformation

Question

Hi everyone,

I am facing the IAM role Arn invalid error while trying to run below template. The IAM role getting error is used in the option group for audit logs. In the parameter section it is referred as

```
 IAMRoleARN:
    Description: Arn of IAM role used for audit log
    Type: String  
```

```
  Option group configuration: 
myOptionGroup: 
    Type: "AWS::RDS::OptionGroup"
    Properties: 
      EngineName: sqlserver-ex
      MajorEngineVersion: "15.00"
      OptionGroupDescription: option group for the rds
      OptionConfigurations: 
        - 
          OptionName: SQLSERVER_AUDIT
          OptionSettings: 
             -  Name: S3_BUCKET_ARN
                Value: !Ref 'S3BucketARN'
             -  Name: IAM_ROLE_ARN
                Value: Ref 'IAMRoleARN'        
```

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.SQLServer.Options.Audit.html#Appendix.SQLServer.Options[%E2%80%A6]ateAuditsAndSpecifications

Entire CFT is as mentioned below: 
```
Parameters:
  DBUser:
    NoEcho: 'false'
    Description: The database admin account username
    Type: String
    MinLength: '1'
    MaxLength: '16'
  DBPassword:
    NoEcho: 'true'
    Description: The database admin account password
    Type: String
    MinLength: '8'
    MaxLength: '41'
  DBInstanceClass:
    Description: Instance class for RDS
    Type: String
    MinLength: '1'
    MaxLength: '16'
  AllocatedStorage:
    Description: Required storage
    Type: Number
  Engine:
    Description: DB Engine
    Type: String
    MinLength: '1'
    MaxLength: '16'
  EngineVersion:
    Description: RDS version
    Type: String
  BackupRetentionPeriod:
    Description: RDS retention period
    Type: String
  DBInstanceIdentifier:
    Description: DB identifier
    Type: String
  BackupRetentionPeriod:
    Description: RDS retention period
    Type: String
  EnablePerformanceInsights:
    Description: Enable or Disable performance insight
    Type: String
  MultiAZ:
    Description: Enable or diable multi AZ
    Type: String
  PreferredBackupWindow:
    Description: Backup window
    Type: String
  PreferredMaintenanceWindow:
    Description: Maintainence window
    Type: String
  VPCSecurityGroups:
    Description: SG for RDS
    Type: String
  SubnetID1:
    Description: Subnets for the RDS in subnet group
    Type: String
  SubnetID2:
    Description: Subnets for the RDS in subnet group
    Type: String
  MaxAllocatedStorage:
    Description: Scales database to a specific threshold
    Type: Number
    Default: 1000
  MonitoringRoleArn:
    Description: RDS Monitoring Role
    Type: String
  BucketName:
    Description: Name of S3 bucket for audit log
    Type: String
  S3BucketARN:
    Description: Arn of S3 bucket used for audit log
    Type: String
  IAMRoleARN:
    Description: Arn of IAM role used for audit log
    Type: String    
Resources:
  MyDB:
    Type: AWS::RDS::DBInstance
    Properties:
      AllocatedStorage: !Ref 'AllocatedStorage'
      DBInstanceClass: !Ref 'DBInstanceClass'
      Engine: !Ref 'Engine'
      EngineVersion: !Ref 'EngineVersion'
      LicenseModel: license-included
      BackupRetentionPeriod: !Ref 'BackupRetentionPeriod'
      DBInstanceIdentifier: !Ref 'DBInstanceIdentifier'
      DBSubnetGroupName: !Ref 'MYSubnetGroup'
      EnableCloudwatchLogsExports: 
         - error
      EnablePerformanceInsights: !Ref 'EnablePerformanceInsights'
      MultiAZ: !Ref 'MultiAZ'
      OptionGroupName: !Ref 'myOptionGroup'
      PreferredBackupWindow: !Ref 'PreferredBackupWindow'
      PreferredMaintenanceWindow: !Ref 'PreferredMaintenanceWindow'
      PubliclyAccessible: False
      StorageType: gp2
      MaxAllocatedStorage: !Ref 'MaxAllocatedStorage'
      MonitoringInterval: 60
      MonitoringRoleArn: !Ref 'MonitoringRoleArn'
      VPCSecurityGroups: 
        - !Ref 'VPCSecurityGroups'
      MasterUsername: !Ref 'DBUser'
      MasterUserPassword: !Ref 'DBPassword'
      DBParameterGroupName: !Ref 'MyRDSParamGroup'
      DeletionProtection: False
      AutoMinorVersionUpgrade: False
      CopyTagsToSnapshot: True
  MyRDSParamGroup:
    Type: AWS::RDS::DBParameterGroup
    Properties:
      Family: sqlserver-ex-15.0
      Description: CloudFormation Sample Database Parameter Group
      Parameters:
        rds.force_ssl: '1'
  myOptionGroup: 
    Type: "AWS::RDS::OptionGroup"
    Properties: 
      EngineName: sqlserver-ex
      MajorEngineVersion: "15.00"
      OptionGroupDescription: option group for the rds
      OptionConfigurations: 
        - 
          OptionName: SQLSERVER_AUDIT
          OptionSettings: 
             -  Name: S3_BUCKET_ARN
                Value: !Ref 'S3BucketARN'
             -  Name: IAM_ROLE_ARN
                Value: Ref 'IAMRoleARN'        
  MYSubnetGroup:
    Type: AWS::RDS::DBSubnetGroup
    Properties: 
      DBSubnetGroupDescription: subnet group for the rds
      SubnetIds: 
        - !Ref 'SubnetID1'
        - !Ref 'SubnetID2'
```

Accepted Answer

I don't know what error you are getting, but isn't it "Value: !Ref 'IAMRoleARN'" instead of "Value: Ref 'IAMRoleARN'"?  
Ref may be written incorrectly.  
The following information is written in the correct manner.  
```
  myOptionGroup: 
    Type: "AWS::RDS::OptionGroup"
    Properties: 
      EngineName: sqlserver-ex
      MajorEngineVersion: "15.00"
      OptionGroupDescription: option group for the rds
      OptionConfigurations: 
        - 
          OptionName: SQLSERVER_AUDIT
          OptionSettings: 
             -  Name: S3_BUCKET_ARN
                Value: !Ref 'S3BucketARN'
             -  Name: IAM_ROLE_ARN
                Value: !Ref 'IAMRoleARN'        
```

Error: IAM role ARN value is invalid or does not include the required permissions for: SQLSERVER_AUDIT | Cloudformation

Relevant content