Sun Identity Manager 8.1 Resources Reference

Chapter 21 Microsoft SQL Server

The Microsoft SQL Server resource adapter is defined in the com.waveset.adapter.MSSQLServerResourceAdapter class

Adapter Details

Use this adapter to manage multiple databases on the SQL server. Logins can be managed to the server itself as well as the managed databases.

If you have a custom SQL table, see Chapter 10, Database Table for information about using the Resource Adapter Wizard to create a custom Microsoft SQL table resource.

Resource Configuration Notes

None

Identity Manager Installation Notes

The Microsoft SQL Server resource adapter is a custom adapter. You must perform the following steps to complete the installation process:

Installing the Microsoft SQL Server Resource Adapter

To add this resource to the Identity Manager resources list, you must add the following value in the Custom Resources section of the Configure Managed Resources page.
com.waveset.adapter.MSSQLServerResourceAdapter

If you connect to the resource with the Microsoft SQL Server 2005 Driver for JDBC, copy the mssqlserver.jar file to the InstallDir\idm\WEB-INF\lib directory.

If you connect to the resource with the Microsoft SQL Server 2000 Driver for JDBC, copy the following jar files from the Program Files\2000 Microsoft SQL Server 2000 Driver for JDBC\lib directory to the InstallDir\idm\WEB-INF\lib directory.
- msbase.jar
  - mssqlserver.jar
  - msutil.jar
  Note –
  All connections to SQL Server must be performed using the same version of the JDBC driver. This includes the repository as well as all resource adapters that manage or require SQL Server accounts or tables, including the Microsoft SQL adapter, Microsoft Identity Integration Server adapter, Database Table adapter, Scripted JDBC adapter, and any custom adapter based on these adapters. Conflict errors occur if you attempt use different versions of the driver.

Usage Notes

You can use two types of authentication with SQL Server:

Windows authentication. SQL Server relies on Windows for all authentication and security mechanisms. When a user access SQL Server, it obtains the user and password information from the user’s network security attributes. If the user has been granted access to SQL Server from within Windows, the user is logged in to SQL Server automatically. Account IDs passed in to the adapter must be in the form of Domain\accountID. Pass-through authentication is not supported for Windows authentication.
Mixed mode authentication. In this scenario, both Windows authentication and SQL Server authentication are enabled. When a user connects with a specified login name and password from a non-trusted connection, SQL Server performs the authentication itself by checking to see if a SQL Server login account has been set up and if the specified password matches the one previously recorded. If SQL Server does not have a login account set, authentication fails and the user receives an error message.

Windows authentication mode for the SQL Server resource adapter can only be configured on the Microsoft SQL Server adapter if the Identity Manager server is running on a Windows machine that is included in the same Windows security/authentication framework as the SQL Server server instance.

The JDBC driver supports the use of Type 2 integrated authentication on Windows operating systems through the integratedSecurity connection string property. To use integrated authentication, copy the sqljdbc_auth.dll file to a directory on the Windows system path on the computer where the JDBC driver is installed.

The sqljdbc_auth.dll files are installed in the following location:

InstallationDirectory\sqljdbc_Version\Language\auth\

On a 32-bit processor, use the sqljdbc_auth.dll file in the x86 folder. On a 64-bit processor, use the sqljdbc_auth.dll file in the x64 folder.

For more information, see the following article:

http://msdn2.microsoft.com/en-us/library/ms378428.aspx

The SQL Server resource adapter uses the following system procedures to manage user accounts:

sp_addlogin, sp_droplogin
sp_addrole
sp_addrolemember, sp_droprolemember
sp_addsrvrolemember, sp_dropsrvrolemember
sp_grantdbaccess
sp_helplogins
sp_helprole
sp_helpuser
sp_helpsrvrolemember
sp_password
sp_revokedbaccess

Security Notes

This section provides information about supported connections and privilege requirements.

Supported Connections

Identity Manager uses JDBC over SSL to communicate with SQL Server.

Required Administrative Privileges

The following table indicates who can execute the system procedures:

System Procedure	Permissions Required
`sp_addlogin`	Members of the sysadmin and securityadmin fixed server roles.
`sp_addrole`	Members of the sysadmin fixed server role, and the db_securityadmin and db_owner fixed database roles.
`sp_addrolemember`	Members of the sysadmin fixed server role and the db_owner fixed database role can execute `sp_addrolemember` to add a member to fixed database roles. Role owners can execute `sp_addrolemember` to add a member to any SQL Server role they own. Members of the db_securityadmin fixed database role can add users to any user-defined role.
`sp_addsvrrolemember`	Members of the sysadmin fixed server role.
`sp_droplogin`	Members of the sysadmin and securityadmin fixed server roles.
`sp_droprolemember`	Only members of the sysadmin fixed server role, the db_owner and db_securityadmin fixed database roles can execute `sp_droprolemember`. Only a member of the db_owner fixed database role can remove users from a fixed database role.
`sp_dropsvrrolemember`	Members of the sysadmin fixed server role.
`sp_grantdbaccess`	Members of the sysadmin fixed server role, the db_accessadmin and db_owner fixed database roles.
`sp_helplogins`	Members of the sysadmin and securityadmin fixed server roles.
`sp_helprole`	Execute permissions default to the public role.
`sp_helpsrvrolemember`	Execute permissions default to the public role.
`sp_helpuser`	Execute permissions default to the public role.
`sp_password`	Execute permissions default to the public role for a user changing the password for his or her own login. Only members of the sysadmin role can change the password for another user’s login.
`sp_revokedbaccess`	Members of the sysadmin fixed server role, and the db_accessadmin and db_owner fixed database roles

Provisioning Notes

The following table summarizes the provisioning capabilities of this adapter.

Feature	Supported?
Enable/disable account	Yes
Rename account	No
Pass-through authentication	Mixed mode authentication: Yes Windows authentication: No
Before/after actions	No
Data loading methods	Import directly from resource Reconcile with resource

Account Attributes

The following table lists the default account attributes (all strings).

Identity Manager User Attribute	Resource User Attribute	Description
domain	`IGNORE_ATTR`	The domain the user belongs to.
defaultDB	`defaultDB`	The user’s default database.
serverRoles	`serverRoles`	The database roles the user is a member of.

Because multiple databases can be managed, the Identity Manager administrator must add account attributes for each database to be managed. These attributes must include the database name as part of the attribute name in order to differentiate them from attributes for other managed databases:

Identity Manager User Attribute	Data Type	Description
userNameDBName	String	The user name of the account on the database. Setting a userName for a database will grant access to the database for the account, and clearing the userName for a database will remove access.
rolesDBName	String	The roles for the account on the database.

Resource Object Management

None

Identity Template

$domain$ $accountId$

Sample Forms

MSSQLServerUserForm.xml

Troubleshooting

Use the Identity Manager debug pages to set trace options on the following classes:

com.waveset.adapter.MSSQLServerResourceAdapter
com.waveset.adapter.JdbcResourceAdapter