How to Change Database Authentication

  
How to Change Database Authentication
 
Note: This how-to is only valid for SQL Server databases configurations.
 
When upgrading an existing Agile Platform installation to start using a different authentication mode in the database, it is important to follow the steps below to avoid undesired behaviors.
To reduce downtime during the process, it is advised that you previously execute the steps of the Database Catalogs, though you won’t be able to test the connections in Service Center.
Also, in Farm Installations there can be a temporary problem contacting the Session database between the steps 8 and 10.
  
Standalone Installations
 
1- Open the Configuration Tool in the Controller machine
2- Change the 'Authentication' dropdown to the desired type of Authentication.
3- Configure usernames and passwords for all users on the ‘Database’ tab. Take note that users may be required to have ‘Run as a Service’ permissions on the Front-end servers.
4- Click 'Grant Permissions' and check everything is ok with the 'Test Connection' for each user.
 
If changing to Windows Authentication.
 
If machines have Application Pools configured to run OutSystems Applications other than the 'OutSystemsApplications' pool:
 
In 'IIS Management Console', for each pool running OutSystems applications set the 'Identity' in Advanced Settings to match the 'Runtime user' and click Ok
 
If changing to SQL Authentication.
 
It is recommended that you also restore the Application Pools 'Identity' to the 'NetworkService' or 'ApplicatioPoolIdentity'
 
  
5- Click 'Configure Session Database' in the 'Session' tab. 
6- Click 'Apply and Exit' and choose to run the Service Center Install.
 
If there are Database Catalogs configured:
7- Open the Service Center
8- Go to 'Administration’ -> ‘Database Catalogs’
9- For each configured catalog (other than 'Main') you will have to give permissions to users to access that catalog in the database.
The 'Admin user' requires the roles: db_accessadmin, db_datareader, db_datawriter, db_ddladmin and db_securityadmin.
The 'Runtime user' requires the db_datareader and db_datawriter.
Notes:
- If the Catalog was configured before 7.0 with a specific Runtime user, our recommendation is to change it to the same user configured in the Configuration Tool.
- Using specific users is only allowed for backward compatibility.
 
10- Test the connection of each database catalog.
 
11- Republish all eSpaces.
 
 
Farm Installations
 
1- Open the Configuration Tool in the Controller machine
2- Change the 'Authentication' dropdown to the desired type of Authentication.
3- Configure usernames and passwords for all users on the ‘Database’ tab.
4- Click 'Grant Permissions' and check everything is ok with the 'Test Connection' for each user.
5- Use the 'File->Export Configuration' and save the configuration file
 
On each Front-end:
6- Use the 'File->Import Configuration' with the exported configuration file
7- Configure all usernames and passwords on the ‘Database tab’ if needed (because in 'Windows Authentications' passwords are not saved)
8- 'Grant Permissions' and check everything is ok with the 'Test Connection' for each user.
 
If changing to Windows Authentication.
 
If machines have Application Pools configured to run OutSystems Applications other than the 'OutSystemsApplications' pool:
 
In 'IIS Management Console', for each pool running OutSystems applications set the 'Identity' in Advanced Settings to match the 'Runtime user' and click Ok.
 
If changing to SQL Authentication.
 
It is recommended that you also restore the Application Pools 'Identity' to the 'NetworkService' or 'ApplicatioPoolIdentity'
 
 
On the Controller Configuration Tool:
9- Click 'Configure Session Database' in the 'Session' tab. To avoid downtime, set the Session state to a different database after step 4.
10- Click 'Apply and Exit' and choose to don't run the Service Center Install.
 
On each Front-end:
11- Click 'Apply and Exit'.
NOTE: In case you have multiple Front-end servers, you can avoid downtime by performing this operation one Front-end at a time and leaving at least a one Front-end untouched until you are sure that all applications have been republished.
 
On the Controller:
12- Open the Configuration Tool again, click 'Apply and Exit' and choose to run the Service Center Install.
 
If there are Database Catalogs configured:
13- Open the Service Center
14- Go to 'Administration’ -> ‘Database Catalogs’
15- For each configured catalog (other than 'Main') you will have to give permissions to users to access that catalog in the database.
The 'Admin user' requires the roles: db_accessadmin, db_datareader, db_datawriter, db_ddladmin and db_securityadmin.
The 'Runtime user' requires the db_datareader and db_datawriter.
Notes:
- If the Catalog was configured before 7.0 with a specific Runtime user, our recommendation is to change it to the same user configured in the Configuration Tool.
- Using specific users is only allowed for backward compatibility.
16- Test the connection of each database catalog.
  
17- Republish all eSpaces.
I've performed this operation (going from SQL authentication to Windows authentication, standalone installation, version 7.0.0.14) and it worked.

The only thing that we had to fix afterwards was the MSMQ permissions for async logging. The existing MSMQ outsystems queues had security permissions set up that allowed the machines NETWORK account to access them, but were not changed to give the new Windows account we configured as the Logging service account to have permission. The effect was that the queues weren't written to, and a warning was appearing in the server's event log saying permission denied.

Rathetr than go thorugh and grant permission to each of these queues (there are about 10 of them, all with names starting outsystems...) we just deleted the outsystems queues from MSMQ, stopped all the OutSystems services and restarted them. The logging service recreated all the queues and gave them appropriate permissions. After that, everything just worked fine.

We did all this on a development stage server, so dropping the queues was fine, you might need to reconsider this if your doing this on a production server?
Hi Andrew

Regarding the problem you identified, thank you for the feedback. I have reported this problem internally at OutSystems to be fixed in the Agile Platform - log queue permissions should have been reset.

We did all this on a development stage server, so dropping the queues was fine, you might need to reconsider this if your doing this on a production server?

Until the problem is fixed, however, that will need to be done manually - either by resetting permissions or deleting the queues to be recreated.
Deleting the message queues will cause all the information kept in them (Agile Platform / Service Center logs pending write to the database and/or SMS messages if such integration is being used) to be lost.
If that is acceptable, that option can be used. Otherwise, changing the permissions should be preferred.

With best regards,
Acácio