How do I allow a domain user to access the SQL Server instance on an EC2 instance?

Short description

By default, only the built-in local administrator account can access a SQL Server instance launched from an Amazon Web Services (AWS) Windows AMI. You can use SQL Server Management Studio (SSMS) to add domain users so that they can access and manage SQL Server.

Resolution

To give a domain user access to the SQL Server instance, follow these steps:

1. Connect to your instance using Remote Desktop Protocol (RDP) as a local administrator.
2. Open SSMS. For Authentication, choose Windows Authentication to log in with the built-in local administrator.
3. Choose Connect.
4. In Object Explorer, expand Security.
5. Open the context (right-click) menu for Logins, and then select New Login.
6. For Login name, select Windows authentication. Enter DomainName\username, replacing DomainName with your domain NetBIOS name, and username with your Active Directory user name.
7. On the Server Roles page, select the server roles that you want to grant to the Active Directory user. For more information, see Server-level roles on the Microsoft Ignite website.
8. Select the General page, and then choose Ok.
9. Log out from the instance, and then log in again as a domain user.
10. Open SSMS. For Authentication, choose Windows authentication to log in with your domain user account.
11. Choose Connect.

Note: Performing these steps allows the user to access the SQL Server tables.

Related information

Tutorial: Get started with Amazon EC2 Windows instances

Troubleshoot EC2 Windows instancesServer-level roles