Windows user setup for Amazon EC2 High Availability for SQL Server

To grant SQL Server access for NT AUTHORITY\SYSTEM

The following steps need to be repeated on every SQL Server instance on the Amazon EC2 instance. Amazon EC2 SQL HA obtains High Availability metadata on all SQL Server installs on the Amazon EC2 instance, so the default local user needs to be able to query SQL Server across all SQL Server instances.

• Connect to your Amazon EC2 instance and open SQL Server Management Studio, then run the following SQL Server command on each SQL Server instance. This command creates a SQL Server login for NT AUTHORITY\SYSTEM and grants minimal read-only SQL Server permissions for this user.

  -- Create SQL Server login for default local user
  IF NOT EXISTS (SELECT name FROM master.sys.server_principals WHERE name = 'NT AUTHORITY\SYSTEM')
  BEGIN
      CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
  END
  
  USE [master]
  GO
  
  IF NOT EXISTS (SELECT name FROM master.sys.sysusers WHERE name = 'NT AUTHORITY\SYSTEM')
  BEGIN
      CREATE USER [NT AUTHORITY\SYSTEM] FOR LOGIN [NT AUTHORITY\SYSTEM]
  END
  GO
  
  -- Grant database permissions
  USE [master]
  GO
  
  IF NOT EXISTS (SELECT name FROM master.sys.database_principals WHERE name = 'db_role_ec2_sql_ha')
  BEGIN
      CREATE ROLE [db_role_ec2_sql_ha]
  END
  
  GRANT VIEW DATABASE STATE to [db_role_ec2_sql_ha]
  GO
  
  ALTER ROLE [db_role_ec2_sql_ha] ADD MEMBER [NT AUTHORITY\SYSTEM]
  GO
  
  -- Grant server permissions
  USE [master]
  GO
  
  IF NOT EXISTS (SELECT name FROM master.sys.server_principals WHERE name = 'svr_role_ec2_sql_ha')
  BEGIN
      CREATE SERVER ROLE [svr_role_ec2_sql_ha]
  END
  
  GRANT VIEW SERVER STATE TO [svr_role_ec2_sql_ha]
  GRANT VIEW ANY DEFINITION TO [svr_role_ec2_sql_ha]
  GRANT VIEW ANY DATABASE TO [svr_role_ec2_sql_ha]
  GO
  
  ALTER SERVER ROLE [svr_role_ec2_sql_ha] ADD MEMBER [NT AUTHORITY\SYSTEM]
  GO

To create and configure a domain user

1. Create a domain user

   This step differs based on the type of Active Directory (AD) being used, and assumes the Amazon EC2 instances you wish to enable for SQL HA standby detection are already joined to this domain. For an Amazon managed Microsoft AD, use the following Amazon Amazon CLI commands to create a new domain user. Replace username and password with your desired username and password.

   aws ds-data create-user \
       --directory-id directory-id \
       --sam-account-name "username"

   Then assign a password to the domain user:

   aws ds reset-user-password \
       --directory-id directory-id \
       --user-name "username" \
       --new-password "password"

2. Create an Amazon secret containing credentials

   Save the Windows domain user credentials to an Amazon secret. The domain user's username must be saved in the following format: directory-netBIOS-name\username. The directory-netBIOS-name is the directory NetBIOS name of your AD.

   aws secretsmanager create-secret \
       --name "domain-user-credentials" \
       --description "Domain user credentials for EC2 SQL HA standby detection." \
       --secret-string "{\"username\":\"directory-netBIOS-name\\username\",\"password\":\"password\"}"

3. Grant SQL Server access for domain user

   Connect to your Amazon EC2 instance and open SQL Server Management Studio, then run the following SQL Server command on each SQL Server instance. Replace username with the username you selected and directory-netBIOS-name with the AD's directory NetBIOS name.

   -- Create SQL Server login for domain user
   IF NOT EXISTS (SELECT name FROM master.sys.server_principals WHERE name = 'directory-netBIOS-name\username')
   BEGIN
       CREATE LOGIN [directory-netBIOS-name\username] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
   END
   
   USE [master]
   GO
   
   IF NOT EXISTS (SELECT name FROM master.sys.sysusers WHERE name = 'directory-netBIOS-name\username')
   BEGIN
       CREATE USER [directory-netBIOS-name\username] FOR LOGIN [directory-netBIOS-name\username]
   END
   GO
   
   -- Grant database permissions
   USE [master]
   GO
   
   IF NOT EXISTS (SELECT name FROM master.sys.database_principals WHERE name = 'db_role_ec2_sql_ha')
   BEGIN
       CREATE ROLE [db_role_ec2_sql_ha]
   END
   
   GRANT VIEW DATABASE STATE to [db_role_ec2_sql_ha]
   GO
   
   ALTER ROLE [db_role_ec2_sql_ha] ADD MEMBER [directory-netBIOS-name\username]
   GO
   
   -- Grant server permissions
   USE [master]
   GO
   
   IF NOT EXISTS (SELECT name FROM master.sys.server_principals WHERE name = 'svr_role_ec2_sql_ha')
   BEGIN
       CREATE SERVER ROLE [svr_role_ec2_sql_ha]
   END
   
   GRANT VIEW SERVER STATE TO [svr_role_ec2_sql_ha]
   GRANT VIEW ANY DEFINITION TO [svr_role_ec2_sql_ha]
   GRANT VIEW ANY DATABASE TO [svr_role_ec2_sql_ha]
   GO
   
   ALTER SERVER ROLE [svr_role_ec2_sql_ha] ADD MEMBER [directory-netBIOS-name\username]
   GO

To create and configure a local user

1. Create a local user on the Amazon EC2 instance

   Connect to your Amazon EC2 instance and open PowerShell as Administrator, then execute the following command. Replace username and password with your desired username and password.

   New-LocalUser -Name "username" -Password (ConvertTo-SecureString "password" -AsPlainText -Force) -Description "Local user for EC2 SQL HA standby detection."

2. Create an Amazon secret containing credentials

   Save the Windows local user credentials to an Amazon secret.

   aws secretsmanager create-secret \
       --name "local-user-credentials" \
       --description "Local user credentials for EC2 SQL HA standby detection." \
       --secret-string "{\"username\":\"username\",\"password\":\"password\"}"

3. Grant SQL Server access for local user

   Connect to your Amazon EC2 instance and open SQL Server Management Studio, then run the following SQL Server command on each SQL Server instance. Replace username with the username you selected and COMPUTERNAME with the Amazon EC2 instance computer name. You can retrieve the computer name with the PowerShell command $env:COMPUTERNAME.

   -- Create SQL Server login for local user
   IF NOT EXISTS (SELECT name FROM master.sys.server_principals WHERE name = 'COMPUTERNAME\username')
   BEGIN
       CREATE LOGIN [COMPUTERNAME\username] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
   END
   
   USE [master]
   GO
   
   IF NOT EXISTS (SELECT name FROM master.sys.sysusers WHERE name = 'COMPUTERNAME\username')
   BEGIN
       CREATE USER [COMPUTERNAME\username] FOR LOGIN [COMPUTERNAME\username]
   END
   GO
   
   -- Grant database permissions
   USE [master]
   GO
   
   IF NOT EXISTS (SELECT name FROM master.sys.database_principals WHERE name = 'db_role_ec2_sql_ha')
   BEGIN
       CREATE ROLE [db_role_ec2_sql_ha]
   END
   
   GRANT VIEW DATABASE STATE to [db_role_ec2_sql_ha]
   GO
   
   ALTER ROLE [db_role_ec2_sql_ha] ADD MEMBER [COMPUTERNAME\username]
   GO
   
   -- Grant server permissions
   USE [master]
   GO
   
   IF NOT EXISTS (SELECT name FROM master.sys.server_principals WHERE name = 'svr_role_ec2_sql_ha')
   BEGIN
       CREATE SERVER ROLE [svr_role_ec2_sql_ha]
   END
   
   GRANT VIEW SERVER STATE TO [svr_role_ec2_sql_ha]
   GRANT VIEW ANY DEFINITION TO [svr_role_ec2_sql_ha]
   GRANT VIEW ANY DATABASE TO [svr_role_ec2_sql_ha]
   GO
   
   ALTER SERVER ROLE [svr_role_ec2_sql_ha] ADD MEMBER [COMPUTERNAME\username]
   GO