Using Microsoft SQL Server

This topic shows you how to add accounts so you can use Microsoft SQL Server with Managed Service for Microsoft Active Directory. You can use the following procedure for a Compute Engine instance running SQL Server or a self-managed instance.

Create local administrator account

To set up the SQL Server integration, create a local administrator account and temporarily grant it the sysadmin role. Learn how to configure Windows Service Accounts and Permissions.

By default, SQL Server grants the sysadmin role to all members of Users. Initially, the Authenticated Users group is the only member of Users. However, when a computer joins the domain, the Domain Users group is automatically added to the Users group on the computer.

To improve security, you should consider restricting the sysadmin role to a smaller set of users. Learn about Roles in SQL Server.

Joining the SQL Server instance to the domain

Next, join the instance that is running SQL Server to the Managed Microsoft AD domain. If the instance is already joined, you can skip to adding logins.

To join the instance to the domain, complete the following steps:

  1. Use the local administrator account to connect to the instance with Remote Desktop Protocol (RDP).
  2. Join the instance to the domain.
  3. Restart the instance.

Note that after you complete joining the instance to the domain, the local administrator account will no longer work for Windows Authentication to SQL unless you explicitly allow it.

Adding logins

  1. Use the local administrator account to connect to the SQL Server instance using RDP.
  2. Open Microsoft SQL Server Management Studio (SSMS).
  3. For Authentication, select Windows Authentication to log in with the built-in local administrator account.
  4. Select Connect.
  5. In Object Explorer, select Security.
  6. Right-click Logins, and then select New Login from the menu.
  7. For Login name, select Windows authentication, and then enter domain-name\username. username can be an Active Directory username, group name or built-in security principal.
  8. On the Server Roles page, select the server roles to grant to the Active Directory user.
  9. Select the General page, and then click Ok.

These new logins now work when you use Windows Authentication with SSMS.

Adding RDP permissions

Add the new logins to the local Remote Desktop Users group to grant them permission to RDP into the SQL Server instance. Learn how to allow log on through Remote Desktop Services.