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:

Connector uses SQL Server PowerShell provider to communicate with MSSQL server. Before installing MSSQL connector, SQL Server PowerShell module should be installed. You can install the SQL Server PowerShell module using following link

General usage

For establishing connection with MSSQL server, connector uses PSDrive that has own path pattern: SQLSERVER:\SQL\ComputerName\InstanceName.
Example: SQLSERVER:\SQL\10.50.41.50,1433\default
If standalone server hosts only one MSSQL Server, connection path should have ‘\default’ ending.
If standalone server hosts more than one MSSQL server instances, connection path should end with 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

Connector supports following MSSQL server authentications:

  1. MSSQL server authentication – connector uses MSSQL credentials that are set inside OpenIAM Managed system configuration.
  2. Windows authentication – connector service should run as windows user which has permissions inside MSSQL server.

Provisioning

Connector works in two modes.

  1. With all databases in one MSSQL server (system databases will be ignored). It is default mode.
  2. With particular databases in one MSSQL server.

For provisioning database users in particular databases, connector supports two approaches:

  1. Set Databases attribute in user policy map. This approach allows adding or removeing database users for particular databases.
  2. Set baseDN or DATABASE_NAME attributes in metaData attributes. This approach allows provisioning database users in one database. If both parameters are set, connector will choose DATABASE_NAME attribute.

baseDN - Database path
For example:
Databases\DatabaseName
DATABASE_NAME - Database name
For example:
DatabaseName

For provisioning users with Windows Authentication, connector requires DOMAIN_NAME atrtibute in metaData attributes.
DOMAIN_NAME - Domain or server instance name
For example:
OpenIAMDomain

Connector uses SMO objects for managing MSSQL objects: Link

ADD

Attribute nameDescriptionType
NameSpecifies the user name in a database.
Required
string
UserTypeSpecifies 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
LoginSpecifies a name for the Login object. The case sensitivity is the same as that of the instance of SQL Server.
Required if "UserType" is SqlLogin
Required if "CreateLoginIfNotExist" is True
string
LoginTypeSpecifies the type of the Login object as a Microsoft.SqlServer.Management.Smo.LoginType value.
Required if "CreateLoginIfNotExist" is True

"SqlLogin" - Login is based on SQL login.
"WindowsGroup" - Login is based on Windows Group.
"WindowsUser" - Login is based on 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 rolesstring
array
Databases
(Custom attribute)
List of database names in which 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 database user in databases which have status another 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
GrantConnectSqlIndicates 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
EnableIndicates 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 rolesstring
array
DefaultDatabaseSpecify the default database for the Login object. The default value is master.string
EnforcePasswordPolicyIndicates that the password policy is enforced for the Login object. This parameter applies only SqlLogin type objects.bool
EnforcePasswordExpirationIndicates 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
MustChangePasswordAtNextLoginIndicates 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 nameDescriptionType
NameOnly if LoginType is "SqlLogin" or UserType is "NoLogin"string
Password
(Custom attribute)
Change password for Login, from Login attributestring
DataBaseRoles
(Custom attribute)
Add and remove database roles for user Name attributestring
array
GrantConnectSqlTrue – grant connect permission for Login attribute
False – deny connect permission for Login attribute
bool
EnableTrue – Enable user
False – Disable user

Note: Not works for LoginType = "WindowsGroup"
bool
ServerRoles
(Custom attribute)
Add and remove server roles for Login attributestring
array
DefaultDatabaseSet default database for Login attributestring
EnforcePasswordPolicySet enforce policy for Login attribute

Note: For LoginType = "SqlLogin"
bool
EnforcePasswordExpirationSet enforces password expiration for Login attribute

Note: For LoginType = "SqlLogin"
bool
MustChangePasswordAtNextLoginSet if 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 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 database user in databases which have status another than normal.

In case only if connector works with all databases.

Common statuses:
"Offline",
"Shutdown"
More statuses
string
array

RESET PASSWORD

Password can be changed only for LoginType = "SqlLogin"

Attribute nameDescriptionType
LoginLogin name for whom will be changed password.
Required
string
Password
(Custom attribute)
New password for Login attribute
Required
string
MustChangePasswordAtNextLoginSet if user must change the password at the next login.bool

SUSPEND

Disable login. If login has windows authentication, then DATABASE_NAME attribute should contain domain or instance name.

Attribute nameDescriptionType
LoginLogin name which will be disabled.
Required
string
LoginTypeLogin’s type
Required

Note: Not works for LoginType = "WindowsGroup"
enum

RESUME

Enable login. If login has windows authentication, then DATABASE_NAME attribute should contain domain or instance name.

Attribute nameDescriptionType
LoginLogin name which will be enabled.
Required
string
LoginTypeLogin’s type
Required

Note: Not works for LoginType = "WindowsGroup"
enum

DELETE

Deletes database user from database, Database should be set in baseDN (database path) or DATABASE_NAME attribute in metaData atributes. If login uses windows authentication, then DATABASE_NAME attribute should contain domain of the instance.

Attribute nameDescriptionType
NameDatabase user name which will be deleted
Reqiurd
string
UserTypeUser’s type
Reqiurd
enum
DropLoginIfNotUsed
(Custom attribute)
True – After delete user, connector will check if mapped login does not have any mapped users to another databases , then mapped login will be deleted.
False – After delete user, Connector will not check login to any users mapping
bool
LoginTypeLogin’s type
Required if login has windows authentication and UserType is not "NoLogin"
enum
Databases
(Custom attribute)
List of database names in which 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 database user in databases which have status another than normal.

In case only if 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 not set then connector will use baseDN or DATABASE_NAME attributes, if attributes do not exist connector return users from all databases.

Attribute nameDescriptionResponse type
IdUser’s idint
NameUser’s namestring
DatabaseUser’s databasestring
DatabaseRoleGranted database rolesarray
UserTypeUser’s typestring
LoginUser’s login namestring
LoginTypeLogin’s typestring
AuthenticationTypeUser’s authentication typestring
CreateDateUser’s creation datestring
DateLastModifiedThe last date of user modificationstring

Database object

Get-Databases – return databases
[-Database <array>] - array of database names or * - return all databases
[-SkipSystemObject] - skip system databases (like master)

If -Database parameter not set then connector will use baseDN or DATABASE_NAME attributes, if attributes do not exist connector return all databases.

Attribute nameDescriptionResponse type
IdId’s databaseint
NameDatabase namestring
FixedRoleDefault database’s rolesarray
UserDefinedRoleCustom rolesarray