Azure SQL Server in IaaS - Password reset

Question

Hi all,

We have our Microsoft SQL server configured in VM IaaS. We are using service account to connect to server. As per password rotation policy, we have request to reset the password every three months. But , while resetting this password we are facing many issue like application downtime, manually replacing the password and it worst case we have to raise Change request and deploy the code in PROD environments.

Is there anyway we can avoid this issue in IaaS platform.

Answer 1

Good day ,

Is there anyway we can avoid this issue in IaaS platform.

(1) As I see it, It is true that Virtual Machine (VM) is an Infrastructure as a service (IaaS) but the applications (like SQL Server) which you install inside the VM are basically like On-premises for most aspects. Even when you have external tools like SQL Server IaaS Agent extension (SqlIaasExtension) the SQL Server itself should be considered as application which you fully manage like SQL Server On-premises. The SqlIaasExtension is simply an automation tool like like other tools you can use to manage the server.

(2) Once you get the first point and you see the SQL Server on Virtual Machine as not a lot different than SQL Server on-premises, then you can probably find the architecture which will fit you best.

we have request to reset the password every three months

(3) For better security, It is recommended not to enable SQL Server authentication. Using windows authentication will also prevent such issue since your application will also based on windows authentication and changing the password will not reqriure to configure each application and client for the new password.

Even if you enable SQL Server authentication, It is HIGHLY NOT recommended to use sa account. It is a good idea to create separate LOGIN and USERs for each application (or group of apps) in most cases. This will also help in your case, since you have better control on which app use which LOGIN and you can change the password in both at the same time.

we have request to reset the password

If this a must and you have to keep this architecture then it will be much simpler manage and change the password from the application - as mentioned above, you can (probably should) have a separate LOGIN for each app and in this case you simply can control the LOGIN information from the app, which solve your issue.

This service account has to be reset every 3 months.

Not it is not. You should not use this service account for your app probably, like we should not use sa LOGIN.

So... with the information we have here, in first glance, if you cannot use windows authentication, then it seems like you should create LOGIN for each app and manage the information of the LOGIN from the app or scheduled to change the app information together

Answer 2

What service account do you use to connect to the SQL Server?

Is this an AD service account that your application is using?

I would suggest looking at the option of a Managed Service Account - in this case the password rotation is managed under the covers by AD and nobody ever knows what the password is. This is not a suitable option in every case but without more information I am guessing as to the type of service account that you mean.