How to Migrate the Admin Portal Database?

Owned by Agat Support
Last updated: Apr 13, 2022 by Avi J. Levin (Unlicensed)

In this article, we are going to learn how to migrate an existing Admin Portal Database to another MS SQL Server

In this article  the name of old SQL server 'oldsql' and the new SQL server is named 'newsql'

Requirements:

  • Functioning Admin Portal DB
  •  MS SQL server

Preparation

Before we can begin the migration we need to stop the following components' services :

  • DMZ Bastion
  • Sip Filter (on each server that it is installed on)
  • Authentication Extender

We need to stop these services in order to make sure that no changes are done by users in the system to the database while migrating.


Copying the database

1. Open oldsql  SQL Server Manager right click Access Portal > Task > Backup:

2. Make sure that the right database is selected and that you have access to the backup location:

press OK to start the backup process.

3. Transfer the backup file to the new SQL server.

4.Connect to your newsql server right click Databases > Restore Database:

5. Select the device in the source selection and then browse to your file location:

Press OK to start the restore process.

So far we have just copied the content of the old SQL server to the new one.
Now we will edit our connection strings to match the new change in the environment.

Add users to SQL server

1. In the old SQL server copy the following file to new SQL server:

Agat\SphereShield.Setup\Payload\SqlScripts\FreshInstall.CreateDatabaseUsers.sql

2.Edit the file and add the following lines to end of it:

SET @scriptToExecute = REPLACE(REPLACE(N'USE [{DB_NAME}]
EXEC sp_change_users_login ''Auto_Fix'', {DB_USER_NAME}', N'{DB_NAME}', @dbName), N'{DB_USER_NAME}', @dbUserName)
EXECUTE (@scriptToExecute)

SET @scriptToExecute = REPLACE(REPLACE(N'USE [{DB_NAME}]
EXEC sp_change_users_login ''Auto_Fix'', {DB_USER_NAME}', N'{DB_NAME}', @dbName), N'{DB_USER_NAME}', @dbNotificationName)
EXECUTE (@scriptToExecute)

Run the script and make sure that the AccessPortalUser and AgatDBNotificationUser have been added to your SQL server and you can log in with it:

change the AccessPortalUser password

Adding connection strings to the various components

LAC + EWS Protector

1. Open the test_db UDL file located at:

Agat\SphereShield.Setup\Payload\Bastion

2. Try to connect to your new database using the AccessPortalUser:

3. Open the test_db with notepad and copy your connection string

4. Change the connection string value in the Lync_Access_Control.xml file located at:

Agat\Bastion\filters\<filtername>\Lync_Access_Control.xml

The connection string should look like:

<db connStr="Provider=SQLOLEDB.1;Persist Security Info=True;User ID=AccessPortalUser;Initial Catalog=AccessPortal;Data Source=NEWSQL\NEWSQL;Password=1234;"/>

5. Change the connection string value in the EWS_Protector.xml file located at:

Agat\Bastion\filters\<filtername>\EWS_Protector.xml

The connection string should look like:

<db connStr="Provider=SQLOLEDB.1;Persist Security Info=True;User ID=AccessPortalUser;Initial Catalog=AccessPortal;Data Source=NEWSQL\NEWSQL;Password=1234;"/>

**Please make sure that the password field and value is at the end of the string**

Restart the Bastion Reverse Proxy service.

Admin Portal

1. Rename the Access Portal connection strings configuration file (connectionStrings.config) located at:

inetpub\AccessPortal\configuration

2.Copy the fresh configuration file from

Agat\SphereShield.Setup\Payload\AccessPortal\configuration

to the Access Portal configuration folder.

3. Edit the connection string. Your fresh string should like so:

<connectionStrings>
<add name="AccessPortalEntities" connectionString="data source=SERVER-NAME;initial catalog=AccessPortal;user id=AccessPortalUser;password=USER-PASSWORD;multipleactiveresultsets=True;application name=AccessPortal" providerName="System.Data.SqlClient" />
</connectionStrings>

you need to edit to following values to match your environment:

  • data source - SQL server instance name
  • initial catalog - Access Portal DB name
  • user id - Access Portal user
  • password - Access Portal user's password

4. Restart the IIS server using the command:

iisreset

Important Note

The Admin Portal encrypts the connection strings configuration file when it's being loaded up.
Your current file will look like that:

a fresh configuration file should look like:

Sip Filter

1. On each server that the SIP filter is installed on edit the SIP filter yaml file (AgatSfbSipFilter.yaml) located at:

Agat\SipFilter

2. Edit the connection string value to match your environment:

connection-string: Data Source=<<SQLSERVER>>;Initial Catalog=<<DataBaseName>>;Persist Security Info=True;User ID=<<username>>;Password=<<password>>

*** Important Note***

The SIP filter encrypts the configuration file when it's being loaded up.
Your current file will look like that:

a fresh yaml should look like:

3. Restart the SIP Filter Service

SphereShield Agent

1. If your SphereShield Agent's version is 1.3 and newer you need to add a connection string to the config file.

2. Navigate to your SphereShield  Agent folder and open the config file using notepad located at:

Agat\SphereShieldServiceAgent\AgatSphereShieldServiceAgent.config

3. Edit the connection string to match your DB:

<add key="ConnectionString" value="Data Source=[SQLSERVER];Initial Catalog=[DataBaseName];Persist Security Info=True;User ID=[username];Password=[password]" />

   A working connection string will look like that:

<add key="ConnectionString" value="Data Source=NEWSQL\NEWSQL;Initial Catalog=AceessPortal;Persist Security Info=True;User ID=AccessPortalUser;Password=123456" />

4. Restart the SphereShield Agent service