The Microsoft SQL Server resource adapter is defined in the com.waveset.adapter.MSSQLServerResourceAdapter class
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.
None
The Microsoft SQL Server resource adapter is a custom adapter. You must perform the following steps to complete the installation process:
To add this resource to the Waveset 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
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.
mssqlserver.jar
msutil.jar
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 accesses 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 Waveset 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
This section provides information about supported connections and privilege requirements.
Waveset uses JDBC over SSL to communicate with SQL Server.
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 |
Prior to SQL Server version 2005, users assigned a public role could view metadata for all database objects. Consequently, any user who was logged on to a SQL Server instance could view the metadata for every object in the server, including objects for which the user had no rights.
As of SQL Server version 2005, the visibility of metadata is limited to those securables owned by a user or for which the user has been granted permission.
To allow callers to view metadata, you can grant them the VIEW DEFINITION permission at an appropriate level. For example, you can grant permissions at the object level, database level, or server level. You can also modify the stored procedure so that it executes under the credentials of the owner.
For more information, see http://msdn.microsoft.com/en-us/library/ms187113.aspx.
The following table summarizes the provisioning capabilities of this adapter.
The following table lists the default account attributes (all strings).
Waveset 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 Waveset 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:
Waveset 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. |
None
$domain$ $accountId$
MSSQLServerUserForm.xml
Use the Waveset debug pages to set trace options on the following classes:
com.waveset.adapter.MSSQLServerResourceAdapter
com.waveset.adapter.JdbcResourceAdapter