13

I'm trying to set up a AWS Glue job and make a connection to Redshift.

I'm getting error when I set the connection type to Redshift:

"Unable to find a suitable security group. Change connection type to JDBC and retry adding your connection."

Following what was said here in these forums, I added permissions to my IAM account for role AWSGlueServiceRoleDefault:

enter image description here

I then set up the job with the matching IAM role AWSGlueServiceRoleDefault:

enter image description here

I need to store the Glue data in Redshift DB, so I chose JDBC then added a connection:

enter image description here

As soon as I choose Redshift, it complains that it cannot find a suitable security group. Why is this?

enter image description here

enter image description here

user3871
  • 12,432
  • 33
  • 128
  • 268

3 Answers3

32

I found the same issue when trying to connect Glue with Amazon RDS (MySQL) and solved it following the AWS Glue guidelines -> Setting Up a VPC to Connect to JDBC Data Stores.

In a nutshell you should check that the security group associated to your RedShift cluster allows self-referencing traffic.

  1. Go to RedShift console and choose Clusters
  2. Look at the Cluster Properties section for the ID of the security group associated to the cluster (e.g. sg-957be3ef).
  3. Click at the security group name to jump to the EC2 console -> Security groups section. Choose the group and modify the Inbound and Outbound rules adding self-referencing rule to allow AWS Glue components to communicate.
    • Inbound rules: chose Type=All TCP, leave the default values and type the security group in the Source field (i.e. sg-957be3ef for this example).
    • Outbound rules: same as Inbound rules.

Hope that works!

fikipollo
  • 528
  • 5
  • 9
  • After setting up self-referencing rule, I had an issue on SSH to my endpoint, it ends up the port open to 22 from 0.0.0.0/0 is needed. – wei Sep 25 '19 at 07:35
  • Same exact issue, and this solution did the trick! Thank you sir – pyro_lemur Dec 11 '19 at 23:56
  • 1
    I am trying to do the exact same thing for for a postgres database but when trying to add the security group in the source field from inbound rules I get this message: `You may not specify a referenced group id for an existing IPv4 CIDR rule.` I would really appreciate your help. – brenda Jul 23 '21 at 16:01
1

You have to create a:

1)AWSGlueServiceRole role and attach s3FullAccess,GlueServiceRole and RedshiftFullAccess policy.

2)Check you have vpc Endpoint, If not create a VPC endpoint and make sure subnets are added to route table. 3)Create self referencing security group.

Now connect using JDBC connection,

jdbc:type://xxxx:port/databasename

type is redshift/postgresql/etc... xxxx: server name where the database hosted.

Keren Caelen
  • 1,466
  • 3
  • 17
  • 38
Narayana shanubhog
  • 31
  • 4
0

Use JDBC connection instead to connect to your Redshift. Just ensure the corresponding VPC has an end-point defined.

Ishan Arora
  • 124
  • 1
  • 6