MS SQL server AWS kerberos installation.md

Installation

1. Create a managed Directory Service

2. Launched Microsoft Windows Server 2019 with SQL Server 2019 Enterprise - ami-08d76971476b11b4f
   The following IAM policy roles are needed to connect to the AD properly:

   • AmazonEC2FullAccess
   • AmazonSSMManagedInstanceCore
   • AmazonSSMDirectoryServiceAccess

   Make sure during creation join to the existing domain dropdown set properly.

Kerberos

1. RDP to the server
2. Check that connected to the domain:
   wmic computersystem get domain
3. To install the Active Directory administration tools on Windows Server 2019
   • Open Server Manager from the Start screen by choosing Server Manager.
   • In the Server Manager Dashboard, choose Add roles and features,
   • In the Add Roles and Features Wizard choose Installation Type, select Role-based or feature-based installation, and choose Next.
   • Under Server Selection, make sure the local server is selected, and choose Features in the left navigation pane.
   • In the Features tree, open Remote Server Administration Tools, Role Administration Tools, select AD DS and AD LDS Tools, scroll down and select DNS Server Tools, and then choose Next.
   • Review the information and choose Install. When the feature installation is finished, the Active Directory tools are available on the Start screen in the Administrative Tools folder.
4. Create keytab file:
   ktpass /out mssql.keytab /princ [email protected] /mapUser mssql /pass PASSWORD_CHANGE_ME /crypto all /ptype KRB5_NT_PRINCIPAL
5. Check that TGT can be obtained:
   kinit -kt mssql.keytab [email protected]
6. Check that TGT obtained properly:
   klist

SQL Server

1. To change security authentication mode:
   • In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.
   • On the Security page, under Server authentication, select the new server authentication mode (Windows and SQL auth), and then click OK.
   • In the SQL Server Management Studio dialog box, click OK to acknowledge the requirement to restart SQL Server.
   • In Object Explorer, right-click your server, and then click Restart. If SQL Server Agent is running, it must also be restarted.
2. To enable the sa login
   • In Object Explorer, expand Security, expand Logins, right-click sa, and then click Properties.
   • On the General page, you might have to create and confirm a password for the login.
   • On the Status page, in the Login section, click Enabled, and then click OK.
3. Check authenication method (must be 2):
   sqlcmd -S localhost -U sa -P PASSWORD_CHANGE_ME -Q "EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode'"
   • 1: Windows Authentication
   • 2: SQL Server and Windows Authentication mode
4. Check authenication scheme (must be KERBEROS):
   sqlcmd -S localhost -U sa -P PASSWORD_CHANGE_ME -Q "SELECT s.session_id, s.original_login_name, c.net_transport, c.auth_scheme, c.local_net_address, c.local_tcp_port, s.program_name FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id) WHERE s.is_user_process = 1"
auth_scheme possible values:
   • SQL: When SQL Server authentication is used
   • NTLM: When NTLM authentication is used
   • KERBEROS: When KERBEROS authentication is used
5. Connect to the server:
   sqlcmd -U [email protected]