Search
Generic filters
Exact matches only

Understand SQL Server Security

The Visual KPI Web Service manages communications with the Visual KPI database. During installation, the Visual KPI Web Service and the Visual KPI database are configured to allow for this communication to occur in a secure manner, thereby assuring a secure connection.

The Web Service is assigned to one of the application pools also created during installation.

The application pool’s Identity is set to Predefined and uses the NT AUTHORITY\NETWORK SERVICE account. In the Visual KPI database, a SQL Server Login and VisualKPI database User are created for the NETWORK SERVICE account.

Note: If you choose to use an application pool Identity other than a built-in account, the account must be added to the IIS_WPG local users group. If not, you may see the “server not available” message when attempting to access the database. Additionally, if the user account password has a timeout defined, the interface will become unavailable after the password’s timeout period.

SQL Server Login Account

A different account can be used for the Application Pool’s Identity, but it must adhere to the following requirements:

  • The account must be mapped to a SQL Login account, with permission to connect to the Visual KPI database.
  • The Login account should be a member of the database role: db_VisualKPIRole. The db_owner database role will also work but is not necessary.
  • The db_VisualKPIRole has permission to run all of the Visual KPI stored procedures, but it will not have permissions on any other SQL Server object such as a table or a view.
  • If the Login account is not mapped to the db_VisualKPIRole, it must be granted the Execute permission on all of the non-system stored procedures in the Visual KPI database.

SQL Server Security for Interfaces

SQL Server can be configured to support one of two types of Authentication modes: Windows or SQL Server and Windows.

Windows authentication requires a Windows domain account to connect to the SQL Server.

SQL Server and Windows authentication allow either a Windows domain account or a SQL Server account to connect to SQL Server. You need to know which authentication method your SQL Server is configured to use.

Using Windows Authentication for SQL Server Security

If your SQL Server interface is connecting to a remote SQL Server that uses Windows authentication, you need to ensure that the domain account used in the Application Pool has the proper access to the remote SQL Server.

Verify the following are set correctly:

  • The interface’s Application Pool has its Identity set to a Windows domain account
  • The interface’s connect string uses Integrated Security
    Example: “Server=<servername>[\<instancename>];Database=<databasename>;
    Trusted_Connection=yes;”
  • A SQL Server Login account exists that maps to the domain user used as the application pool’s Identity, or to a domain group that includes this domain user, AND this SQL Server Login account has:
    • permission to connect to the database
    • been granted access to the appropriate access to the Stored Procedures and/or Tables

Using SQL Server and Windows Authentication

If your SQL Server interface is connecting to a remote SQL Server that uses SQL Server and Windows authentication, you need to ensure that the SQL account specified in the connect string has the proper access to the remote SQL Server.

Verify the following are set correctly:

  • The connect string specifies a SQL Server Login account
  • The interface’s connect string does not use Integrated Security
    Example:
    “Server=<servername>[\<instancename>];Database=<databasename>;Trusted_Connection=no;UID=<userid>;PWD=<password>;”
  • The SQL Server Login account has:
    • permission to connect to the database
    • been granted access to the appropriate access to the Stored Procedures and/or Tables

Learn more

Troubleshooting Virtual Directory Issues

Was this article helpful?

Related Articles