Add permissions to the PI Vision database for web services

Prerequisites

It is necessary to follow the procedures on this page if either you use Azure SQL Database for your PI Vision database, or if you use on-premises SQL Server for your PI Vision database and you created the database using the Go.ps1 script without specifying the Windows service account when running the script.

You can skip this section if you are using on-premises SQL Server and Windows Integrated Security for PI Vision web services to connect to the PI Vision SQL database, and you also either created the PI Vision SQL database via the Administration website or by specifying the Windows service account to run the Go.ps1 script.

Introduction

The user or identity that PI Vision web services uses to connect to the PI Vision SQL database must have permissions to the database to run the application. That user can be:

• On-premises SQL Server: Windows or SQL user.

• Azure SQL Database: SQL user, Microsoft Entra user, or a Microsoft Entra System or User Assigned Managed Identity.

  SQL User: SQL authentication cannot be used to assign permissions to a Microsoft Entra user identity.

  System Assigned Managed Identity: This option can be used if the machine on which you are running the commands in the next steps is an Azure-hosted VM and its System Assigned Managed Identity is enabled. Typically, this machine is the server where PI Vision is installed.

  Note: The name of the system-assigned service principal is always the same name as the Azure resource it's created for. For more information, refer to Managed Identity Types.

  User Assigned Managed Identity: This option can be used if the machine on which you are running the commands in the next steps is an Azure-hosted VM and the User Assigned Managed Identity is assigned to it. Typically, this machine is the server where PI Vision is installed.

Choose whether to connect to the PI Vision SQL database via Microsoft SQL Server Management Studio or Powershell with sqlcmd in order to assign the permissions, and follow the appropriate set of instructions below based on your choice.

Add permissions using Microsoft SQL Server Management Studio

1. Open Microsoft SQL Server Management Studio.

2. Connect to the SQL Server instance that stores the PI Vision SQL database using your preferred authentication method.

3. Run the appropriate queries below based on your authentication method. To do so, start by expanding Databases, right-click either master or the PI Vision database (depending on which database you are instructed to run the query against below), and select New Query.

   Windows authentication

   If PI Vision web services will connect to SQL using Windows authentication (supported for on-premises SQL server and Amazon RDS for SQL Server), create the login if it does not already exist, and assign it the db_PIVision database role, by running the following queries against the PI Vision database:

   CREATE LOGIN "<domain>\<Application Pool ID>" FROM WINDOWS

   CREATE LOGIN "PIVisionService" FROM WINDOWS;

   ALTER ROLE db_PIVision ADD MEMBER "PIVisionService"

   SQL authentication

   f PI Vision web services will connect to SQL using SQL authentication (not recommended), create the login if it does not already exist. Enter and run the following queries against the master database:

   CREATE LOGIN [<PIVisionServicesSQLUserName>] WITH password='<Password>';

   CREATE USER [<PIVisionServicesSQLUserName>] FOR LOGIN [<PIVisionServicesSQLUserName>];

   Then, enter and run the following queries against the PI VIsion database:

   CREATE USER [<PIVisionServicesSQLUserName] WITH password='<Password>';

   ALTER ROLE db_PIVision ADD MEMBER [<PIVisionServicesUserOrIdentityName>];

   All other authentication methods

   For all other authentication methods, enter and run the following queries against the master database:

   CREATE LOGIN [<PIVisionServicesUserOrIdentityName>] FROM EXTERNAL PROVIDER;

   CREATE USER [<PIVisionServicesUserOrIdentityName>] FOR LOGIN [<PIVisionServicesUserOrIdentityName>];

   Then, enter and run the following queries against the PI Vision database:

   CREATE USER [<PIVisionServicesUserOrIdentityName>] FROM EXTERNAL PROVIDER;

   ALTER ROLE db_PIVision ADD MEMBER [<PIVisionServicesUserOrIdentityName>];

Add permissions using Command Prompt with sqlcmd

4. Open Command Prompt.

5. Connect to the AVEVA PI Vision SQL database via sqlcmd using your preferred authentication method (refer to Authenticate with Microsoft Entra ID in sqlcmd for further examples), create a database user for the user or identity that AVEVA PI Vision services will use to connect to the Azure SQL database, and assign it the db_PIVision role to give it minimum database permissions.

   To connect to an on-premises SQL Server, Azure SQL server, or Amazon RDS for SQL Server with SQL Server authentication and create and assign permissions to a SQL database user, run the following commands and enter the password for the SQL user you specified in <SQLUserName> when prompted:

   sqlcmd -S <SQLServer> -D <PIVisionDB> -U <SQLUserName> -Q “CREATE USER [<PIVisionServicesSQLUserName>] WITH PASSWORD '<password>'; ALTER ROLE db_PIVision ADD MEMBER [<PIVisionServicesSQLUserName>]”

   Example:

   sqlcmd -S my-azure-sql.database.windows.net -D PIVision -U MySQLUserName -Q “CREATE USER [svcpivision] WITH PASSWORD ‘abcd123’; ALTER ROLE db_PIVision ADD MEMBER [svcpivision]”

   To connect to an Azure SQL server with Microsoft Entra interactive authentication, run the following commands and enter the Microsoft Entra user’s password when prompted in a web browser:

   sqlcmd -S <AzureSQLURL> -D <PIVisionDB> -G -U <MicrosoftEntraUserName> -Q “CREATE USER [<PIVisionServicesUserOrIdentityName>] FROM EXTERNAL PROVIDER; ALTER ROLE db_PIVision ADD MEMBER [<PIVisionServicesUserOrIdentityName>]”

   Example:

   sqlcmd -S my-azure-sql.database.windows.net -D PIVision -G -U user@domain.com -Q “CREATE USER [MyManagedIdentity] FROM EXTERNAL PROVIDER; ALTER ROLE db_PIVision ADD MEMBER [MyManagedIdentity]”

   To connect to an on-premises SQL Server or Amazon RDS for SQL Server with Windows authentication and create and assign permissions to the Windows account used to run PI Vision web services, run the following commands:

   sqlcmd -S <SQLServer> -D <PIVisionDB> -E -Q "CREATE LOGIN ' PIVisionService' FROM WINDOWS; ALTER ROLE db_PIVision ADD MEMBER ' PIVisionService'"