Configure a Fusion API (Super User)

Starting with Fusion XI, an API must be deployed and continuously run at all times. During each Fusion login attempt, this API is contacted to validate user credentials. It replaces the need for Open Database Connectivity (ODBC) configuration and enhances overall security. It is strongly recommended to create a dedicated SQL login named fusionapiuser, rather than using an existing or active Fusion login. If you are upgrading from a previous version of Fusion, create the fusionapiuser account in Fusion Administrator before performing the upgrade.

Important: The API user must exist in the PFUSER table in the Fusion database.

Activity Steps

  1. Open SQL Server Management Studio.

    The Connect to Server window displays.

  2. Enter the following Server details:
    • Server type
    • Server name
    • Select the Authentication method. If required, enter your login credentials.
    • Select the Login as sa or another sysadminuser.

      Note: You must connect as a sysadmin.

  3. Enter the following Connection Security details:
    1. Select the Encryption type.
    2. Select whether to Trust server certificate.
    3. If Strict is selected as the Encryption type, enter the Host name in certificate.
  4. Click Connect.

    The Microsoft SQL Server Management Studio software opens.

  5. Click New Query.

    The New Query window opens.

  6. Copy, paste and execute the following SQL script below.

    Note: You need to replace all occurrences of Fusion11_Demo_Central with the actual name of your Fusion Central database. If you need to change the associated password, contact a Datamine consultant for assistance.

  7. Close SQL Server Management Studio after running the SQL script.

SQL Script

-- REPLACE BOTH OCCURENCES OF "Fusion11_Demo_Central" 
-- WITH YOUR ACTUAL FUSION CENTRAL DATABASE NAME
USE [Fusion11_Demo_Central]

-- CREATE ADMIN LOGIN IN SQL INSTANCE (IF IT DOESN'T ALREADY EXIST)
IF NOT EXISTS 
    (SELECT name  
     FROM master.sys.server_principals
     WHERE name = 'fusionapiuser')
BEGIN
    CREATE LOGIN [fusionapiuser] WITH PASSWORD = 'fu5i0N11SQLapi'
    , DEFAULT_DATABASE = Fusion11_Demo_Central
    , DEFAULT_LANGUAGE = English
    , CHECK_POLICY = OFF
    , CHECK_EXPIRATION = OFF
END
GO

-- ASSIGN THE USER TO THE DB
CREATE USER [fusionapiuser] FOR LOGIN [fusionapiuser];
GO
-- ADD THE REQUIRED SERVER ROLE (REQUIRED TO GRANT ROLES TO USERS)
EXEC sp_addsrvrolemember 'fusionapiuser', 'securityadmin';
GO
-- ADD THE REQUIRED DATABASE ROLE (DB ADMIN)
EXEC sp_addrolemember 'db_owner', [fusionapiuser];
EXEC sp_addrolemember 'Role_Read_Only', [fusionapiuser];
GO    
-- INSERT REQUIRED ENTRY INTO PFUSER IF IT DOESN'T ALREADY EXIST
-- HANDLE CASE WHERE DB IS ALREADY UPGRADED AND IF DB IS STILL A FUSIONX DB
IF NOT EXISTS (SELECT userid FROM pfuser WHERE userid = 'fusionapiuser') 
BEGIN
INSERT [dbo].[pfuser] ([userid], [password], [first_name], [middle_name], [last_name], [no_unsucc_logons], [department], [cost_center], 
    [phone_number], [location], [pwd_changed_date], [no_grace_logons], [prev_pwd_1], [prev_pwd_2], [prev_pwd_3], [prev_pwd_cd], [status], 
    [audit_create_date], [audit_create_user], [audit_update_date], [audit_update_user], [email_address], [email_username], [email_password], 
    [user_role], [connect_to_central], [logon_type], [domain_name], [reference_code_id], [enforce_password_policy], [password_expiry], 
    [password_expiry_length], [password_minimum_length], [password_set_date], [password_converted], [PASSWORD_WARNING_LENGTH]) 
    VALUES (N'fusionapiuser', N'219014159189112185221247037247066197036249207072', N'Fusion', 
    NULL, N'API', 0, NULL, NULL, NULL, N'DO NOT DELETE', NULL, 3, NULL, NULL, NULL, NULL, N'A', CAST(N'2025-02-07T15:18:10.717' AS DateTime), 
    N'admin', CAST(N'2025-02-07T15:18:10.717' AS DateTime), N'admin', NULL, NULL, NULL, N'Role_Century_Admin', N'Y', N'DB', N'', 
    CAST(26 AS Numeric(12, 0)), N'N', N'N', NULL, NULL, CAST(N'2025-02-07T15:18:11.173' AS DateTime), N'Y', CAST(7 AS Numeric(3, 0)));
    update list_versions set central_list_version = central_list_version + 1 where list_name = 'pfuser';
END    
GO

Note: After running the script, a few (1 row affected) messages display. If any errors occur, review the details and make the necessary corrections. If you require assistance, contact your local Datamine office for support.