search

equinor / fusion-project-portal

Project Portal powered By Fusion
https://project.fusion.equinor.com/
MIT License
1 stars 0 forks source link

Investigate if SQL Password is needed #652

Open Jossilainen opened 3 months ago

Jossilainen commented 3 months ago

Expected outcome

Investigate if SQL Password is needed in connection strings. Can we use entra-id in stead? What must be set up in azure, and how to set up radix.

Following keyvaults are used for connection strings now: kv-pep-api-noe-prod kv-pep-api-noe-test kv-pep-api-noe-feature kv-pep-noe-shared

SQL servers: sql-pep-api-noe-feature sql-pep-api-noe-test sql-pep-api-noe-prod

Business Value / Developer Experience

Eliminate hassle with updating passwords every 6 months or annually.

User Acceptance Tests (UAT)

Environments

All testing of issues are done in project-portal-test

For Backend use these

Jossilainen commented 2 months ago

Waiting for infra

ken-mellem commented 3 weeks ago

What exactly are we waiting for from infra? How can we make progress here?

Jossilainen commented 3 weeks ago

Just waiting for infra to get help with best practice on how to do this.

@nemanja-drobac has been assigned and should assign someone to have a look at this.

ken-mellem commented 3 days ago

@Jossilainen please follow this up.

nemanja-drobac commented 3 days ago

@Jossilainen do you have time today to discuss this? I can just come over to you if you are available and in the office?

nemanja-drobac commented 3 days ago

It is possible to get rid of password here and use Azure AD only auth. Radix team will come soon with more details.

Richard87 commented 3 days ago

Hi!

Dropping by, we just did this in Radix Vulnerability Scanner API, and in Radix Cost Allocation API that both uses Azure SQL Server/Database.

It requires a managed identity that are allowed access to your SQL Server, and federated credentials linked to Radix: https://github.com/equinor/radix-vulnerability-scanner-api/blob/master/radixconfig.platform.yaml#L26 https://radix.equinor.com/guides/workload-identity/

When a Managed Identity have been linked to a Federated Credential in Radix, you can just use a DSN like this to authenticate: server=%s;database=%s;fedauth=ActiveDirectoryDefault (https://github.com/equinor/radix-vulnerability-scanner-api/blob/master/repository/gorm_repository.go#L22C22-L22C74)

If you have a workflow where you automatically assigns users (like configuring a server in GitHub actions), the server needs the Directory Reader role. If you manually sets up the user and access control, the server will use your own token when verifying the service principal against MS Graph. If not, will use the severs identity

Equinor will only provide Directory Reader role for system-assigned identities for security reasons.

https://learn.microsoft.com/nb-no/azure/azure-sql/database/authentication-azure-ad-user-assigned-managed-identity?view=azuresql

ken-mellem commented 2 days ago

@Jossilainen can we get this in ?

Jossilainen commented 2 days ago

@ken-mellem will look into it.