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. Navigate to Admin > Scripts: PowerShell, SQL, SSH, and on the Scripts page, click Create Script.

  2. In the New Script page, type the script name in the Name text box.

  3. Type a description in the Description text box.

  4. Choose to select the Enabled checkbox.

  5. In the Script Type field select SQL.

  6. Click the Category dropdown list to select the type of script. This will determine where the script resides in Secret Server and more. For instance, the Dependency choice ensures that Remote Password Changing is turned on by enabling it on the Remote Password Changing page.

  7. Select the related Password Changer from the dropdown.

  8. Paste your script into the Script text box.

  9. When done, click Save. The new script appears in the table on the Scripts 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

Copy 

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

MySQL

Copy 

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

PostgreSQL

Copy 

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

ODBC

Copy 

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

Basic Oracle PL/SQL

Copy 

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

Advanced Oracle PL/SQL

Copy 

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

Copy 

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

MySQL

Copy 

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

PostgreSQL

Copy 

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

ODBC

Copy 

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

Copy 

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.