Microsoft SQL Server Specific Information

Currently, the SQL Server proxy only supports SQL Server logins. If the proxy is running on Windows, it is possible to configure internal authentication on the SQL Server JDBC driver. Please contact support for more details if this is necessary.
When using the Microsoft Azure Database, the setCatalog() function is not usable to change the catalog on a connection. To support an application selecting the database on the initial connection, use the string ${database} in the JDBC URL to set the database, i.e. ";databaseName=${database}" at the end of the url. The value ${database} will be extracted from the client login to populate this value.
When performing explain extraction, the option checkTableMismatch can be used (any value) to trigger testing and printing if a the tables extracted from the explain match what is used internally by Heimdall. This can be used to track down table extraction issues.
If using Mirroring (not Always-On) for redundancy, transactions will fail. This can apply if using SQL Server in AWS for versions prior to 2016, or older instances spun up before May 2019 with failover. Please use Always-On, as transaction handling with mirroring is not easily supported.
Applications that depend on session recovery are likely to not work properly, in particular if session data is used as part of record/table locking mechanisms, such as the SP "applock_mode" with the lock owner of "session". One example of a library that uses this is "Hangfire".
Applications that require MARS (multiple active result sets) may not work properly. The proxy will actively negotiate this option to be off with clients, which normally is sufficient to allow the applications to work. In some rare cases however, code needs to be modified to read one full result-set into memory before opening a new result-set.

Schema Assist for Dependencies

When using SQL Server, parsing is performed to detect view dependencies along with fixed (not dynamic) dependencies in stored procedures, in order to automate invalidation. This is only performed at proxy start at this time.

Trigger Invalidation with SQL Server

Connection properties:

dbTriggerTracking=true
dbChanges=select ts,name from heimdall.table_change where lastupdate > @ts
dbTimeMs=select heimdall.get_time_ms()

CREATE DATABASE [heimdall]
GO

/*** create the trigger tracking table ***/
USE [heimdall]
CREATE TABLE [dbo].[table_change](
    [ts] [bigint] NULL,
    [name] [varchar](64) NOT NULL,
    CONSTRAINT [PK_table_change] PRIMARY KEY ([name]));

CREATE FUNCTION [dbo].[get_time_ms]() RETURNS BIGINT
AS
BEGIN
DECLARE @timems BIGINT
set @timems= (SELECT cast(DATEDIFF(s, '1970-01-01 00:00:00', GETUTCDATE()) as BIGINT)*1000+cast(DATEDIFF(ms,CONVERT(VARCHAR,GETUTCDATE(),120),GETUTCDATE()) as BIGINT))
return @timems
END

/*** Create the tracking procedure, which will be used by the triggers ***/
CREATE OR ALTER PROCEDURE [heimdall].[dbo].[track_changes] 
    @tablename NVARCHAR(128)
as
    SET NOCOUNT OFF
    UPDATE [heimdall].[dbo].[table_change] SET ts=[heimdall].[dbo].get_time_ms() WHERE name = @tablename
    IF @@ROWCOUNT != 0
        RETURN
    INSERT INTO [heimdall].[dbo].[table_change] VALUES ( dbo.get_time_ms(), @tablename )
GO

/*** trigger to apply to each table that needs trigger based eviction ***/
/**** for table vevo.dbo.Product ***/
USE [vevo2]; CREATE OR ALTER TRIGGER [dbo].[Product_changed]
    ON [vevo2].[dbo].[Product]
    AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    exec [heimdall].[dbo].[track_changes] 'vevo2.dbo.Product'
END
GO

Active Directory Password & Group Synchronization

CREATE OR ALTER PROCEDURE heimdall.sync_user
    (
    @username nvarchar(max),
    @password nvarchar(max),
    @ldapgroups nvarchar(max),
    @createRolesIfMissing BIT = 1 -- Optional parameter, if set to 1 roles are created and membership is added to the user
    )
AS
BEGIN
    --Logic variables
    DECLARE @sql nvarchar(max);
    DECLARE @roleName nvarchar(max);
    DECLARE @currentRow INT = 1;
    DECLARE @rowCount INT;
    CREATE TABLE #groupList (id INT IDENTITY(1,1), g varchar(max)); --table variable can't be used in dynamic SQL
    CREATE TABLE #groupsToRevoke (id INT IDENTITY(1,1), g varchar(max));
    CREATE TABLE #oneToManyMappings (id INT IDENTITY(1,1), g varchar(max));

    --Configurable variables
    DECLARE @currentSchemaName varchar(max) = 'heimdall';
    DECLARE @lookupTableName varchar(max) = 'lookup_table';
    DECLARE @lookupTableKey varchar(max) = 'ldap_group';
    DECLARE @lookupTableValue varchar(max) = 'role_name';

    -- Put ldapgroups to table
    INSERT INTO #groupList (g)
    SELECT LTRIM(RTRIM(value))
    FROM STRING_SPLIT(@ldapgroups, ',');

    -- Check if lookup_table exists
    IF (EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = @currentSchemaName 
                 AND  TABLE_NAME = @lookupTableName))
    BEGIN
        -- One to many mappings of ldap_groups
        SET @sql = CONCAT(
        'INSERT INTO #oneToManyMappings (g) 
        SELECT lt.', QUOTENAME(@lookupTableKey), 
        ' FROM ', QUOTENAME(@lookupTableName), ' lt 
        INNER JOIN #groupList gl ON lt.', QUOTENAME(@lookupTableKey), ' = gl.g 
        GROUP BY lt.', QUOTENAME(@lookupTableKey),
        ' HAVING COUNT(1) > 1;');
        EXEC sp_executesql @sql;
        PRINT @sql;

        -- Delete groups which has one to many mapping
        SET @sql = 'DELETE t1 FROM #groupList t1 JOIN #oneToManyMappings t2 ON t1.g = t2.g'
        EXEC sp_executesql @sql;
        PRINT @sql;

        -- Insert all one to many mappings (corresponding roles)
        SET @sql = CONCAT(
       'INSERT INTO #groupList 
        SELECT ', QUOTENAME(@lookupTableValue), 
        ' FROM ', QUOTENAME(@lookupTableName), 
        ' lt INNER JOIN #oneToManyMappings otmm ON lt.', QUOTENAME(@lookupTableKey), ' = otmm.g;');
        EXEC sp_executesql @sql;
        PRINT @sql

        -- Replace ldap groups with it's corresponding role names from lookup_table
        SET @sql = CONCAT(
        'UPDATE gl SET gl.g = lt.', QUOTENAME(@lookupTableValue), 
        ' FROM #groupList gl JOIN ', 
        QUOTENAME(@currentSchemaName), '.', QUOTENAME(@lookupTableName), ' lt ON gl.g = lt.', QUOTENAME(@lookupTableKey));
        EXEC sp_executesql @sql;
        PRINT @sql;
    END

    -- Create login if not exists, alter otherwise
    IF NOT EXISTS 
      (SELECT name  
       FROM master.sys.server_principals
       WHERE name = @username)
    BEGIN
        SET @sql = CONCAT('CREATE LOGIN ', QUOTENAME(@username), ' WITH PASSWORD = ', QUOTENAME(@password, ''''));
        EXEC sp_executesql @sql;
        PRINT @sql;
    END
    ELSE
    BEGIN
        SET @sql = CONCAT('ALTER LOGIN ', QUOTENAME(@username), ' WITH PASSWORD = ', QUOTENAME(@password, ''''));
        EXEC sp_executesql @sql;
        PRINT @sql;
    END

    -- Create user if not exists, alter otherwise
    IF NOT EXISTS 
       (SELECT 1
        FROM sys.sysusers
        WHERE name = @username)
    BEGIN
        SET @sql = CONCAT('CREATE USER ', QUOTENAME(@username), ' FOR LOGIN ', QUOTENAME(@username));
        EXEC sp_executesql @sql;
        PRINT @sql;
    END
    ELSE
    BEGIN
        SET @sql = CONCAT('ALTER USER ', QUOTENAME(@username), ' WITH LOGIN = ', QUOTENAME(@username));
        EXEC sp_executesql @sql;
        PRINT @sql;
    END

    -- Get roles which user is not a member of anymore
    INSERT INTO #groupsToRevoke (g)
    (
        SELECT r.name
        FROM sys.database_role_members drm
                 JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id
                 JOIN sys.database_principals u ON drm.member_principal_id = u.principal_id
        WHERE u.name = @username
        EXCEPT
        SELECT g
        FROM #groupList
    );

    SET @rowCount = (SELECT COUNT(*) FROM #groupsToRevoke);

    -- Drop membership of roles which user is not a member of anymore
    WHILE @currentRow <= @rowCount
    BEGIN
        SET @roleName = (SELECT g FROM #groupsToRevoke WHERE id = @currentRow);
        SET @sql = CONCAT('ALTER ROLE ', QUOTENAME(@roleName), ' DROP MEMBER ', QUOTENAME(@username));
        EXEC sp_executesql @sql;
        PRINT @sql;

        SET @currentRow += 1;
    END

    SET @currentRow = 1;
    SET @rowCount = (SELECT MAX(id) FROM #groupList)
    -- Iterate over groupList and create roles
    WHILE @currentRow <= @rowCount
    BEGIN
        SET @roleName = (SELECT g FROM #groupList WHERE id = @currentRow);

        IF @roleName IS NOT NULL AND @roleName <> '' AND @createRolesIfMissing = 1 -- If 'roleName' is empty then no role is created and granted to user
        BEGIN
            -- Create role if not exists
            IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = @roleName AND type = 'R')
                BEGIN
                    SET @sql = CONCAT('CREATE ROLE ', QUOTENAME(@roleName));
                    EXEC sp_executesql @sql;
                    PRINT @sql;
                END

                -- Grant role to the user
                SET @sql = CONCAT('ALTER ROLE ', QUOTENAME(@roleName), ' ADD MEMBER ', QUOTENAME(@username));
                EXEC sp_executesql @sql; -- Won't throw an exception if user is already a member of the given role
                PRINT @sql;
        END

        SET @currentRow += 1;
    END
    -- Make sure we drop these to prevent reuse issues on the same connection
    DROP TABLE #groupList;
    DROP TABLE #groupsToRevoke;
END

Active Directory Password & Group Synchronization extra options

By default, a role with the same name is created in the database for each extracted ldap group. By creating the table below for each key found (ldap_group), a role with the specified name in the value (role) will be created instead. Composite Primary Key allows to map more than one role to particular ldap_group; If 'role' is empty then no role is created and granted to user for this particular ldap_group.

CREATE TABLE dbo.lookup_table (
    ldap_group varchar(255),
    role_name varchar(64),
    PRIMARY KEY (ldap_group, role_name)
)

Amazon RDS for SQLServer

Adjustments to the 'master' database are presently limited within Amazon RDS for SQL Server. When the 'master' database is configured in the datasource or left unspecified, certain functionalities, such as LDAP synchronization, may not work properly. To resolve this issue, clients are recommended to specify the default database in the Datasource tab of the JDBC URL. A representative JDBC URL format is demonstrated as follows: "jdbc:sqlserver://{server_name}:1433;databaseName=${database}", then i properties define defaultCatalog with created database name, in our case [testDB].

If we would to set up a proxy with a new user on Amazon RDS for SQLServer, we can use the following example: Connect with admin user to database master. Then execute queries similar to those:

CREATE DATABASE [testDB];  
USE [testDB];  
CREATE LOGIN [test_user] WITH PASSWORD = 'test_user',  DEFAULT_DATABASE=[testDB];    
CREATE USER [test_user] FOR LOGIN [test_user];  
CREATE SCHEMA heimdall;  
GRANT ALTER, CONTROL, EXECUTE, SELECT, INSERT, UPDATE, DELETE
    TO [test_user];  
GRANT CREATE TABLE TO [test_user];  
GRANT CREATE PROCEDURE TO [test_user];

While using ldap in VDB the command for syncing user will look like this:
EXECUTE testDB.heimdall.sync_user N'${user}', N'${password}', N'${ldapgroups}'

For using load-balancing features we will need to run:

GRANT VIEW DATABASE STATE TO [test_user];  
GRANT VIEW SERVER STATE TO [test_user]; -- has to be run on master database

Database name, and username should be adjusted to the clients need. At the end, created user should be placed in Datasource tab, and JDBC URL should point into defined database.

Keys	Action
`?`	Open this help
`n`	Next page
`p`	Previous page
`s`	Search