Creating logins for SQL Server on GCP Cloud SQL through Terraform
Google Cloud Platform (GCP) provides a PaaS service called Cloud SQL, which provides a cloud-based alternative to local MySQL, PostgreSQL, and SQL Server databases.
For SQL Server, not all Microsoft provided features are supported on Cloud SQL. So, go with SQL Server on GCP Compute Engine if you want full set of features.
“sqlserver” is the root user in SQL Server on Cloud SQL. The “sqlserver” user is part of the CustomerDbRootRole role, and its permissions (privileges) include the following:
CONNECT SQL
CREATE ANY DATABASE
ALTER ANY LOGIN
ALTER ANY CONNECTION
ALTER TRACE
VIEW ANY DATABASE
VIEW ANY DEFINITION
VIEW SERVER STATE
ALTER SERVER STATE
CREATE SERVER ROLE
ALTER ANY SERVER ROLE
For more details, refer this link.
In this blog post, I provided the various approaches of creating logins for SQL Server on Cloud SQL using Terraform.
Approach 1: Create all the logins using Terraform code.
In this approach, we create all the logins needed for SQL Server databases on Cloud SQL instance through Terraform code itself.
In the “users” code block, the login credentials are provided. I didn’t hard-code the passwords, but passed them as variables. For code security, its always recommended to store the passwords in HashiCorp Vault or as sensitive variables in Terraform workspaces.
The disadvantage with this approach is, the code has to be updated and re-deployed every time a new login is needed.
Approach 2: Do not create any login using Terraform code.
In this approach, we do not create any login (including “sqlserver”) through Terraform code. The “users” code block will be just empty.
When Terraform creates the Cloud SQL instance and SQL Server databases, “sqlserver” login will be created by default but with an empty password. You can login to SSMS using the default user login credentials and can create the necessary logins and grant them database level access (like db_datareader, db_datawriter, etc.,)
The disadvantage with this approach is, we can’t change the password of the default root user via SSMS. So, “sqlserver” will just have an empty password, which is not a security best practice.
Approach 3: Create only default root user login using Terraform code.
In this approach, we create only the login credentials for “sqlserver” through Terraform code. All the other needed logins will be created by logging into SSMS using the default root user login credentials.
This approach is better than the above mentioned approaches.
- No need to update and re-deploy the Terraform code whenever a new login is needed.
- The password for the default root user can be securely provided through the Terraform code.
Disclaimer: The posts here represent my personal views and not those of my employer or any specific vendor. Any technical advice or instructions are based on my own personal knowledge and experience.
