Running SQL script on onPrem MSSQL from Azure CI/CD with windows authentication
Hi all,
I have found a challenging problem.
We have a project using EF Migration. In a pipeline We want to deploy it to an OnPrem MsSql database.
Running on a self hosted windows agent.
But, our company policy allows only windows authentication to access OnPrem MsSql server.
The job runs in the name of a service user on the agent.
So I have to add ddl-admin to that service user for the database.
It sounds good but We want to use this self hosted agent for a lot more projects -> I need to add access to that agent service user for all databases. This is a security risk.
So the question: how to set up things to use different users for all databases on one self hosted agent using windows authentication ?
Thanks,
Zoli