Configuring a SQL Server instance without a system administrator password

You can configure PATROL for Microsoft SQL Server to monitor SQL Server instances without the sa password. The configuration uses the privileged login to add stored procedures and to add the monitoring user to the monitored databases. After the configuration, the sa user name is stored in the pconfig file but the password is not stored in the pconfig file. This configuration sets the value of the /MSSQLServer/SQLServerInstanceName/GENUSERFLAG variable to 1.

In the SQL Config Setup dialog box, the top section is used by a SQL Server login or Windows user with SQL Server admin privileges. The bottom section is used by a SQL Server login or Windows user with SQL Server user privileges, who is defined as a monitoring user.

Note

After you execute the KM Commands > KM Admin > SQL Config Set up menu command, the following message is displayed for a limited time in the system output window: close: bad channel number. You can ignore this message.

The following table lists the configuration status of the authentication mode used in the SQL Config Setup dialog box.

Admin login mode used by PATROL for

Microsoft SQL Server

User login mode used by PATROL for Microsoft SQL Server

Standard 1

Integrated 2

Standard 3

Configured

Not supported

Integrated 4

Configured

Configured

  1. refers to a SQL Server login 
  2. refers to a Windows user 
  3. refers to a SQL Server login with admin privileges 
  4. refers to a Windows user with SQL Server admin privileges

To configure a SQL Server using the standard security mode for the SQL Server admin and user logins

  1. Open the SQL Config Setup dialog box by choosing KM Commands > KM Admin > SQL Config Set up.
  2. In the Security mode to use with this login check box, select Standard.
  3. Enter the SQL Server login name and password of the login who has admin privileges.
  4. In the Login used by Patrol for SQL KM Monitoring section, select Standard.
  5. Enter the login name and password to use for general user monitoring.
     If the login does not exist in the SQL Server, it will be created.
  6. Click Verify.
     A confirmation message is shown.
  7. Click DONE to exit

The selected server instance is configured for the standard security mode for the SQL Server login.

To configure a SQL Server using the integrated security mode for the SQL Server admin and user logins

  1. Open the SQL Config Setup dialog box by choosing KM Commands > KM Admin > SQL Config Set up.
  2. In the Security mode to use with this login check box, select Integrated.
  3. Enter the Windows user name and password for the user who has SQL Server admin privileges.
  4. In the Login used by Patrol for SQL KM Monitoring section, select Integrated.
  5. Enter the Windows user name and password for the user who has SQL Server user privileges.
     If the Windows user has not been registered in the SQL Server, it will be registered.
  6. Click Verify.
     A confirmation message is shown.
  7. Click DONE to exit.

The selected server instance is configured for the integrated security mode for the Windows user.

To configure a SQL Server using the integrated security mode for the SQL Server admin login and standard security mode for the user login

  1. Open the SQL Config Setup dialog box by choosing KM Commands > KM Admin > SQL Config Set up.
  2. In the Security mode to use with this login check box, select Integrated.
  3. Enter the Windows user name and password for the user who has SQL Server admin privileges.
  4. In the Login used by Patrol for SQL KM Monitoring section, select Standard.
  5. Enter the login name and password for general user monitoring.
     If the login does not exist in the SQL Server, it will be created.
  6. Click Verify.
     A confirmation message is shown.
  7. Click DONE to exit.

The selected server instance is configured for the standard security mode for the SQL Server login.

To configure a SQL Server using the standard security mode for the SQL Server admin login and integrated security mode for the user login

The combination of using the admin login with standard security mode and the user login with integrated security mode is not supported. If you try to configure the SQL Server instance in this way, the CheckConfiguration parameter goes into an alarm state, and you cannot configure PATROL for Microsoft SQL Server for general user monitoring.

If you want to use PATROL for Microsoft SQL Server in this security combination, use the KM Commands > KM Admin > Setup/Configure KM > SQL Server Instance Setup menu command to configure PATROL for Microsoft SQL Server. This configuration stores the SQL Server admin login password information in the pconfig file and disables the general user monitoring mode.

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*