Configure the Microsoft SQL Server target

Complete the procedure below to configure the Microsoft SQL Server target and test your connection to the SQL database.

Create the Microsoft SQL Server target before you configure it. For more information, see Add a publish target.

Note: Ensure that the service account that runs PI Integrator Framework service (or the SQL user if you are using SQL authentication) has the following permissions on the Microsoft SQL Server:

Server level – CONNECT SQL, VIEW ANY DATABASE

Database level – CONNECT, CREATE TABLE

All schemas, including the dbo schema – SELECT, ALTER, INSERT, DELETE

1. On the Administration page, click the Targets tab. Then select the target in the Publish Targets list.

   Note: If you have already selected your target, skip to the next step.

2. Configure the Microsoft SQL Server target with the following parameters:

   Parameter	Description
   Hostname	Host name and instance (Hostname\Instance) of the machine on which SQL Server is running.
   SQL Authentication Username	Username used to connect to SQL Server (Only specify if using SQL Server Authentication. If using Windows integrated security, the PI Integrator for Business Analytics service account is used.)
   SQL Authentication Password	Password used to connect to SQL Server (Only specify if using SQL Server Authentication. If using Windows integrated security, the PI Integrator for Business Analytics service account is used.)
   Publishing Timeout	Time allotted for publishing data to the target before the PI Integrator Framework service times out. The default is 30 seconds.
   Use High Availability	(Optional) Connect to a SQL Server that supports high availability and failover clusters. Use High Availability turns on the MultiSubnetFailover connection property on in the connection string. For more information on this property, refer to the Microsoft web site.
   Database	Name of database in SQL Server in which tables are located.
   Schema	SQL schema associated with the tables where the data is stored.

3. Click Connect to verify that you can connect to the SQL database.

4. Click Save Changes.

5. Give users access to the Microsoft SQL Server target. For more information, see Grant access to targets.