Microsoft SQL Server

JDBC Connection String

jdbc:sqlserver://sql.example.com:1433;databaseName=master;encrypt=true;trustServerCertificate=true

Validate Credentials

DECLARE @SQL NVARCHAR(MAX) = N'';
SET @SQL = N'
SELECT 
    CAST(sp.name + ''@server'' AS VARCHAR(255)) AS integration_specific_id, 
    sp.name AS username, 
    sp.name AS email, 
    '''' AS given_name, 
    '''' AS family_name, 
    CASE 
        WHEN sp.is_disabled = 0 THEN ''ACTIVE'' 
        ELSE ''INACTIVE'' 
    END AS user_status, 
    CASE 
        WHEN sp.type_desc IN (''SQL_LOGIN'', ''WINDOWS_LOGIN'', ''WINDOWS_GROUP'') THEN ''USER'' 
        ELSE ''SERVICE'' 
    END AS account_type, 
    @@SERVERNAME AS server_name, 
    ''master'' AS database_name 
FROM 
    master.sys.server_principals sp 
WHERE 
    sp.type_desc IN (''SQL_LOGIN'', ''WINDOWS_LOGIN'', ''WINDOWS_GROUP'') 
';

SELECT @SQL = @SQL + N'
UNION ALL 
SELECT 
    CAST(dp.name + ''@'' + ''' + d.name + ''' AS VARCHAR(255)) AS integration_specific_id, 
    dp.name AS username, 
    dp.name AS email, 
    '''' AS given_name, 
    '''' AS family_name, 
    CASE 
        WHEN dp.type_desc IN (''SQL_USER'', ''WINDOWS_USER'', ''WINDOWS_GROUP'') THEN ''ACTIVE'' 
        ELSE ''INACTIVE'' 
    END AS user_status, 
    ''USER'' AS account_type, 
    @@SERVERNAME AS server_name, 
    ''' + d.name + ''' AS database_name 
FROM 
    ' + QUOTENAME(d.name) + '.sys.database_principals dp 
WHERE 
    dp.type_desc IN (''SQL_USER'', ''WINDOWS_USER'', ''WINDOWS_GROUP'') 
'
FROM 
    sys.databases d;

SET @SQL = 'SELECT TOP 1 * FROM (' + @SQL + ') AS AllResults;';

EXEC(@SQL);

List Accounts

DECLARE @SQL NVARCHAR(MAX) = N'';
SET @SQL = N'
SELECT 
    CAST(sp.name + ''@server'' AS VARCHAR(255)) AS integration_specific_id,
    sp.name AS username,
    sp.name AS email,
    '''' AS given_name,
    '''' AS family_name,
    CASE 
        WHEN sp.is_disabled = 0 THEN ''ACTIVE'' 
        ELSE ''INACTIVE'' 
    END AS user_status,
    CASE 
        WHEN sp.type_desc IN (''SQL_LOGIN'', ''WINDOWS_LOGIN'', ''WINDOWS_GROUP'') THEN ''USER'' 
        ELSE ''SERVICE'' 
    END AS account_type,
    @@SERVERNAME AS server_name,
    ''master'' AS database_name
FROM 
    master.sys.server_principals sp
WHERE 
    sp.type_desc IN (''SQL_LOGIN'', ''WINDOWS_LOGIN'', ''WINDOWS_GROUP'') ';

SELECT @SQL = @SQL + N'
UNION ALL
SELECT 
    CAST(dp.name + ''@'' + ''' + d.name + ''' AS VARCHAR(255)) AS integration_specific_id,
    dp.name AS username,
    dp.name AS email,
    '''' AS given_name,
    '''' AS family_name,
    CASE 
        WHEN dp.type_desc IN (''SQL_USER'', ''WINDOWS_USER'', ''WINDOWS_GROUP'') THEN ''ACTIVE'' 
        ELSE ''INACTIVE'' 
    END AS user_status,
    ''USER'' AS account_type,
    @@SERVERNAME AS server_name,
    ''' + d.name + ''' AS database_name
FROM 
    ' + QUOTENAME(d.name) + '.sys.database_principals dp
WHERE 
    dp.type_desc IN (''SQL_USER'', ''WINDOWS_USER'', ''WINDOWS_GROUP'') '
FROM sys.databases d;

EXEC(@SQL);

List Entitlements

DECLARE @SQL NVARCHAR(MAX) = N'';
SET @SQL = N'
    SELECT 
        ''ROLE'' AS entitlement_type, 
        srp.name AS integration_specific_id, 
        ''server'' AS integration_specific_resource_id, 
        1 AS is_assignable, 
        srp.name AS label 
    FROM 
        master.sys.server_principals srp 
    WHERE 
        srp.type_desc = ''SERVER_ROLE''
';

SELECT @SQL = @SQL + N'
    UNION ALL 
    SELECT 
        ''ROLE'' AS entitlement_type, 
        drp.name AS integration_specific_id, 
        ''' + d.name + ''' AS integration_specific_resource_id, 
        1 AS is_assignable, 
        drp.name AS label 
    FROM 
        ' + QUOTENAME(d.name) + '.sys.database_principals drp 
    WHERE 
        drp.type_desc = ''DATABASE_ROLE''
'
FROM sys.databases d;

EXEC(@SQL);

Find Entitlement Associations

DECLARE @SQL NVARCHAR(MAX) = N'';

SET @SQL = N'
    SELECT 
        CAST(member_principal.name + ''@server'' AS VARCHAR(255)) AS account_id, 
        role_principal.name AS integration_specific_entitlement_id, 
        ''server'' AS integration_specific_resource_id 
    FROM 
        master.sys.server_role_members srm 
    JOIN 
        master.sys.server_principals member_principal 
        ON srm.member_principal_id = member_principal.principal_id 
    JOIN 
        master.sys.server_principals role_principal 
        ON srm.role_principal_id = role_principal.principal_id 
    WHERE 
        member_principal.type_desc IN (''SQL_LOGIN'', ''WINDOWS_LOGIN'', ''WINDOWS_GROUP'') 
        AND role_principal.type_desc = ''SERVER_ROLE''
';

SELECT @SQL = @SQL + N'
    UNION ALL 
    SELECT 
        CAST(dp.name + ''@'' + ' + QUOTENAME(d.name, '''') + N' AS VARCHAR(255)) AS account_id, 
        drp.name AS integration_specific_entitlement_id, 
        ' + QUOTENAME(d.name, '''') + N' AS integration_specific_resource_id 
    FROM 
        ' + QUOTENAME(d.name) + N'.sys.database_role_members drm 
    JOIN 
        ' + QUOTENAME(d.name) + N'.sys.database_principals dp 
        ON drm.member_principal_id = dp.principal_id 
    JOIN 
        ' + QUOTENAME(d.name) + N'.sys.database_principals drp 
        ON drm.role_principal_id = drp.principal_id 
    WHERE 
        dp.type_desc IN (''SQL_USER'', ''WINDOWS_USER'', ''WINDOWS_GROUP'') 
        AND drp.type_desc = ''DATABASE_ROLE''
' 
FROM sys.databases d;

EXEC(@SQL);

List Resources

SELECT 
    @@SERVERNAME AS integration_specific_id, 
    'SQL Server Instance' AS label, 
    'SERVER_INSTANCE' AS resource_type 
UNION ALL 
SELECT 
    d.name AS integration_specific_id, 
    d.name AS label, 
    'DATABASE' AS resource_type 
FROM 
    sys.databases d;

Using Stored Procedures

Stored procedures can be utilized within queries as long as the query given to lumos returns data in the correct format. One way to do this is using table variables.

In the following examples, the app_users table is defined as this

CREATE TABLE [dbo].[app_users](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[first_name] [varchar](50) NOT NULL,
	[last_name] [varchar](50) NOT NULL,
	[uname] [varchar](50) NOT NULL,
	[status] [varchar](50) NOT NULL,
	[external_id] [varchar](50) NOT NULL,
	[custom_attribute] [varchar](50) NULL
	)

List Accounts (using a stored procedure)

Our stored procedure is defined as the following

CREATE procedure selectAllUsers
AS
SELECT * FROM testdb.dbo.app_users
GO

The query entered into Lumos should be

DECLARE @TempTable TABLE (
    id INT NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
	uname VARCHAR(50) NOT NULL,
	status VARCHAR(50) NOT NULL,
	external_id VARCHAR(50) NOT NULL,
	custom_attribute VARCHAR(50) NULL
 );
INSERT INTO @TempTable
EXEC selectAllUsers;

SELECT CAST(id AS varchar(50)) AS integration_specific_id, first_name AS given_name, last_name as family_name, uname as username, status as user_status, custom_attribute from @TempTable;

This query extracts the results of the stored procedure into a table variable and then runs a SELECT statement on that table variable to correctly alias and cast fields to what Lumos expects.

Deactivate Account (with stored procedure)

Our stored procedure is defined as the following

CREATE PROCEDURE deactivateAccount
    @account_id VARCHAR(50)
AS
BEGIN
    UPDATE dbo.app_users SET status = 'SUSPENDED'  WHERE id = CAST(@account_id AS INT)
END;
GO

The query entered in lumos should look like

EXEC deactivateAccount @account_id = {account_id};