Create the Measurement Advisor SQL Login and User

In most cases, you will need to create a user in the AVEVA Measurement Advisor SQL Server and assign the appropriate roles to allow the ACM Data Connector to create objects and write data in the database.

When the AVEVA Measurement Advisor SQL Server is in the cloud, you do not need to create an additional user. Your access is determined by the CONNECT Service Access Token. For more information, see CONNECT Service Access Token for ACMsettings.json.

When AVEVA Measurement Advisor SQL Server is on-premises, you will need to create a user in the AVEVA Measurement Advisor SQL Server. This user is used by both the data connector and the request connector to access the Measurement Advisor database.

If the AVEVA Measurement Advisor SQL Server is installed in the same domain as the data connector and the request connector, then Windows Authentication can be used when connecting. To configure this, the AVEVA Apps group can be added to the AVEVA Measurement Advisor SQL Server. Ensure that the user configured to run the request and data connector services is a member of this group. With this configuration, the EncryptedMeasurementConnection string can be configured to use a trusted connection. See Encrypted Microsoft SQL Server Connection Strings for ACMsettings.json.

Use the following steps to add a login and user for the domain group:

1. Open Microsoft SQL Server Management Studio, and then connect to the AVEVA Measurement Advisor SQL Server.

2. Use these commands to add the AVEVA Apps domain group to the AVEVA Measurement Advisor SQL Server and set the permissions:

   use master

   go

   -- Replace MYDOMAIN with your domain name before executing

   CREATE LOGIN [MYDOMAIN\AVEVA Apps] FROM WINDOWS WITH DEFAULT_DATABASE=[EFM], DEFAULT_LANGUAGE=[us_english]

   go

   use EFM

   go

   -- Replace MYDOMAIN with your domain name before executing

   CREATE USER [AVEVA Apps] FOR LOGIN [MYDOMAIN\AVEVA Apps] WITH DEFAULT_SCHEMA=[dbo]

   go

   ALTER ROLE [Meas SCADA Commands] ADD MEMBER [AVEVA Apps]

   ALTER ROLE [Meas Data Entry GQ] ADD MEMBER [AVEVA Apps]

   ALTER ROLE [Meas Data Entry Meter] ADD MEMBER [AVEVA Apps]

   ALTER ROLE [Meas Data Entry Event] ADD MEMBER [AVEVA Apps]

   ALTER ROLE [Meas Data Entry Config] ADD MEMBER [AVEVA Apps]

   grant execute on scadaGQDownloadUpdateStatus to [AVEVA Apps]

   go

3. If you are installing on a distributed system with the datapump installed, also run the following commands:

   use xosapp

   CREATE USER [AVEVA Apps] FOR LOGIN [AVEVA Apps] WITH DEFAULT_SCHEMA=[dbo]

   go

   ALTER ROLE db_datareader ADD MEMBER [AVEVA Apps]

   go

If the AVEVA Measurement Advisor SQL Server is installed in a different domain than the data connector and the request connector, then SQL Server Authentication must be used when connecting. To configure this, you must create an SQL user in the AVEVA Measurement Advisor SQL Server, and this is the user that you will configure in the EncryptedMeasurementConnection string. See Encrypted Microsoft SQL Server Connection Strings for ACMsettings.json.

Note: In this case, the user must be called AVEVADataConnector.

Use the following steps to add an SQL login and user:

4. Open Microsoft SQL Server Management Studio, and then connect to the AVEVA Measurement Advisor SQL Server.

5. Open the Properties for the server, and then navigate to the Security page.

6. Select SQL Server and Windows Authentication mode.

7. Use these commands to create the user and set the permissions, being sure to set a strong password.

   use master

   CREATE LOGIN AVEVADataConnector WITH PASSWORD = '<enterStrongPasswordHere>', DEFAULT_DATABASE=[EFM], DEFAULT_LANGUAGE=[us_english]

   go

   use EFM

   CREATE USER [AVEVADataConnector] FOR LOGIN [AVEVADataConnector] WITH DEFAULT_SCHEMA=[dbo]

   go

   ALTER ROLE [Meas SCADA Commands] ADD MEMBER [AVEVADataConnector]

   ALTER ROLE [Meas Data Entry GQ] ADD MEMBER [AVEVADataConnector]

   ALTER ROLE [Meas Data Entry Meter] ADD MEMBER [AVEVADataConnector]

   ALTER ROLE [Meas Data Entry Event] ADD MEMBER [AVEVADataConnector]

   ALTER ROLE [Meas Data Entry Config] ADD MEMBER [AVEVADataConnector]

   grant execute on scadaGQDownloadUpdateStatus to [AVEVADataConnector]

   go

   exec dbo.writeUser 0, 'AVEVADataConnector', @description = 'AVEVADataConnector'

   exec dbo.permissionAdd 'AVEVADataConnector', 'Meas SCADA Commands'

   exec dbo.permissionAdd 'AVEVADataConnector', 'Meas Data Entry GQ'

   exec dbo.permissionAdd 'AVEVADataConnector', 'Meas Data Entry Meter'

   exec dbo.permissionAdd 'AVEVADataConnector', 'Meas Data Entry Event'

   exec dbo.permissionAdd 'AVEVADataConnector', 'Meas Data Entry Config'

   go

8. If you are installing on a distributed system with the datapump installed, run the following commands:

   use xosapp

   CREATE USER [AVEVADataConnector] FOR LOGIN [AVEVADataConnector] WITH DEFAULT_SCHEMA=[dbo]

   go

   ALTER ROLE db_datareader ADD MEMBER [AVEVADataConnector]

   go