PostgreSQL is installed as the database infrastructure for Princeton Identity's Identity Server solution. However, users may prefer to leverage their own Microsoft SQL Server database in place of PostgreSQL. Execute the following steps to connect Identity Server to your instance of Microsoft SQL Server (see 5.3. Other Supported Software for supported versions). If you already have a PostgreSQL database and would like to convert to Microsoft SQL, please see the instructions linked.
1. Make sure you are logged out of Identity Server.
2. Open the Windows Services app.
3. Scroll through the list to find the line named “PI Identity Server”. Right-click on that line and select “Stop”.
4. Setup the SQL Server database if creating a new one (see Microsoft article Create a Database for more information).
5. For an existing or new database, verify that the database can be logged into using SQL Server Authentication.
6. In Windows Server, open File Explorer and go to the Identity Server root directory (ex. c:/identity-server).
7. Within the root directory, locate the file named “data.properties”. (NOTE: It is recommended to create a backup a copy of this file to another location before proceeding).
8. Open the file with a text editing app (Ex: Windows Notepad).
9. The contents of the file will look something like this:
#Mon Sep 11 12:30:07 EDT 2023
data.jdbc.url=jdbc\:postgresql\://localhost/database name
data.jdbc.driverClass=org.postgresql.Driver
data.path=./data
data.jdbc.password=password
data.jdbc.user=username
Note: Ensure that this user has db_owner permission for the database specified (Security > Users > Properties > User Mapping)
10. Delete all lines except for the first one (date & time stamp).
11. Replace the deleted lines with the following (port 1433 is generally the default, unless Dynamic Ports are enabled):
data.jdbc.url=jdbc:sqlserver://localhost:1433;databaseName=DatabaseName
data.jdbc.driverClass=com.microsoft.sqlserver.jdbc.SQLServerDriver
data.path=./data
data.jdbc.password=password
data.jdbc.user=username
If Dynamic Ports are enabled, you can find this value by proceeding to:
-
- Opening SQL Server Configuration Manager
- SQL Server Network Configuration, Protocols for "your SQL instance name":
- Right-click TCP/IP, enable this if not done so already.
- Double-click TCP/IP, IP Addresses tab
- Scroll to the section labelled "IPAII". The port will be listed here.
12. Replace the “DatabaseName”, “password” and “username” entries with those associated with the database you will connect IDS to (we generally use AccessControlPI by default, but it can be any one of your choice).
13. Save and close the file.
14. Return to the Windows Services app.
15. Scroll through the list to find the line named “PI Identity Server”.
16. Right-click on that line and select “Start”.
Your Identity Server installation will now be connected to the specified Microsoft SQL Server database.