Migrating Data from Local MS SQL to Microsoft Azure SQL Database

Password Manager Pro allows you to use Microsoft Azure SQL, a cloud database provided as a part of Microsoft Azure, as a backend database. Migrate all your existing data from the local MS SQL database to the Microsoft Azure SQL database to benefit from a fully-managed database service.

Advantages

The foremost advantage of using a cloud database is that it can be accessed anytime from anywhere as it runs on the Cloud computing platform. Microsoft Azure, an intelligent database as a service and powered by AI, learns your workload patterns and optimizes automatically, thereby resulting in cost optimization. It is a competent database service with salient features, such as built-in availability SLA, dynamic scaling, automatic performance, etc.

This document explains in detail the process of migrating your data from the local Microsoft SQL database to the Azure Microsoft SQL database in Password Manager Pro.

Steps Required

The process of migrating data from Local MS SQL to Microsoft Azure SQL database involves three steps. The first step is to transfer the data from the local MS SQL database to the Microsoft Azure SQL database. Next, encrypt the data uploaded to the Microsoft Azure SQL database. Then, the final step is to import an SSL certificate into the Password Manager Pro server.

This document walks you through the below topics:

  1. Migrating Local MS SQL Data
  2. Encrypting Azure Database
  3. Importing SSL Certificate

1. Migrating Local MSSQL Database

  1. Open the SQL Management Studio and select local MSSQL Database and execute the below queries:

    ALTER PROCEDURE [dbo].[OptimizedInventoryProc] (@SQL1 varchar(max) ,@SQL2 varchar(max),@SQLSTR varchar(max)) AS BEGIN SET NOCOUNT ON; CREATE TABLE #PTRX_DUMMYGROUPPASSWORDS ( RESID BIGINT,ACCID BIGINT,USERID BIGINT,UNIQUEID BIGINT ); END


    ALTER PROCEDURE [dbo].[PasswordActionNotificationProc] (@SQL1 varchar(max),@SQL2 varchar(max)) AS BEGIN SET NOCOUNT ON; DECLARE @SQl VARCHAR(MAX) EXEC(@SQL2) EXEC(@SQL1) SET @SQL ='SELECT TOP 1 * from Ptrx_DummyGroupNotification' EXEC(@SQL) SET NOCOUNT OFF; END


    DROP SYMMETRIC KEY PMP_SYM_KEY DROP CERTIFICATE PMP_CERT DROP MASTER KEY

  2. Now, right click the database and navigate to Task >> Deploy Database to Microsoft Azure SQL Database.

  3. Click Next in the dialog box that appears.

  4. Mention the required details and click Next.

  5. Click Finish.

2. Encrypting Azure Database

  1. Now, in SQL Management Studio, connect the database to Azure and execute the following queries:

    ALTER PROCEDURE [dbo].[OptimizedInventoryProc] (@SQL1 varchar(max) ,@SQL2 varchar(max),@SQLSTR varchar(max)) WITH ENCRYPTION AS BEGIN SET NOCOUNT ON; CREATE TABLE #PTRX_DUMMYGROUPPASSWORDS ( RESID BIGINT,ACCID BIGINT,USERID BIGINT,UNIQUEID BIGINT ); END


    ALTER PROCEDURE [dbo].[PasswordActionNotificationProc] (@SQL1 varchar(max),@SQL2 varchar(max)) WITH ENCRYPTION AS BEGIN SET NOCOUNT ON; DECLARE @SQl VARCHAR(MAX) EXEC(@SQL2) EXEC(@SQL1) SET @SQL ='SELECT TOP 1 * from Ptrx_DummyGroupNotification' EXEC(@SQL) SET NOCOUNT OFF; END


    CREATE MASTER KEY ENCRYPTION BY PASSWORD='<Password in masterkey.key file>' CREATE CERTIFICATE PMP_CERT WITH SUBJECT='Created by ManageEngine'; CREATE SYMMETRIC KEY PMP_SYM_KEY WITH ALGORITHM = AES_256, KEY_SOURCE = 'ZOHO_Key_Source', IDENTITY_VALUE = 'ZOHO_Identity_Value' ENCRYPTION BY CERTIFICATE PMP_CERT; OPEN SYMMETRIC KEY PMP_SYM_KEY DECRYPTION BY CERTIFICATE PMP_CERT;

  2. Open database_params.conf file from the conf folder and replace the <%DNS_NAME%> with Microsoft Azure SQL instance url and also modify the username and password.

3. Importing SSL Certificate

In Windows:

  1. Download the Azure Portal's certificate from browser and import mycertfile.cer using the command importCert.sh/bat.

In Linux:

  1. Download the Azure Portal's certificate from browser or create a certificate by executing the following command:

    openssl s_client -showcerts -connect <%DNS_NAME%>:443 </dev/null 2>/dev/null|openssl x509 -outform PEM >mycertfile.cer

  2. Import mycertfile.cer using the command importCert.sh/bat.

You have successfully migrated from Local MSSQL Database to Microsoft Azure SQL Database. Now, start the Password Manager Pro server and connect to the Password Manager Pro web client to retrieve the passwords.

Top