Microsoft SQL Server
Installing the connector
All PowerShell connectors are installed in the same way, which is described in the document: PowerShell connector installation
Details related to PowerShell SQL Server connector installation:
The connector uses the SQL Server PowerShell provider to communicate with MSSQL server. Before installing the MSSQL connector, the SQL Server PowerShell module should be installed. You can install the SQL Server PowerShell module using this link.
General usage
For establishing a connection with MSSQL server, the connector uses PSDrive that has its own path pattern: SQLSERVER:\SQL\ComputerName\InstanceName.
Example: SQLSERVER:\SQL\10.50.41.50,1433\default
If the standalone server hosts only one MSSQL Server, the connection path should have the ‘\default’ ending.
If the standalone server hosts more than one MSSQL server instances, connection path should end with the name of MSSQL Server instance.
For example:
SQLSERVER:\SQL\10.50.41.50,1433\Instance1
SQLSERVER:\SQL\10.50.41.50,1433\Instance2
See more at: SQL Server PowerShell Provider
The connector supports the following MSSQL server authentications:
- MSSQL server authentication – the connector uses MSSQL credentials that are set inside the OpenIAM Managed System configuration.
- Windows authentication – the connector service should run as a windows user which has permissions inside MSSQL server.
Provisioning
The connector works in two modes.
- With all databases in one MSSQL server (system databases will be ignored). It is the default mode.
- With particular databases in one MSSQL server.
For provisioning database users in specific databases, the connector supports two approaches:
- Set the Databases attribute in the user policy map. This approach allows adding or removing database users for particular databases.
- Set baseDN or DATABASE_NAME attributes in metaData attributes. This approach allows for provisioning database users in one database. If both parameters are set, the connector will choose the DATABASE_NAME attribute.
baseDN - Database path
For example:
Databases\DatabaseName
DATABASE_NAME - Database name
For example:
DatabaseName
For provisioning users with Windows Authentication, the connector requires the DOMAIN_NAME attribute in metaData attributes.
DOMAIN_NAME - Domain or server instance name
For example:
OpenIAMDomain
The connector uses SMO objects for managing MSSQL objects. More info can be found at this Link
ADD
| Attribute name | Description | Type |
|---|---|---|
| Name | Specifies the user name in a database. Required | string |
| UserType | Specifies the type of user in a database. Required "AsymmetricKey" - Specifies that the user login for the database is based on an asymmetric key. "Certificate" - Specifies that the user login for the database is based on a certificate. "External" - Specifies that the user is based on external authentication "NoLogin" - Specifies that the user does not have a login for the database. "SqlLogin" - Specifies a SQLLogin user. This is deprecated, use SQLUser instead. "SqlUser" - Specifies that the user is either a SQLLogin user or a user with password. | enum |
| Login | Specifies a name for the Login object. The case sensitivity is the same as that of the instance of the SQL Server. Required if "UserType" is SqlLogin Required if "CreateLoginIfNotExist" is True | string |
| LoginType | Specifies the type of the Login object as a Microsoft.SqlServer.Management.Smo.LoginType value. Required if "CreateLoginIfNotExist" is True "SqlLogin" - Login is based on the SQL login. "WindowsGroup" - Login is based on the Windows Group. "WindowsUser" - Login is based on the Windows User. | enum |
| CreateLoginIfNotExist (Custom attribute) | True – Create login if login does not exist False – Do not create login | bool |
| DataBaseRoles (Custom attribute) | Contains database roles | string array |
| Databases (Custom attribute) | List of database names in which the user should be added. baseDN and DATABASE_NAME attributes will be ignored | string array |
| ExcludeDatabasesByStatus (Custom attribute) | List of database statuses which should be ignored. Prevent provisioning the database user in databases which have status other than normal. In case only if connector works with all databases. Common statuses: "Offline", "Shutdown" More statuses | string array |
| Login attributes | ||
| Password (Custom attribute) | Set password for login. Required if "CreateLoginIfNotExist" is True and "LoginType" is SqlLogin | string |
| GrantConnectSql | Indicates that the Login object is not denied permissions to connect to the database engine. By default, Login objects are denied permissions to connect to the database engine. | bool |
| Enable | Indicates that the Login object is enabled. By default, Login objects are disabled WindowsGroup type objects are always enabled. This parameter does not affect them. | bool |
| ServerRoles (Custom attribute) | Contains server roles | string array |
| DefaultDatabase | Specify the default database for the Login object. The default value is master. | string |
| EnforcePasswordPolicy | Indicates that the password policy is enforced for the Login object. This parameter applies only SqlLogin type objects. | bool |
| EnforcePasswordExpiration | Indicates that the password expiration policy is enforced for the Login object. This parameter applies only SqlLogin type objects. This parameter implies the EnforcePasswordPolicy parameter. You do not have to specify both. | bool |
| MustChangePasswordAtNextLogin | Indicates that the user must change the password at the next login. This parameter applies only SqlLogin type objects. This parameter implies the EnforcePasswordExpiration parameter. You do not have to specify both. | bool |
MODIFY
| Attribute name | Description | Type |
|---|---|---|
| Name | Only if LoginType is "SqlLogin" or UserType is "NoLogin" | string |
| Password (Custom attribute) | Change password for Login, from the Login attribute | string |
| DataBaseRoles (Custom attribute) | Add and remove database roles for user Name attribute | string array |
| GrantConnectSql | True – grant connect permission for the Login attribute False – deny connect permission for the Login attribute | bool |
| Enable | True – Enable user False – Disable user Note: Does not works for the LoginType = "WindowsGroup" | bool |
| ServerRoles (Custom attribute) | Add and remove server roles for the Login attribute | string array |
| DefaultDatabase | Set default database for theLogin attribute | string |
| EnforcePasswordPolicy | Set enforce policy for theLogin attribute Note: For LoginType = "SqlLogin" | bool |
| EnforcePasswordExpiration | Set enforce password expiration for the Login attribute Note: For LoginType = "SqlLogin" | bool |
| MustChangePasswordAtNextLogin | Set user must change the password at the next login. Note: Only for changing password for LoginType = "SqlLogin" | bool |
| Databases (Custom attribute) | List of database names in which the user should be added or removed. baseDN and DATABASE_NAME attributes will be ignored | string array |
| ExcludeDatabasesByStatus (Custom attribute) | List of database statuses which should be ignored. Prevent provisioning the database user in databases which have a status another than normal. only if connector works with all the databases. Common statuses: "Offline", "Shutdown" More statuses | string array |
RESET PASSWORD
Password can be changed only for LoginType = "SqlLogin"
| Attribute name | Description | Type |
|---|---|---|
| Login | Login name for user Required | string |
| Password (Custom attribute) | New password for Login attribute Required | string |
| MustChangePasswordAtNextLogin | Set if user must change the password at the next login. | bool |
SUSPEND
Disable login. If login has windows authentication, then the DATABASE_NAME attribute should contain the domain or instance name.
| Attribute name | Description | Type |
|---|---|---|
| Login | Login name which will be disabled. Required | string |
| LoginType | Login’s type Required Note: Does not work for LoginType = "WindowsGroup" | enum |
RESUME
Enable login. If login has Windows authentication, then the DATABASE_NAME attribute should contain the domain or instance name.
| Attribute name | Description | Type |
|---|---|---|
| Login | Login name which will be enabled. Required | string |
| LoginType | Login’s type Required Note: Does not work for LoginType = "WindowsGroup" | enum |
DELETE
Deletes database user from the database. The database should be set in baseDN (database path) or the DATABASE_NAME attribute in metaData atributes. If the login uses Windows authentication, then the DATABASE_NAME attribute should contain the domain of the instance.
| Attribute name | Description | Type |
|---|---|---|
| Name | Database user name which will be deleted Reqiurd | string |
| UserType | User’s type Required | enum |
| DropLoginIfNotUsed (Custom attribute) | True – After the user is deleted, the connector will check if the mapped login does not have any mapped users to other databases, and then the mapped login will be deleted. False – After the user is deleted, the connector will not check the login to any user's mapping | bool |
| LoginType | Login’s type Required if login has windows authentication and UserType is not "NoLogin" | enum |
| Databases (Custom attribute) | List of database names in which the user should be removed. baseDN and DATABASE_NAME attributes will be ignored | string array |
| ExcludeDatabasesByStatus (Custom attribute) | List of database statuses which should be ignored. Prevent provisioning the database user in databases which have a status other than normal. only if the connector works with all databases. Common statuses: "Offline", "Shutdown" More statuses | string array |
Search
User object
Get-DBUsers – return users from databases
[-Database <array>] - array of database names or * -all databases
[-UserName <string>] - database user name
[-UsersFilter <string>] - query for search
[-SkipSystemObject] – skip system databases (like master) and system users (like guest)
If -Database parameter is not set then the connector will use baseDN or DATABASE_NAME attributes. If the attributes do not exist the connector will return users from all databases.
| Attribute name | Description | Response type |
|---|---|---|
| Id | User’s id | int |
| Name | User’s name | string |
| Database | User’s database | string |
| DatabaseRole | Granted database roles | array |
| UserType | User’s type | string |
| Login | User’s login name | string |
| LoginType | Login’s type | string |
| AuthenticationType | User’s authentication type | string |
| CreateDate | User’s creation date | string |
| DateLastModified | The last date of user modification | string |
Database object
Get-Databases – return databases
[-Database <array>] - array of database names or * - return all databases
[-SkipSystemObject] - skip system databases (like master)
If the -Database parameter is not set then the connector will use baseDN or DATABASE_NAME attributes. If attributes do not exist the connector will return all databases.
| Attribute name | Description | Response type |
|---|---|---|
| Id | Id’s database | int |
| Name | Database name | string |
| FixedRole | Default database’s roles | array |
| UserDefinedRole | Custom roles | array |