2.7 Configuring SQL Server User in Security Manager

To run the Discovery_SQLServer Knowledge Script with a specific SQL/Windows authentication user, you must configure your user login and password information in the Custom tab of AppManager Security Manager. This is the user that AppManager will use to login to SQL Server instances. You can either configure a default user for all the instances, or a specific user for specific SQL Server instances. You can also use a non-default TCP port for SQL Server instance or Availability Group Listener by configuring the AppManager Security Manager.

NOTE:This module does not use the user specifications configured in Security Manager for the earlier versions of SQL module.

2.7.1 Configuring SQL Server User

To use a specific SQL/Windows authentication user account, use the following configuration:

Field	Description
Label	SQLServer
Sub-label	Do one of the following: For SQL Server Instance: UserName or UserName$InstanceName. For example: Type sa, sa$sqlserverinst1, or amdom001\user1$sqlserverinst1 For AlwaysOn Availability Group Listener Node: Specify the availability group listener node name as the instance name, UserName or UserName$AvailabilityGroupListenerNode. For example: Type sa, sa$aglistener1, or amdom001\user1$aglistener1 NOTE:For Windows user, specify the username in the DomainName\UserName format. For example: amdom001\user1$sqlserverinst1 A user without any specific instance is the default user. For Windows Authentication, if you leave this field blank, AppManager uses the NetIQ service account to log in to the SQL Server.
Value 1	Password for the user.
Value 2	Leave this field blank. NOTE:Type blank in this field if the password in the Value 1 field is left empty, that is, the user account does not have a password.
Value 3	Leave this field blank.
Extended application support	Required field. Encrypts the user name and password in Security Manager. Do not leave this option unselected.

2.7.2 Configuring non-default TCP port

You can specify the non-default TCP port that is used by a SQL Server or an Availability Group Listener in the AppManager Security Manager.

NOTE:This feature is only supported on AppManager for Microsoft SQL Server 8.2 or later.

To use a non-default TCP port for the SQL Server or the Availability Group Listener, use the following configuration:

Field	Description
Label	SQLServer
Sub-label	Specify the username in the InstanceName_TCPPort format. For example: sqlserverinst1_TCPPort. NOTE:The InstanceName must be the Virtual node name.
Value 1	Specify the TCP port. For example: 1469, where 1469 is the TCP port used by the SQL Server or the Availability Group Listener.
Value 2	Leave this field blank.
Value 3	Leave this field blank.
Extended application support	Required field. Encrypts the user name and password in Security Manager. Do not leave this option unselected.

2.7.3 Specifying the User Name in the Knowledge Script

To run a SQL Server Knowledge Script with a specific Windows/SQL authentication user, you must specify a user name to access the SQL Server instances. You can specify a default user name (a user without any instance), a user name with specific instances, or a combination of both. You can specify multiple user names separated by a comma.

The users that you specify in the Knowledge Script must be configured in Security Manager before running the script. For more information, see Section 2.7.1, Configuring SQL Server User.

NOTE:When running a Knowledge Script, you can specify only one default user in the User name parameter.

The different use cases below provide examples of different types of authentication. All the users in the examples are already configured in Security Manager.

Windows Authentication

Use Case 1 - User Name with Default User

Use amdom001\user1 for User name in a Knowledge Script. When you run this script on three instances, SQLServerInst1, SQLServerInst2, and SQLServerInst3, the script runs on all the instances successfully. On all the instances, this script uses the default amdom001\user1 user configuration.

The user name without any instance name is used as the default user for all instances.

Use Case 2 - User Name with Specific Instances

Use amdom001\user2$SQLServerInst1 for User name in a Knowledge Script. When you run this script on two instances, SQLServerInst1 and SQLServerInst2, this script runs successfully on the SQLServerInst1 instance with this amdom001\user2$SQLServerInst1 user configuration. On SQLServerInst2, this script fails, because there is no specific or default user available for this instance.

Use Case 3 - User Name with Specific Instances and Default User

Use amdom001\user1,amdom001\user2$SQLServerInst1 for User name in a Knowledge Script. When you run this script on three instances, SQLServerInst1, SQLServerInst2, and SQLServerInst3, this script runs on all the instances successfully. On SQLServerInst1 instance, this script uses this amdom001\user2$SQLServerInst1 user configuration and on SQLServerInst2, and SQLServerInst3, this script uses the amdom001\user1 user configuration.

The user name without any instance name is used as the default user for all instances.

NOTE:User with a specific instance takes precedence over the default user.

Use Case 4 - User Name with Specific Instances and Default User in Incorrect Format

Use amdom001\user2$SQLServerInst1,user1 for User name in a Knowledge Script. When you run this script on three instances, SQLServerInst1, SQLServerInst2, and SQLServerInst3, this script runs properly on SQLServerInst1 instance. This script fails on SQLServerInst2, and SQLServerInst3, because the user name is not formatted properly in the DomainName\UserName format.

Use Case 5 - User Name Field as Blank

If you select Windows Authentication in the Authentication field and leave the User name field blank in the Knowledge Script, then the script uses the default NetIQ service account user to login to the SQL Server.

SQL Server Authentication

Use Case 1 - User Name with Default User

Use sa for User name in a Knowledge Script. When you run this script on three instances, SQLServerInst1, SQLServerInst2, and SQLServerInst3, this script runs on all the instances successfully. On all the instances, this script uses the default sa user configuration.

The user name without any instance name is used as the default user for all instances.

Use Case 2 - User Name with Specific Instances

Use sa1$SQLServerInst1 for User name in a Knowledge Script. When you run this script on two instances, SQLServerInst1 and SQLServerInst2, this script runs successfully on SQLServerInst1 instance with this sa1$SQLServerInst1 user configuration. On SQLServerInst2, this script fails, because there is no specific or default user available for this instance.

Use Case 3 - User Name with Specific Instances and Default User

Use sa,sa1$SQLServerInst1 for User name in a Knowledge Script. When you run this script on three instances, SQLServerInst1, SQLServerInst2, and SQLServerInst3, this script runs on all the instances successfully. On SQLServerInst1 instance, this script uses this sa1$SQLServerInst1 user configuration and on SQLServerInst2, and SQLServerInst3, this script uses the sa user configuration.

The user name without any instance name is used as the default user for all instances.

NOTE:User with a specific instance takes precedence over the default user.

Use Case 4 - User Name Field as Blank

If you select SQL Server Authentication and leave the User name field blank in the Knowledge Script, then the script raises an error event.

The figure below displays the user specifications that is used by SQL Server instances while running a knowledge script.