How to connect to Microsoft SQL database and encyrpt user password

How to connect to Microsoft SQL database and encyrpt user password

Note: For windows authentication see KB article

For SQL Authentication see instructions below. If TLS is required, see attachements.

1) Create a clean database in SQL Server or connect to an existing SQL database

You can get username/password (SQL authentication) from your DBA (database administrator).  User should have DBO Owner.

2. In windows services,  stop the PassagePoint services

3. In c:\PPGobal\config=> delete the 'sqlcontext.xml'  

4.    Rename 'sqlcontext.xmlmssql' to 'sqlcontext.xml'Set

5. Edit the 'sqlcontext.xml' using any text editor.

6. Find the JDBC string:  jdbc:sqlserver://localhost:1433;databaseName=amber;

Update the string to connect to your Microsoft SQL Server

Example: If  you have a SQL instance: jdbc:sqlserver://sql2014\sqlexpress;databaseName=amber   

If there is no instance:
jdbc:sqlserver://sql2014;databaseName=amber

7. Update the 'amber' user name with the SQL user where

      <property name="user">
         <value>amber</value>
      </property>

8. update the password ' {TZwofiG8jts=}' with the sql user password

      <property name="password">
         <value>{TZwofiG8jts=}</value> 

To encrypt the password:
    a. open command prompt cd\ to the root of c:
    b. cd to ppglobal\config
    c. enter 'encrypt mypassword' where mpassword is your password
    d. Copy the encrypted string and replace the password



9. Save the 'sqlcontext.xml'

10. In windows services, start the 'PassagePoint Services'

NOTE: If SQL server is not Listening on the standard 1433 port, the connection will fail. 

To see the SQL server port, either contact the SQL server Admin, or open up sql server surface configuration manager, and bring up the TCP dynamic port.

In sql server surface configuration manager, under SQL Server Network configuration edit protocols and edit TCP/IP

seen below:






for encrypted string that needs a cert, use this:

jdbc:sqlserver://SQLserverNameorIP:1439;databaseName=PassagePoint;integratedSecurity=true;encrypt=true;sslProtocol=TLSv1.2;trustServerCertificate=true;</value>



    • Related Articles

    • Use MSSQL database instead of PostgreSQL

      If you want to use SQL, after PassagePoint has been installed >> go to \PPGlobal\config delete the first 'sqlcontext.xml' file. Then rename the 'sqlcontext.xml.mssql' to 'sqlcontext.xml' Here - edit the new updated 'sqlcontext.xml' You will need to ...

    • How to assign Screen Policies by User Role

      Please make sure to assign the ‘Admin’ role to the Default Screen Policy and uncheck the ‘Apply to all roles’ before adding and assigning new screen policies. User roles are limited to one screen policy, please make sure to not add user roles to ...

    • How to move PassagePont's backend SQL DB to a new SQL server

      To move PassagePoint to a new SQL server: 1) Do a full backup of the passagepoint DB on the current SQL server if you don't know which SQL server or what DB it is, see here: ...

    • How to encrypt the SQL server password in the SQLcontext.xml file

      To encrypt the password: a. open command prompt cd\ to the root of c: b. cd to ppglobal\config c. enter 'encrypt mypassword' where mpassword is your password d. Copy the encrypted string and replace the password Save the 'sqlcontext.xml'

    • How to repoint PassagePoint to a new SQL server

      PassagePoint keeps the SQL server connection string in the "sqlcontext.xml" file on the passagepoint application server. see path below: \\PPGlobal\config\sqlcontext.xml To repoint: Edit the 'sqlcontext.xml' You will need to update the database ...