Unable to access MySQL database with Python connection…

Hello,

I'm relatively new to Google Cloud software, and despite my efforts to thoroughly read the documentation, I found myself unable to access my MySQL database on Google Cloud. I hope this information helps clarify my problem to provide context.

I'm tasked with creating access to a MySQL database on Google Cloud for my company's employees. You created an instance with a private IP and configured a service account with all required roles and permissions. I plan to use this to allow workers to access the database. Additionally, you configured your VPC network, connected your Cloud SQL instance to this VPC, and authorized your local IP to connect. At this time, we do not have any tunnels configured with Google Cloud VPN or your local network. The first question is: Do I need VPN IPsec on my local network to enable communication between my Cloud SQL instance and my local network?

You cannot configure VPN IPsec on your local network at this time. Assuming it was needed, I temporarily switched the Cloud SQL instance to public just long enough to establish a connection. I was hoping that this would simplify the process of connecting to the database. I set up some firewall rules that only allow communication from the local IP to the instance. I tried to connect to the database using the following Python script (excerpted from the documentation).

“”

please import us

import sqlalchemy

import pymysql

import from google.authload_credentials_from_file

google.cloud.sql.connector import connector, from IPType

from dotenv import load_dotenv

def connect_with_connector() -> sqlalchemy.engine.base.Engine:

“”

Initialize the connection pool for a Cloud SQL instance of MySQL.

Use the Cloud SQL Python connector package.

“”

# Note: Although it would be convenient to store credentials in environment variables, this is not the case

# secure – Consider more secure solutions such as:

# See Cloud Secret Manager (https://cloud.google.com/secret-manager).

# Keep your secrets safe.

load_dotenv()

Instance connection name = os.getenv(“INSTANCE_CONNECTION_NAME”)

db_user = os.getenv(“DB_USER”)

db_pass = os.getenv(“DB_PASS”)

db_name = os.getenv(“DB_NAME”)

credential_path = os.getenv(“CREDENTIAL_PATH”)

credentials, _ = load credentials from file(credentials path)

# SSL file path

ssl_ca = os.getenv(“SSL_CA”) # path to server-ca.pem file

ssl_cert = os.getenv(“SSL_CERT”) # path to client-cert.pem file

ssl_key = os.getenv(“SSL_KEY”) # path to client-key.pem file

ssl_args = {

“SSL”: {

'as': ssl_as,

'Certificate': ssl_cert,

'key': ssl_key

}

}

# ip_type = IPTypes.PRIVATE if private_ip else IPTypes.PUBLIC

ip_type = IPTypes.PUBLIC

connector = connector(ip_type)

def getconn() -> pymysql.connections.Connection:

conn: pymysql.connections.Connection = connector.connect(

instance connection name,

“pymysql”,

user=db_user,

db=database name,

**ssl_args

)

return con

pool = sqlalchemy.create_engine(

“mysql+pymysql://”,

Author=getconn,

pool_size=5, # Connection pool size

max_overflow=2, # Maximum number of additional connections allowed

pool_timeout=30, # Maximum time to wait before getting a connection

pool_recycle=1800, # Maximum connection lifetime (in seconds)

)

return pool

Since I intend to connect to a service account (for my final use case), I ran the following command to set the application default credentials (ADC).

gcloud auth application-default login –impersonate-service-account SERVICE_ACCT_EMAIL

gcloud config set auth/impersonate_service_account gcloud auth application-default login –impersonate-service-account SERVICE_ACCT_EMAIL

Now that we have the credentials file, set its path to the environment variable GOOGLE_APPLICATION_CREDENTIALS. You have successfully granted all required permissions to the service account. Below is a list of permissions.

Cloudaicompanion.entitlements.get Cloudsql.backupRuns.create Cloudsql.backupRuns.get Cloudsql.backupRuns.list Cloudsql.databases.create Cloudsql.databases.get Cloudsql.databases.list Cloudsql.databases.update Cloudsql.instances.addServerCa Cloudsql.instances.connect Cloudsql.instances.export Cloudsql.instances.failover Cloudsql.instances.get Cloudsql.instances.list compute.networks.get iam.serviceAccounts.actAs iam.serviceAccounts.get iam.serviceAccounts.getAccessToken iam.serviceAccounts.getOpenIdToken iam.serviceAccounts.implicitDelegation iam.serviceAccounts.list iam.serviceAccounts.signBlob iam.serviceAccounts.signJwt resourcemanager.projects.get

I'm aware that these permissions may be excessive for my needs, but I'll address that once I can connect to the database. Additionally, as mentioned above, we added firewall rules for the following items:

Allow inbound traffic to protocol and port 3306 from your local IP to your Cloud SQL instance and VPC IP. Allow outbound traffic from your VPC and Cloud SQL instance IPs to your local IP and explicitly specify that IP in the Destination field.

In fact, I learned these concepts as I was implementing them, and when I got a little desperate, I tried to connect using Google Cloud Proxy and other methods, which may have affected my configuration.

In conclusion, you receive the following error message:

sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1045, “Access is denied for user 'serviceaccountusername @'cloudsqlproxy~localIP' (with password: NO)”)

(Context for this error: URL has been removed by staff)

I'm trying to connect with a service account, so I'm not using a password. I have the credentials file. I triple-checked information such as db_user, db_name, instance_connection_name, and ssl_certificate (tried something that might not be necessary if you connect using the Python connector).

I'm having trouble identifying the cause of the error and the resulting solution. Is there something important that I might have overlooked? Or am I doing something wrong?

Thank you for your cooperation.

What Are The Main Benefits Of Comparing Car Insurance Quotes Online

LOS ANGELES, CA / ACCESSWIRE / June 24, 2020, / Compare-autoinsurance.Org has launched a new blog post that presents the main benefits of comparing multiple car insurance quotes. For more info and free online quotes, please visit https://compare-autoinsurance.Org/the-advantages-of-comparing-prices-with-car-insurance-quotes-online/ The modern society has numerous technological advantages. One important advantage is the speed at which information is sent and received. With the help of the internet, the shopping habits of many persons have drastically changed. The car insurance industry hasn't remained untouched by these changes. On the internet, drivers can compare insurance prices and find out which sellers have the best offers. View photos The advantages of comparing online car insurance quotes are the following: Online quotes can be obtained from anywhere and at any time. Unlike physical insurance agencies, websites don't have a specific schedule and they are available at any time. Drivers that have busy working schedules, can compare quotes from anywhere and at any time, even at midnight. Multiple choices. Almost all insurance providers, no matter if they are well-known brands or just local insurers, have an online presence. Online quotes will allow policyholders the chance to discover multiple insurance companies and check their prices. Drivers are no longer required to get quotes from just a few known insurance companies. Also, local and regional insurers can provide lower insurance rates for the same services. Accurate insurance estimates. Online quotes can only be accurate if the customers provide accurate and real info about their car models and driving history. Lying about past driving incidents can make the price estimates to be lower, but when dealing with an insurance company lying to them is useless. Usually, insurance companies will do research about a potential customer before granting him coverage. Online quotes can be sorted easily. Although drivers are recommended to not choose a policy just based on its price, drivers can easily sort quotes by insurance price. Using brokerage websites will allow drivers to get quotes from multiple insurers, thus making the comparison faster and easier. For additional info, money-saving tips, and free car insurance quotes, visit https://compare-autoinsurance.Org/ Compare-autoinsurance.Org is an online provider of life, home, health, and auto insurance quotes. This website is unique because it does not simply stick to one kind of insurance provider, but brings the clients the best deals from many different online insurance carriers. In this way, clients have access to offers from multiple carriers all in one place: this website. On this site, customers have access to quotes for insurance plans from various agencies, such as local or nationwide agencies, brand names insurance companies, etc. "Online quotes can easily help drivers obtain better car insurance deals. All they have to do is to complete an online form with accurate and real info, then compare prices", said Russell Rabichev, Marketing Director of Internet Marketing Company. CONTACT: Company Name: Internet Marketing CompanyPerson for contact Name: Gurgu CPhone Number: (818) 359-3898Email: [email protected]: https://compare-autoinsurance.Org/ SOURCE: Compare-autoinsurance.Org View source version on accesswire.Com:https://www.Accesswire.Com/595055/What-Are-The-Main-Benefits-Of-Comparing-Car-Insurance-Quotes-Online View photos