Integrate with Database User Management (Microsoft SQL Server)

Prerequisites

Before you install and configure a Database User Management (Microsoft SQL Server) connected system, you should consider the following pre-requisites and tasks.

Certified Components

The target system can be any one of the following:
  • Microsoft SQL Server 2005, 2008, 2012, 2014, 2016, 2019

Supported Modes

Database User Management (Microsoft SQL Server) connected system supports Permissions Management mode.

Supported Connected System Operations

The Database User Management (Microsoft SQL Server) connected system supports the following connector operations:
  • User Management
    • Create UserName
    • Create UserLogin
    • Change UserLogin password
    • Delete UserLogin
  • Role Grant Management
    • Assign Roles to a userName
    • Revoke Roles from a userName

Create a Target System User Account for Database User Management (MSSQL) Connected System Operations

  1. Verify the following requirements for your Microsoft SQL Server installation before configuring the connected system.
    • The Microsoft SQL Server TCP/IP port is enabled. The default port is 1433.
      To enable the TCP/IP port:
      1. Open the Microsoft SQL Server Configuration Manager.
      2. Click SQL Server Network Configuration.
      3. Click Protocols for MSSQLSERVER.
      4. In the right frame, right-click TCP/IP and then click Enable.
    • Mixed mode authentication is enabled.
    • The TCP/IP port is not blocked by a firewall.
  2. Create Login using the following query:
    Create LOGIN serviceuser with PASSWORD='password' , DEFAULT_DATABASE =DBname
    GO
  3. Create a user using the following query:
    USE DBname;
    Create USER serviceuser with LOGIN serviceuser;
    GO
  4. Assign the following permissions and roles to the created user:
    ALTER ROLE db_datawriter ADD MEMBER serviceuser;
    ALTER ROLE db_datareader ADD MEMBER serviceuser;
    ALTER ROLE db_accessadmin ADD MEMBER serviceuser;
    ALTER ROLE db_owner ADD MEMBER serviceuser;
    exec sp_addsrvrolemember 'serviceuser', 'securityadmin';

Configure

You can establish a connection between Microsoft SQL Server and Oracle Access Governance by entering connection details. To achieve this, use the Connected Systems functionality available in the Oracle Access Governance Console.

Navigate to the Connected Systems Page

Navigate to the Connected Systems page of the Oracle Access Governance Console, by following these steps:
  1. From the Oracle Access Governance navigation menu icon Navigation menu, select Service Administration → Connected Systems.
  2. Click the Add a connected system button to start the workflow.

Select and configure a new Connected System

To start the add a connected system workflow, you should select the type of system that you would like to connect with Oracle Access Governance:

Select system

On the Select system step of the workflow, you can specify which type of application you would like to onboard.

  1. Select Database User Management (MSSQL).
  2. Click Next.

Enter details

On the Enter Details step of the workflow, enter the details for the connected system:

  1. Enter a name for the application you want to connect to in the What do you want to call this application? field.
  2. Enter a description for the application in the How do you want to describe this application? field.
  3. Click Next.

Configure

On the Configure step of the workflow, enter the configuration details required to allow Oracle Access Governance to connect to the target Microsoft SQL Server.

  1. In the Easy connect URL for the database field, enter an easy connect URL to connect to the Microsoft SQL Server database, using the following syntax jdbc:sqlserver://[host]:[port];[databaseName];[encrypt];[trustServerCertificate]. For further information, consult the JDBC driver documentation..
  2. In the Username field, enter the administration username you will use to connect to the Microsoft SQL Server database.
  3. Enter the password for the adminstration user in the Password/Confirm password fields.
  4. In the Connection properties field, enter any connection properties that will be used to configure a secure connection. They should be key value pairs in the following format: key1=val1#key2=val2.
  5. The agent for this Connected System requires a Microsoft SQL Server driver jar in the classpath. Details of how this is used by the agent can be found in Custom Jar Support. The Microsoft SQL Server jar name and checksum should be in the format of <jarName>::<jarChecksum>.
  6. Update Database inclusion list with one or more database names that data should be included in the dataload.
  7. Update Database exclusion list with one or more database names that data should be excluded from the dataload.
  8. Click Add to create the connected system.

Finish up

On the Finish Up step of the workflow, you are asked to download the agent you will use to interface between Oracle Access Governance and Microsoft SQL Server. Select the Download link to download the agent zip file to the environment in which the agent will run.

After downloading the agent, follow the instructions explained in the Agent Administration article.

After downloading the agent, follow the instructions explained in the article.

Post Configuration

There are no post configuration steps associated with a Microsoft SQL Server connected system.