Microsoft SQL Server
Connector name |
|
Connector type | Executable |
Type (UI field value) | Microsoft SQL Server |
Target system versions supported / tested | The
|
Connector status / support | Bravura Security-Verified This connector has been tested and is fully supported by Bravura Security. |
Installation / setup | No agent software is installed on the SQL Server system. |
Bravura Security Fabric can manage SQL Server database-level accounts (logins) using the connector for SQL Server (agtsql). Bravura Security Fabric can bind to an MSSQL server using its native TDS protocol. Once connected, Bravura Security Fabric can list users, validate current passwords, and administratively reset passwords by issuing SQL commands and/or calling stored procedures (for example SELECT, ALTER USER, UPDATE). Default SQL commands are included to update SQL Server user passwords.
Bravura Security Fabric can create, delete, enable, disable, modify, and rename system users in any specified MSSQL server. It creates new MSSQL users by cloning existing ones, copying and adjusting their group memberships and tablespace rights in the process. It can also directly manage the membership of MSSQL users in MSSQL groups.
The following Bravura Security Fabric operations are supported by this connector:
user verify password *
user change password
get server information
administrator reset password
check password expiry
enable account
disable account
check account enabled
check account lock
create account
delete account
add user to group
delete user from group
update attributes
list account attributes
check account expiry
run command
List:
accounts
attributes
groups
members
computer objects
Note
Bravura Security Fabric group operations, such as "add user to group", work for what SQL Server refers to as server roles. There is no group security object in SQL Server.
For a full list and explanation of each connector operation, see Connector operations.
Bravura Security Fabric can manage passwords for accounts defined wholly inside an application table space, rather than database-level accounts, using the scripted connector for SQL Server (agtsqlscript). See Microsoft SQL Server Hosted Applications for details.
Bravura Security Fabric can also discover SQL server instances and retrieve attributes from the discovered instances.
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.
Preparation
Before Bravura Security Fabric can manage database-level accounts on SQL Server, you must:
Install the client software.
Configure a target system administrator.
Create at least one template account
Alternatively, you can grant access by Windows group membership, in which case you can skip the above requirements.
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, 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 define a server alias for the SQL Server server that Bravura Security Fabric will connect to. 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.
Assign the target system administrator the System Administrators (sysadmin) server role. 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. This section provides examples to illustrate how you can create a template account on your SQL Server server.
Login types
The types of logins are as follows:
Windows User – a user whose account already exists in a Windows NT or Microsoft Active Directory domain and is granted access to the SQL Server. See Creating a template account for a Windows User login .
Standard – a user whose account is stored on the SQL Server and is separate from any Windows domain. See Creating a template account for a Standard login .
Windows Group – the name of a Windows server or Active Directory group. All members of the group can connect to SQL Server using Windows authentication.
This type of login cannot be managed by
agtsql. Instead you should use the Microsoft Windows server or Active Directory target system types. See Granting access by Windows group membership .
Creating a template account for a Windows User login
Use the following procedure to grant a Windows user account access to a SQL Server system. See your SQL Server system administrator or SQL Server documentation for more information if required.
To create a template account for a Windows user:
Start SQL Server Enterprise Manager.
Expand Console Root > Microsoft SQL Servers > <server group> > <server name> > Security.
Where <server name> is the name of the SQL Server target system, and <server group> is the server group to which the target belongs.
Right-click Logins, then select New Login.
The SQL Server Login Properties - New Login window displays.
Select the General tab if it does not have focus.
Ensure the Windows Authentication method is selected.
In the Name field, type the Windows user ID for the account being used as the template, or click the Browse button to the right of the field and select the user ID.
The Windows Authentication Domain automatically completes depending on what is entered in the Name field.
For Security access, ensure the Grant access radio button is selected.
The Default database is already set, but if required, you can change it to a database of your choice.
Select the Server Roles tab and assign the appropriate server roles in the Server Roles list box.
Click the Database Access tab and assign the individual databases to which the template should have access.
In the Permit in Database Role list box, select the appropriate permissions.
Click OK.
Creating a template account for a Standard login
Use the following procedure to create a standard user account on an SQL Server system. See your SQL Server system administrator or SQL Server documentation for more information.
To create a template account for a Standard user:
Start SQL Server Enterprise Manager.
Expand Console Root > Microsoft SQL Servers > <server group> > <server name> > Security.
Where <server name> is the name of the SQL Server target system, and <server group> is the server group to which the target belongs.
Right-click Logins, then select New Login.
The SQL Server Login Properties - New Login window displays.
Select the General tab if it does not currently have focus.
In the Name field, type a Standard user ID.
For Authentication, select SQL Server Authentication.
You are prompted to assign a password.
Type a password of your choice in the Password field and press Enter.
You are prompted to confirm the password.
For Security access, ensure the Grant access radio button is selected.
The Default database is already set, but if required, change it to a database of your choice.
Click the Server Roles tab and assign the appropriate server roles using the Server Roles list box.
Click the Database Access tab and assign the individual databases to which the template should have access.
In the Permit in Database Role list box, select the appropriate permissions.
Click OK.
Granting access by Windows group membership
If you are running an SQL Server in either mode, but prefer to manage the SQL Server access by Windows group membership, you can do the following:
Create a Windows server or Active Directory group for users that require access to the SQL Server database(s).
Create a template account on the appropriate Windows server or Active Directory target system.
Ensure that the template user is a member of the designated group.
Grant the newly created group access to the server by following the procedure in Creating a template account for a Windows User login . In Step 6, replace the template ID with the group name.
When a new user is created from a template user, the new user becomes a member of the group and is granted access to the SQL Server.
Targeting a Microsoft SQL Server
For each SQL Server server, add a target system (Manage the system > Resources > Target systems):
Type is Microsoft SQL Server .
Address uses options described in the table below.
Option | Description |
|---|---|
Options marked with a | |
Server | The IP address/domain name of the database server. The instance name and/or port number are required when targeting any instance other than the default one. Alternatively, use the alias you configured. (key: server) |
Use windows authentication for connecting to database | Default is false . (key: windowsAuthentication) |
ODBC Driver | Select an ODBC driver available on the system (key: driver) |
Type of accounts to list from the server | Select:
|
Trust server certificate | Default is false (key: trustServerCertificate) |
Encrypt | Encrypt connection. Default is false (key: encrypt) |
Discover DB details | Retrieve instance specific attributes. Default is false (key: probeDB) |
The syntax is as follows:
{server=<serverName>[\<serverInstance>][,<serverPort>];[windowsAuthentication=<true|false>];[driver=<nativeclient>];[accountListType=<All|Windows|SQL>];[trustServerCertificate=<true|false>];[encrypt=<true|false>];}
Administrator ID and Password are the login ID and password for the target system administrator you configured in 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 for SQL, enable the Run as? option for the target system administrator.
The full list of target parameters is explained in Target system options .
Handling account attributes
You can view the complete list of attributes that Bravura Security Fabric can manage, including native and pseudo-attributes, using in the Manage the system (PSA) module. To do this, select Microsoft SQL Server from the Manage the system > Resources > Account attributes > Target system type menu.
This section describes the attributes that Bravura Security Fabric uses to compose values, set flags, or control behavior on the SQL Server.
Server roles
SQL Server uses server roles to grant server-wide security privileges to a login. Server roles correspond to the following boolean attributes in Bravura Security Fabric :
sysadmin System Administrators
securityadmin Security Administrators
serveradmin Server Administrators
setupadmin Setup Administrators
processadmin Process Administrators
diskadmin Disk Administrators
dbcreator Database Creators
bulkadmin Bulk Insert Administrators.
By default, server roles are copied from the template user. Alternatively you can assign users to server roles using the Bravura Security Fabric group management facility.
Database roles
Each SQL Server database also contains a set of roles. Because roles are unique to each database, you must create a db_roles_<database name> attribute for each database. For example, an SQL Server install always has a database called "master". To manage roles for this database, create the attribute db_roles_master .
Create the db_roles_<database name> attribute as a multi-valued attribute. Its values list the roles that a user has in that database. The "fixed" list of possible roles are:
db_owner
db_accessadmin
db_datareader
db_datawriter
db_ddladmin
db_securityadmin
db_backupoperator
db_denydatareader
db_denydatawriter
Note
The database roles (db_roles_<database name> ) attributes are copied by default. If you want to SET one or more of the attributes, they must have a Sequence Number higher than that of the databases attribute, since a user must have access to a database before the user can have a role for that database.
Disabling and enabling accounts
You can disable and enable accounts created from a Windows domain user template. The status is handled by the accessgranted attribute.
You cannot disable accounts created from a Standard user template.
Deleting accounts
When you delete a Windows user from Bravura Security Fabric , the Windows user’s access to SQL Server is revoked. When you delete a standard user from Bravura Security Fabric , the user is deleted completely.
Additional considerations
In order for Bravura Security Fabric to create a new Windows User login for SQL Server, the user must have an account in the corresponding Windows domain. Bravura Security Fabric includes rules to ensure that when an Active Directory template and an SQL server template are selected as part of the same request, the Active Directory account is created first.
It is highly recommended that you configure Bravura Security Fabric so that users cannot select an SQL Server template without also selecting an Active Directory template, or without already owning an Active Directory account.
For example, the IDWFM REQUEST REWRITE PLUGIN can be used to validate requests when the "postselect_template" key-value is present. If the recipient does not have the Active Directory account or the Active Directory account is not present in the request, then the plugin can deny the request or modify it to include the template.
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.
The Run as? option is enabled for the target system administrator when using Windows Authentication.