Microsoft SQL server

This topic describes the Microsoft SQL server plugin.

Automatic password management is supported on Microsoft SQL server accounts on IPv4 and IPv6.

Supported Platforms

The CPM supports remote password management on a Microsoft SQL Server on the following platforms:

Microsoft SQL Server versions 7, 2008, 2012, 2014, 2016, 2017, 2019

Required Drivers

The following driver is required on the CPM machine:

Microsoft SQL Server 2014, 2016, 2017, 2019, 2022
ODBC Driver 18.3 for SQL Server
Microsoft SQL Server 2012, 2014, 2016, 2017, 2019, 2022
ODBC Driver 17.10 for SQL Server
Microsoft SQL Server 2016, 2017, 2019, 2022
ODBC Driver 13.1 for SQL Server
Microsoft SQL Server 2008, 2012, 2014
ODBC Driver 11 for SQL Server

Make sure that the Microsoft SQL server ODBC driver is installed on the machine that runs the CPM. If this driver is not installed, you can download it from the Microsoft downloads website.

ODBC Support

The machine that runs the CPM must support ODBC, version 2.7 and higher. If the machine does not support this version of ODBC, download the latest MDAC_typ.exe from the Microsoft downloads site.

Platform

In the Platform Management page, make sure that the following target account platform is displayed:

Microsoft SQL Server

Connection Methods

This plugin supports the following connection methods to connect to remote databases:

DSN
Connection string (DSN-less)

Connect to the Remote Database with DSN

  1. Create a System DSN for each database in the CPM machine.
  2. Use the testing option in the DSN to test the connection between the CPM machine and the database server.

     
    • Make sure that the DSN is a system DSN and not a User DSN.

    • Use either the IP address or FQDN format for the Server parameter.

  3. Recommended: Secure the connection with SSL/TLS.

Connect to the Remote Database with a Connection String (DSN-less)

  1. In the Additional Policy Settings section of the platform, check the values of the following required parameters:

Parameters Description
ConnectionCommand The Connection String that will be used to connect to the database through ODBC.
This parameter is required if the DSN parameter is not supplied either at policy level or password level, indicating that the connection method is DSN-Less.

The Connection String template may contain any number of the following variables enclosed with ‘%’ (percentage) sign. These variables will be replaced during run time with the appropriate values:

Variable Replaced by … Taken from
USER The user name. Password object properties
OLD PASSWORD The current password. Password object properties
LOGON PASSWORD The password that the plugin uses for logon. The plugin automatically detects the password to use for logon. This is either the current password or the password of the reconciliation account. Password object properties
or
Reconcile account password object properties
ADDRESS

The address of the database server where the password is used. This parameter is required. If there are several instances on the same machine, specify the instance as part of the address as follows:
ADDRESS\INSTANCE. The instance name is case-sensitive and must be specified exactly as it appears.

The address can be specified as the IP address or a Fully Qualified Domain Name (FQDN). For example, mycompany.com.

Password Object properties or, if not defined there, from the ExtraInfo section of the platform.
PORT The port used by the CPM to access the remote machine. Password Object properties or, if not defined there, from the ExtraInfo section of the platform.
DATABASE The database name. Password Object properties or, if not defined there, from the ExtraInfo section of the platform.
RECONCILE USER The name of the user who will replace the invalid password with the new password. Reconcile account password object properties
RECONCILE PASSWORD The password of the user who will replace the invalid password with the new password. Reconcile account password object properties

The following example displays a valid connection string on a Microsoft SQL Server:

ODBC Driver 18.3

Driver={ODBC Driver 18.3 for SQL Server};Server=%ADDRESS%;Database=%DATABASE%;Uid=%USER%; Pwd=%LOGONPASSWORD%;Encrypt=No;

ODBC Driver 17.10

Driver={ODBC Driver 17.10 for SQL Server};Server=%ADDRESS%;Database=%DATABASE%;Uid=%USER%; Pwd=%LOGONPASSWORD%;

ODBC Driver 13.1

Driver={ODBC Driver 13.1 for SQL Server};Server=%ADDRESS%;Database=%DATABASE%;Uid=%USER%; Pwd=%OLDPASSWORD%;

ODBC Driver 11

Driver={ODBC Driver 11 for SQL Server};Server=%ADDRESS%;Database=%DATABASE%;Uid=%USER%; Pwd=%OLDPASSWORD%;

  1. Create a temporary DSN and use the testing options in the DSN to test the connection between the CPM machine and the database server.

Recommended: Secure the connection with SSL/TLS.

Secure the connection with SSL/TLS

When using the connection methods described above, we recommend securing the connection.

CyberArk supports TLS 1.2.

To secure the connection:

  1. Enforce encryption of the connection. This can be done on the SQL Server side or on the CPM side by adding Encrypt=Yes to the connection string.

  2. Add trusted certificate enforcement on the CPM side by adding the TrustServerCertificate=No.

Configure the Password Change SQL Statement for Microsoft SQL Passwords

During installation a default SQL statement template is configured for each database vendor whose users will be managed by the CPM during a password change task.

In the Additional Policy Settings section of the platform, check the values of the following required parameters:
Parameters Description
ChangeCommand The legal SQL statement template that will be used to change the password on the required database.

The statement template can contain any number of the following variables enclosed with ‘%’ (percentage) sign. These variables will be replaced during run time with the appropriate values:

Variable Replaced by … Taken from
USER The user name Password Object properties
OLDPASSWORD The current password Password Object properties
NEWPASSWORD The new generated password Password Object properties
DATABASE The database name Password Object properties or, if not defined there, from the ExtraInfo section of the platform.
RECONCILE USER The name of the user who will replace the invalid password with the new password. Reconcile account password object properties
RECONCILE PASSWORD The password of the user who will replace the invalid password with the new password. Reconcile account password object properties
LOGON PASSWORD The password that the plugin uses for logon. The plugin automatically detects the password to use for logon. This is either the current password or the password of the reconciliation account. Password object properties
or
Reconcile account password object properties

To ensure that these variables will be used as values and not as part of the command, when possible enclose them with quotation marks, as shown in the example below.

The following example displays the command used to change a user password on a Microsoft SQL server:

sp_password "%OLDPASSWORD%", "%NEWPASSWORD%"

To reduce the risk of a security hazard, in the Additional Policy Settings section of the platform, specify the following parameters:
Parameters Description

CommandForbiddenCharacters

Characters that cannot be used in the parameters of the change command, listed in the table above.

Default values: '\/@".'{}() -|*>~!^#

CommandBlackList

Words that cannot be used in the change command, listed in the table above.

Default values: delete, drop, exec, create, alter, rename, truncate, comment, select, insert, update, merge, call, explain, lock, grant, revoke

 

 

After upgrading to v9.5, add these parameters manually and specify the default values.

Configure the Password Reconciliation SQL Statement for Microsoft SQL Passwords

During installation a default SQL statement template is configured for each database vendor whose users will be managed by the CPM during a password reconciliation task.

In the Additional Policy Settings section of the platform, check the values of the following parameters:

Parameters Description
ReconcileCommand (required) The legal SQL statement template that will be used to reconcile the password on the required database.

ReconcileIsWinAccount

Whether or not the reconcile account is a Windows domain account. Setting this parameter to ‘No’ indicates that the reconcile account is an SQL account.

Default value: No

ReconcileWinExtraConnectionString

An extra connection string for Windows accounts. This parameter will only be used if the ‘ConnectionStringFile’ parameter is used and the ‘ReconcileIsWin Account’ parameter is set to ‘Yes’.

Default value: Trusted_Connection=yes;

The statement template can contain any number of the following variables enclosed with ‘%’ (percentage) sign. These variables will be replaced during run time with the appropriate values:

Variable Replaced by … Taken from
USER The user name Password Object properties
OLDPASSWORD The current password Password Object properties
NEWPASSWORD The new generated password Password Object properties
DATABASE The database name Password Object properties or, if not defined there, from the ExtraInfo section of the platform.
RECONCILE USER The name of the user who will replace the invalid password with the new password. Reconcile account password object properties
RECONCILE PASSWORD The password of the user who will replace the invalid password with the new password. Reconcile account password object properties
LOGON PASSWORD The password that the plugin uses for logon. The plugin automatically detects the password to use for logon. This is either the current password or the password of the reconciliation account. Reconcile account password object properties

To ensure that these variables will be used as values and not as part of the command, when possible enclose them with quotation marks, as shown in the example below.

The following example displays the command used to reconcile a user password on a Microsoft SQL server:

sp_password @new="%NEWPASSWORD%", @loginame="%USER%"

To reduce the risk of a security hazard, in the Additional Policy Settings section of the platform, specify the following parameters:
Parameters Description

CommandForbiddenCharacters

Characters that cannot be used in the parameters of the reconcile command, listed in the table above.

Default values: '\/@".'{}() -|*>~!^#

CommandBlackList

Words that cannot be used in the reconcile command, listed in the table above.

Default values: delete, drop, exec, create, alter, rename, truncate, comment, select, insert, update, merge, call, explain, lock, grant, revoke

 

 

After upgrading to v9.5, add these parameters manually and specify the default values.

Add the Password

When you add the password or edit an existing one to be used on an ODBC database, specify the following properties:

For a DSN connection:
Property Description
DSN

A legal DSN name that will be used to access the database through ODBC. This parameter is required if the ConnectionStringFile parameter was not supplied in the platform, indicating that the connection method is DSN, or if the DSN parameter in the platform was not specified. This parameter supports FQDN and IP formats.

For a Connection String connection:
Property Description
Address The address of the database server that is used as the value of the ADDRESS variable in the Connection String. This parameter is required. This parameter supports FQDN and IP formats.
Port The port number of the database server. This parameter is optional.
Database The database name that will be used as the value of the DATABASE variable in the Connection String. This parameter is optional.

Password Management Features

The CPM can change and verify Microsoft SQL Server passwords on remote machines, and can reconcile these passwords with both local and domain accounts. If a password is invalid, the CPM can generate a new password and replace the invalid password on the remote machine and its corresponding password in the Password Vault. The parameters that define these tasks are in the platform. A reconciliation account password can be specified either at platform level or at account level.

For details, see Automatic account management.