How to connect to the aws rds for sql server and upload a dataset(csv) using python dash code from my local machine if i am a IAM (having mfa authentication) user and not a root user?

0

I am trying to connect to the aws rds for sql server and upload a dataset(csv) using python dash code from my local machine. We are using IAM and having MFA authentication. To connect to the AWS RDS For SQL server, which and all credentials we need to pass in the code? I have passed aws_access_key, aws_secret_key, aws_session_token, mfa_device_arn, region, rds_host, rds_port, db_name, db_user, db_password. But we are facing the below error while trying to upload the dataset from local : sqlalchemy.exc.OperationalError: (pymssql._pymssql.OperationalError) (18456, b"Login failed for user 'musigma'.DB-Lib error message 20018, severity 14:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (munlq.csgknz7x9y0v.us-east-1.rds.amazonaws.com)\nDB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (munlq.csgknz7x9y0v.us-east-1.rds.amazonaws.com)\n")

i have attached the code for reference :

# AWS IAM user credentials and RDS connection details
aws_access_key = 'ASIAVHYFZXFRT3XHJ26Y'
aws_secret_key = 'mwDGdLNopX/XH8uBjAhq3Z7D1oRMdoIB3EnyelCz'
mfa_device_arn = 'arn:aws:iam::360252553571:mfa/ShraddhaDevice'  # Replace with your MFA device ARN
region = 'us-east-1'  # Replace with your AWS region
rds_host = 'munlq.csgknz7x9y0v.us-east-1.rds.amazonaws.com'
rds_port = 1433
db_name = 'munlq'
db_user = 'musigma'
db_password = 'musigma123'


# Initialize AWS and SQL Server connections
session = boto3.Session(
    aws_access_key_id=aws_access_key,
    aws_secret_access_key=aws_secret_key,
    aws_session_token=aws_session_token,
    region_name=region
)

# Prompt the user for the MFA code
mfa_code = input("Enter your MFA code: ")

Generate temporary credentials
sts_client = session.client('sts')
token = sts_client.get_session_token(
    DurationSeconds=3600,  # Adjust the duration as needed
    SerialNumber=mfa_device_arn,
    TokenCode=mfa_code
)

Use the temporary credentials for the rest of the code
aws_access_key = token['Credentials']['AccessKeyId']
aws_secret_key = token['Credentials']['SecretAccessKey']
aws_session_token = token['Credentials']['SessionToken']

# Initialize S3 client (for uploading CSV file)
s3 = session.client('s3')

table_names = []


def update_uploaded_data():
  global table_names
  cnxn = pymssql.connect(server=rds_host, user=db_user, password=db_password, database=db_name)

  # Get a list of table names in the database
  cursor = cnxn.cursor()


  cursor.execute("""
      SELECT name AS TABLE_NAME, create_date AS CREATE_DATE
      FROM sys.tables
  """)

  results = cursor.fetchall()


  threshold = datetime.datetime.now() - datetime.timedelta(days=30)

  for row in results:
      table_name = row[0]
      create_date = row[1]

      if create_date < threshold:
        print(f"Deleting {table_name}...")
        cursor.execute(f"DROP TABLE [{table_name}]")
        cnxn.commit()

  cursor.execute("""
      SELECT name AS TABLE_NAME, create_date AS CREATE_DATE
      FROM sys.tables
  """)
  results = cursor.fetchall()

  table_names = [row[0] for row in results]

  cursor.close()
  cnxn.close()
 

Note : We will remove the hard coded values soon after finishing our work. Also, we have allowed all traffic for both inbound and outbound rule. Attached screenshot below for reference : RDS SQL server main page

Can anyone please look into the above issue and give me the appropriate solution?

  • Are you using IAM to control access on RDS? Because RDS does not requires IAM credentials by default, it uses the username and password to authenticate, and in this case you would only need to deal with network stuff.

asked 8 months ago190 views
No Answers

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions