Microsoft SQL Server Hosted Applications

Connector name	`agtsqlscript`
Connector type	Executable with configuration SQL script
Type (UI field value)	Microsoft SQL Server (SCript)
Connector status / support	Bravura Security-Verified This connector has been tested and is fully supported by Bravura Security.
Installation / setup	You write a script file to define SQL commands used in the interaction between the `agtsqlscript` connector and the SQL Server database. A sample script, `agtsqlscript.cfg` is contained in the samples directory. Note that any sample script may need customizations in order to work with your system. No connector software is installed on the SQL Server Applications server or the back-end database.

Bravura Security Fabric can manage passwords for accounts defined wholly inside an SQL Server application table space using the scripted connector for SQL Server (agtsqlscript). This connector uses the SQL Server client software to bind to the SQL Server database server and issues SQL commands that you provide in a script file.

Bravura Security Fabric can perform operations on applications that have an SQL Server back-end database by connecting to the SQL Server using TDS, and either directly updating user records (for example, SQL UPDATE) or by calling stored procedures.

The following Bravura Security Fabric operations are supported by this connector:

user verify password
user change password
get server information
administrator reset password
administrator reset+expire password
expire password
unexpire password
administrator verify password
verify+reset password
enable account
disable account
check account enabled
create account
delete account
expire account
check account expiry
unexpire account
lock account
unlock account
check account lock
add user to group
delete user from group
create group
delete group
add group to group
remove group from group
update attributes
list account attributes
List:
- accounts
- attributes
- groups
- members
- computer objects
- subscribers
- update subscriber attributes/password

For a full list and explanation of each connector operation, see Connector operations.

This connector also supports custom operations, as defined in the configuration script .

Bravura Security Fabric can also manage SQL Server database-level accounts using the connector for SQL Server (agtsql). See Microsoft SQL Server for details.

Authentication

The agtsql connector supports both SQL Server Authentication and Windows Authentication. When using Windows Authentication, the SQL server and Bravura Security Fabric server should belong to the same domain, and the password reset operation is not supported.

Preparation

Before Bravura Security Fabric can manage application accounts in a SQL Server database, you must:

Install the client software.
Define a server alias (optional).
Configure a target system administrator.
Create at least one template account to provision accounts for this target.
Write a script to configure connector behavior

Installing client software

Bravura Security Fabric communicates with the SQL Server server via the TDS protocol. Before you can target the SQL Server, you must install and configure the SQL Server 2005 Service Pack 3, or 2008, or 2012 client software (connectivity only) on the Bravura Security Fabric server.

Consult the documentation included with your SQL Server client software for more information.

Defining a server alias

Optionally, you can use the SQL Server Client Network Utility to define a server alias for the SQL Server server to which Bravura Security Fabric will connect. You can then set the Bravura Security Fabric target system address to use this alias. Alternatively, if you don’t want to define an alias, you can target the SQL Server using its original server name.

Steps for defining an alias vary depending on your version of the software. Ensure that you specify the TCP/IP protocol in the configuration.

Configuring a target system administrator

Bravura Security Fabric uses a designated account (for example, psadmin) on the SQL Server target system, or a Domain account login (for example, MYAD\admin) to perform operations. The target system administrator requires SQL Server back end database privileges that grant execution of SQL commands used in the written script.

Ensure that you set and note the account’s password. You will be required to enter the login ID and password when you add the SQL Server target system to Bravura Security Fabric .

Creating a template account

Bravura Security Fabric uses template accounts as models or "blueprints" for creating new SQL Server accounts.

The steps required to create a template account depend on your application. Consult your systems administrator or application documentation for more information.

To learn how to create a template for SQL Server database-level accounts, see Creating a template account.

Writing a script to configure connector behavior

You write a script file to define SQL commands used in the interaction between the agtsqlscript connector and the SQL Server database. A sample script, agtsqlscript . cfg is contained in the samples directory. Note that any sample script may need customizations in order to work with your system.

Learn about writing script files for SQL application connectors.

Using Transactions

A script for targeting a SQL Server hosted application can use transactions to commit work only if all SQL statements are successful.

A key-value of "usetransaction" enables or disables the use of transactions. The key-value "usetransaction" is available to each operation KVGroup and is either true or false.

For example:

   "m-tech:agents:sqlscript" "1.1" = {
     "operation" "reset" = {
       ...
       "usetransaction" = "true"
       ...
     }
   ...
   }

Executing SQL statements at block level

When the connector encounters any errors in an operation which contains multiple blocks, it is able to proceed to run following SQL blocks by enabling "proceedblocks". By default this is disabled.

This flag only applies to the operations that involve changing, which include: rename, updatesubscriber, change, verifyreset, reset, lock, unlock, enable, disable, resetexpirepw, expirepw, unexpirepw, expireacct, unexpireacct, delete, groupuseradd, groupuserdelete, groupgroupadd, groupgroupdelete.

A key-value of "proceedblocks" enables or disables the use of proceeding at block level. The key-value "proceedblocks" is available to each operation KVGroup and is either true or false.

For example:

  "m-tech:agents:sqlscript" "1.1" = {
     "operation" "delete" = {
       ...
       "proceedblocks" = "true"
       ...
     }
   ...
   }

Targeting Microsoft SQL Server hosted applications

For each SQL Server server hosted application, add a target system (Manage the system > Resources > Target systems):

Type is Microsoft SQL Server (Script) .
Address uses options described in the table below.

Table 1. SQL server address configuration

Option	Description
Options marked with a are required.
Server	The IP address/domain name of the database server. Optionally, you can include the SQL Server instance name in the server name field. You must include the instance if you want to target an SQL Server instance that isn’t the default instance of the target system administrator. Alternatively, you can use the server alias . (key: server)
ODBC Driver	Select an ODBC driver available on the system (key: driver)
Instance	The value for the substitution variable _instance . It can be used to specify a database to use or a schema in the script.
Script file	The file must be in the <Program Files path>\Bravura Security\Bravura Security Fabric\<instance>\ script\ directory and describes the SQL commands used in the interaction between the connector and the database. (key: script)
Use windows authentication for connecting to database	Default is false . (key: windowsAuthentication)

The Administrator ID and Password are the login ID and password for the target system administrator you configured in configured earlier .
By default, all connectors run the Bravura Security Fabric processes on the Bravura Security Fabric server, as the local psadmin account. To enable the target system administrator to run those processes, select the Run as? checkbox.
If using Windows Authentication, check the Run as? option for the target system administrator.

The full list of target parameters is explained in Target system options .

Handling account attributes

In order for Bravura Security Fabric to manage attributes, you must first add the attributes to Bravura Security Fabric.

See Account attributes in the Bravura Security Fabric configuration documentation for more information.

Troubleshooting

If you experience any errors, verify that:

The SQL Server client software is installed on the Bravura Security Fabric server.
The SQL Server libraries are on the system-wide search path (PATH variable). If not, add the appropriate directory to the PATH environment variable and restart the Bravura Security Fabric server.
You can log into each SQL Server from the Bravura Security Fabric server using isql and the administrator ID and password you created.
You can issue an ALTER USER command on each SQL Server database to reset a user’s password when logged in with the administrative account.
You can execute the same SQL instructions interactively using isql while logged into the same database instance with the same login ID.
The Run as? option is enabled for the target system administrator when using Windows Authentication.

In this section: