Creating and Using SQL Scripts

You can use SQL scripts in Secret Server to automate specific tasks. You can configure a SQL script as a dependency of a secret and run after the password is successfully changed on the secret.

Creating a SQL Script

1. From the Administration Menu, click Scripts.
2. Click the SQL tab on the Scripts page.
3. Click the Create New button.
4. Type the name, description, and script in the dialog box.
5. Click the OK button.
6. Using SQL scripts as dependencies requires that Remote Password Changing is turned on, so ensure that this is enabled on the Remote Password Changing page.

Using Parameters

Where SQL scripts are used, we provide an arguments text box. It is often beneficial to assign variables to other more meaningful variables.

Examples

SQL

UPDATE TABLE cmsuser
SET password = PWDENCRYPT(@Password) WHERE username = @Username;

MySQL

UPDATE TABLE cmsuser
SET password = PASSWORD(?) WHERE username = ?;

PostgreSQL

UPDATE TABLE cmsuser
SET passwd = CRYPT(?, GEN_SALT('sha256')) WHERE username = ?;

ODBC

UPDATE TABLE cmsuser
SET passwd = $Password WHERE username = $Username;

Basic Oracle PL/SQL

INSERT INTO cmuser values (:username, :password);

Advanced Oracle PL/SQL

EXECUTE IMMEDIATE 'alter user '|| :username ||' identified by "' || :password || '"';

Returning Errors

In situations where the script should fail given specific conditions, the script should explicitly throw an exception. When an exception is thrown, the script stops running and the failure is recorded in the system Log. The script is considered to have successfully run if no errors or exceptions occur while processing.

Examples

SQL

RAISERROR(N'ERROR: %s', 14, 1, N'Failure');

MySQL

SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'ERROR: Failure';

PostgreSQL

DO language plpgsql $$
BEGIN
RAISE EXCEPTION 'ERROR (14)';
END
$$;

ODBC

RAISERROR(N'ERROR: %s', 14, 1, N'Failure');

SQL Example

An issue that occurs in SQL Server database environments is when a linked database is set up with a credential and that credential's password changes. To counter this, you can set up a SQL script to run as a dependency after the password change occurs to drop and recreate the link. Note that you may need to edit the option depending on the desired linked server configuration.

Example

SQL

EXEC master.dbo.sp_dropserver @server=@MACHINE, @droplogins='droplogins'
EXEC master.dbo.sp_addlinkedserver @server = @MACHINE, @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=@MACHINE,@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=@MACHINE,@useself=N'False',@locallogin=@LOCALUSERNAME,@rmtuser=@REMOTEUSERNAME,@rmtpassword=@PASSWORD

Parameters

• @MACHINE The machine or instance of the server where the linked database exists. For instance, SERVER\SQL2014.
• @LOCALUSERNAME The local login on the server where the linked database is configured.
• @REMOTEUSERNAME The username that is set in the linked database's security info for connecting to the linked database. This should be the username of the secret that the dependency is on.
• @PASSWORD This will be the new password after the SQL account's password is changed.