A Microsoft SQL Server database is required at the time of Privileged Identity installation and serves as the software's storage and configuration data store. This database stores management sets, system information, account information, stored passwords, event sinks, answer files, email files, and more.
You can implement a new instance on an existing database server, or you can set up a new database server altogether. We recommend not sharing database instances with other applications. We also recommend placing the database on a system separate from other Privileged Identity components to keep the encrypted data segregated from the encryption key.
Supported SQL Versions for Production Environments
We recommend using the most current version of SQL Server available. If you must use an older version, any version from SQL Server 2012 or later is supported, including Azure SQL Database.
Microsoft Azure SQL Database requires Privileged Identity to use the latest version of the Microsoft SQL Native Client (not supplied with product download).
Both 32-bit and 64-bit versions of Microsoft SQL Server are supported. Standard and Enterprise editions are supported.
We also recommend installing SQL Server Native Client Version 11 or higher on all of the servers hosting the following PI components:
- Primary and any secondary Admin Consoles
- PI Web Services and Web App Instances
- Data Store
- Zone Processors
- All Deferred Processors (stand-alone or on the same server with a Admin Console)
- All other servers running scripts, customizations, or integrations that connect to the PI Data Store (SQL Server)
The latest version of the SQL Server Native Client can be found at:
Supported SQL Versions for Test Environments Only
Microsoft SQL Server Express is a lightweight version of SQL Server that is available for free download from the Microsoft web site. SQL Express should be used for testing scenarios only. If you must use SQL Server Express for testing, any version from SQL Server 2012 Express or later is supported.
Using SQL Express will impact performance, scalability, and high availability options, and disaster recovery options.
SQL Express configures itself to a random port number during installation. The port number is required to complete the installation of Privileged Identity. See Microsoft documentation for details.
Components Requiring Data Store Access
The following components require access to the database:
- Management Console
- Web Service
- Deferred Processor / Zone Processors
Data Store Authentication
The following methods may be used to authenticate to a Microsoft SQL database:
- Local SQL Account Authentication / Explicit Database Authentication
- Integrated Windows Authentication
Whichever method you configure in the management console at the time of component deployment will also be the method used by the web application, the deferred processor, and the zone processors.
While you may use either authentication method, we recommend Integrated Windows Authentication, as this allows for additional logging and permits more granular control over who can access stored information. If you choose SQL authentication instead, all access to the database server happens in the context of the SQL account rather than the account of the user performing the action.
Whichever method you choose, you must provide the SQL account, the Windows user account, or the Windows group with access to the Privileged Identity database.
Data Store Permissions
If using a dedicated instance of Microsoft SQL, grant:
- SYSADMIN = server role
- Control Server = database server right
This allows granted users the rights to perform all actions within that instance of SQL, including creating databases, storing procedures, and using all other features in the main application, as well as performing backup and restoration.
This allows granted users the rights to perform all actions with that instance of SQL, including:
- Creating databases
- Storing procedures
- Using all features in the main application
- Performing backup and restoration
If you don't want or are not permitted to grant SYSADMIN or Control Server to the SQL instance, then the database administrator must create the Privileged Identity database beforehand. The SQL account or Windows user/group must be granted the following roles/rights over the Privileged Identity database:
- DBO = user role
- db_datareader = user role
- db_datawriter = user role
- db_ddladmin = user role
- EXECUTE = database permission
- CREATE TABLE = database permission required during install and upgrade
- CREATE VIEW = database permission required during install and upgrade
If you are using explicit database permissions rather than granting SYSADMIN or DBO, then once the account has been granted the db_ roles above, you must grant the remaining permissions using SQL statements such as GRANT EXECUTE TO username.
Additionally, Privileged Identity can use the performance recommendations made by SQL Server for defragmentation, auto-index creation, etc. To do so, the SQL account or Windows user/group must be granted View Server State on the host SQL server.
If View Server State is not granted, a database administrator must regularly tune the product database, or performance will decrease over time.