Export (0) Print
Expand All

Backward Compatibility for Multiserver Configurations

SQL Server 2000

  New Information - SQL Server 2000 SP3.

Multiserver administration is the process of automating administration tasks across multiple instances of SQL Server. Use multiserver administration if you manage two or more servers and you want to centralize maintenance tasks.

In SQL Server 2000 SP3, the SQL Server Agent service account does not need to be a Windows administrator, unless you need to use the SQL Server Agent Proxy Account. The SQL Server Agent service account must be a member of the sysadmin fixed server role.

With multiserver administration, you must have at least one master server and at least one target server. A master server distributes jobs to and receives events from target servers. A master server stores the central copy of job definitions for jobs that run on target servers. Target servers connect periodically to their master server to update their list of jobs to perform. If a new job exists, the target server downloads the job and disconnects from the master server. After the target server completes the job, it reconnects to the master server and reports the status of the job.

Before you apply SP3, you must complete several steps to upgrade your SQL Server 2000 master/target server configuration. The changes that are introduced with SP3 are not compatible with SQL Server 7.0 target servers, or with any servers not running SQL Server 2000 SP3. This is a change from the original SQL Server 2000 functionality.

To upgrade your master/target server configuration

  1. Create a new master server (MSX) account on your master server. This is to prepare the target server or servers (TSX) for the upgrade. To do this, run the following commands.
    --Option A: Windows Authentication
    EXEC sp_grantlogin 'DOMAIN\user'
    --Option B: SQL Server Authentication – see detailed explanation below 
    EXEC sp_addlogin 'MSXAccount', 'MSXAccountPassword', 'msdb' 
    GO
    USE msdb
    GO
    EXEC sp_adduser 'MSXAccount'
    GO
    EXEC sp_addrolemember 'TargetServersRole', 'MSXAccount'
    GO

    You have the following options when choosing an MSX account:

    • Windows Authentication. This is the most secure option, as passwords do not need to be stored, and SQL Server and SQL Server Agent can be configured without local Windows administrator rights.

    • SQL Server Authentication. This requires the SQL Server Agent service accounts to have local Window administrator rights. The reason for this is that SQL Server stores the user name and password as an LSA (local security authority) secret, and access is restricted to local Windows administrators. You may create an account for all target servers, or one account per target server.

    Do not specify a SQL Server Agent probe account (<machine_name>_msx_probe_login). As part of the upgrade to SP3, SQL Server removes the old probe accounts because the target servers no longer use them.

  2. Upgrade your target servers to SP3 one at a time. (Before you apply the service pack, see Step 3 for more information about timing your upgrade.)

  3. To minimize downtime, run the extended stored procedure xp_sqlagent_msx_account on each target server shortly after the SP3 update is completed. For more information, see xp_sqlagent_msx_account below.

  4. Apply SP3 to your master server. The SP3 setup program removes the old _msx_probe accounts, as the target server(s) will not use these accounts any more. If an account own SQL Agent jobs, the account will not be removed, and you need to change the owner of the job(s) to another user and manually remove these accounts. If you want to continue to use the old _msx_probe account(s) that own SQL Agent jobs, you may need to change the password of the _msx_probe account.

SP3 includes a new extended stored procedure that allows you to configure the account that the SQL Server Agent target server uses to download instructions from an master server. This account is known as the MSX account or master server account.

xp_sqlagent_msx_account

The xp_sqlagent_msx_account extended stored procedure sets or retrieves the SQL Server Agent MSX account user name and password to or from the LSA (local security authority) secrets on the target server. Execute permissions of this extended stored procedure are restricted to the securityadmin fixed server role.

SQL Server Agent must be running to execute this extended stored procedure. In addition, if the account specified is a SQL Server login, SQL Server Agent must have local Windows administrator rights. The reason for this is that SQL Server Agent stores the user name and password as an LSA secret, and access is restricted to local Windows administrators.

Syntax

xp_sqlagent_msx_account

    {N'GET' |

    N'SET' | N'DEL', N'MSX_domain_name', N'MSX_username', N'MSX_password'

    }

Arguments

N'GET'

Retrieves the current SQL Server Agent MSX account. N'GET' is an nvarchar with no default. The password is not reported for security reasons.

N'SET'

Sets the account to be used as the SQL Server Agent MSX account. Use the MSX_username, and MSX_password parameters to specify the account to use as the SQL Server Agent MSX account. N'SET' is an nvarchar with no default.

N'DEL'

Deletes the SQL Server Agent MSX account.

MSX_domain_name

Reserved for future use.

MSX_username

The name of the Windows account to be used as the SQL Server Agent MSX account. Specify an empty string for this parameter and MSX_password to select Windows security. In this case, the SQL Server Agent service account credentials are used to log on to the MSX server. MSX_username is an nvarchar with no default.

MSX_password

The password for the SQL Server account specified in MSX_username. Specify an empty string for this parameter and the MSX_username to select Windows security. In this case, the SQL Server Agent service account credentials are used to log in to the MSX server. MSX_password is an nvarchar with no default.

Note  Parameters for xp_sqlagent_msx_account must be specified in order. Named parameters cannot be used.

Return Code Values

0 (success) or 1 (failure)

When xp_sqlagent_msx_account fails and returns 1, SQL Server generates an error message with information about the error.

Result Sets

If a SQL Server Agent MSX account has been set, xp_sqlagent_msx_account returns a result set with the following information when you specify N'GET'.

Column Data type Description
domain sysname N/A. Reserved for future use.
username sysname Account used as the SQL Server Agent MSX account.

If a SQL Server Agent MSX account has not been set, or if N'SET' is specified, no result set is returned.

Permissions

Execute permissions for xp_sqlagent_msx_account default to members of the securityadmin fixed server role.

Examples
  1. Retrieve the currently assigned SQL Server Agent MSX account

This example retrieves the account currently assigned for use as the SQL Server Agent MSX account.

EXEC master.dbo.xp_sqlagent_msx_account N'GET'
  1. Set the SQL Server Agent MSX Account to use Windows Authentication

This example sets the SQL Server Agent MSX account to use Windows Authentication.

EXEC master.dbo.xp_sqlagent_msx_account N'SET',
   N'',  -- Reserved for future use 
   N'',  -- MSX_username
   N''  -- MSX_password
C. Set the SQL Server Agent MSX account to use SQL Server Authentication

This example sets the SQL Server Agent MSX account to Ralph and specifies a password.

EXEC master.dbo.xp_sqlagent_msx_account N'SET',
   N'',  -- Reserved for future use 
   N'Ralph',  -- MSX_username
   N'RalphPwd'  -- MSX_password
D. Delete the SQL Server Agent MSX account

This example deletes the SQL Server Agent MSX account. This means that SQL Server Agent defaults to Windows integrated security authentication.

EXEC master.dbo.xp_sqlagent_msx_account N'DEL'

SQL Server now checks to ensure that the Agent job owner has permission to append or overwrite a file. This happens in three ways:

  • If the job owner is a member of the sysadmin fixed server role, the job can be written to the server.

  • If the job owner is a Windows user, SQL Server tests to see if the user has permission to write to the server.

  • If the job owner is a SQL Server user, SQL Server tests the SQL Server Agent proxy account for permission to write to the server. If the proxy account has not been set, no log will be written.

In all cases, jobs are written with SQL Server Agent credentials, but SQL Server now tests to ensure the user has permission to write to the server. Errors will appear in the job history, but the job steps will not fail if the log file cannot be written.

Show:
© 2014 Microsoft