Integrate with Database User Management (Microsoft SQL Server)
Prerequisites
Before you install and configure a Database User Management (Microsoft SQL Server) orchestrated system, you should consider the following pre-requisites and tasks.
Certified Components
- Microsoft SQL Server 2005, 2008, 2012, 2014, 2016, 2019
Supported Modes
Database User Management (Microsoft SQL Server) orchestrated system supports Managed System mode.
Supported System Operations
- Create UserName
- Create UserLogin
- Change UserLogin password
- Delete UserLogin
- Assign Roles to a userName
- Revoke Roles from a userName
Create a System User Account for Database User Management (MSSQL) System Operations
- Verify the following requirements for your Microsoft SQL Server
installation before configuring the orchestrated system.
- The Microsoft SQL Server TCP/IP port is enabled. The
default port is 1433.
To enable the TCP/IP port:
- Open the Microsoft SQL Server Configuration Manager.
- Click SQL Server Network Configuration.
- Click Protocols for MSSQLSERVER.
- 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.
- The Microsoft SQL Server TCP/IP port is enabled. The
default port is 1433.
- Create Login using the following query:
Create LOGIN serviceuser with PASSWORD='password' , DEFAULT_DATABASE =DBname GO
- Create a user using the following query:
USE DBname; Create USER serviceuser with LOGIN serviceuser; GO
- 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 Orchestrated Systems functionality available in the Oracle Access Governance Console.
Navigate to the Orchestrated Systems Page
- From the Oracle Access Governance navigation menu icon
, select Service Administration → Orchestrated Systems.
- Click the Add an orchestrated system button to start the workflow.
Select system
On the Select system step of the workflow, you can specify which type of system you would like to onboard.
- Select Database User Management (MSSQL).
- Click Next.
Enter details
- Enter a name for the system you want to connect to in the What do you want to call this system? field.
- Enter a description for the system in the How do you want to describe this system? field.
- Determine if this orchestrated system is an authoritative source, and if Oracle Access Governance can manage permissions by setting the following checkboxes.
- This is the authoritative source for my identities
- I want to manage permissions for this system
- Click Next.
Add Owner
Note:
When setting up the first Orchestrated System for your service instance, you can assign owners only after you enable the identities from the Manage Identities section.- Select an Oracle Access Governance active user as the primary owner in the Who is the primary owner? field.
- Select one or more additional owners in the Who else owns it? list. You can add up to 20 additional owners for the resource.
Account settings
-
Select where to send notification emails when an account is created. The default setting is User. You can select one, both, or none of these options. If you select no options then notifications will not be sent when an account is created.
- User
- User manager
- When an identity moves within your enterprise, for example when
moving from one department to another, you may need to adjust what accounts the
identity has access to. In some cases the identity will no longer require
certain accounts which are not relevant to their new role in the enterprise. You
can select what to do with the account when this happens. Select one of the
following options:
- Disable
- Delete
- When an identity leaves your enterprise you should remove access to
their accounts. You can select what to do with the account when this happens.
Select one of the following options:
- Disable
- Delete
Note:
If you do not configure your system as a managed system then this step in the workflow will display but is not enabled. In this case you proceed directly to the Integration settings step of the workflow.Note:
If your orchestrated system requires dynamic schema discovery, as with the Generic REST and Database Application Tables (Oracle) integrations, then only the notification email destination can be set (User, Usermanager) when creating the orchestrated system. You cannot set the disable/delete rules for movers and leavers. To do this you need to create the orchestrated system, and then update the account settings as described in Configure Orchestrated System Account Settings.Integration settings
On the Integration settings step of the workflow, enter the configuration details required to allow Oracle Access Governance to connect to the Microsoft SQL Server.
- 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.. - In the Username field, enter the administration username you will use to connect to the Microsoft SQL Server database.
- Enter the password for the adminstration user in the Password/Confirm password fields.
- 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
. - The agent for this orchestrated 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>
. - Update Database inclusion list with one or more database names that data should be included in the dataload.
- Update Database exclusion list with one or more database names that data should be excluded from the dataload.
- Click Add to create the orchestrated 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 database. 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.
- Customize before enabling the system for data loads
- Activate and prepare the data load with the provided defaults
For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.
Access to Oracle Support
Oracle customers that have purchased support have access to electronic support through My Oracle Support. For information, visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info or visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs if you are hearing impaired.