SQL Server SA Account Won't Give Access on Windows 11 Machines

Introduction

Three machines at home have SQL Server 16.0.1000.6 installed, running on Windows 11. The SA (System Administrator) account, which is typically used for installation and configuration tasks, is not providing the necessary access to these machines. In this article, we will explore the reasons behind this issue and provide solutions.

Reason Behind the Issue

Windows 11, by default, does not allow local accounts to be added to the SQL Server sysadmin fixed server role. This security measure is in place to prevent unauthorized access to the SQL Server instance. However, in cases where you need to use the SA account for specific tasks, you can bypass this restriction.

Solution 1: Using Runas

You can use the Runas command in the Command Prompt to execute SQL Server Management Studio (SSMS) as the SA account. Here's how:

1. Open the Command Prompt as an administrator.
2. Type the following command and press Enter:

 runas /user:@ "C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"
 

Replace with the actual username of the SA account and with the name of the machine where SQL Server is installed. Note that you may need to enter the password for the SA account when prompted.

Solution 2: Modifying Group Policy

You can modify the Group Policy settings to allow local accounts to be added to the SQL Server sysadmin fixed server role. Here's how:

1. Press the Windows key + I to open the Settings app.
2. Click on "Accounts" and then "Work or school access."
3. Click on "Add a work or school account" and sign in with an administrator account.
4. Click on "Connected apps" and then "Add a line of text."
5. Enter the following text:

 Windows Defender Firewall with Advanced Security
 Local Policy
 Database Engine and SQL Server Analysis Services Rules
 Allow remote servers to connect to this machine (TCP)

Click "Save" and then close the Settings app.

Now, open the Local Group Policy Editor:

6. Press the Windows key + R, type "gpedit.msc" and press Enter.
7. Navigate to:

 Local Computer Policy\Computer Configuration\Windows Settings\Security Settings\Windows Firewall with Advanced Security\Inbound Rules

Find the rule named "SQLServerMSSQLUser$\" and double-click it.

Set the "Action" to "Allow the connection" and click "OK."

Now, open SQL Server Management Studio as the local administrator:

10. Open the Command Prompt as an administrator.
11. Type the following command and press Enter:

 runas /user: "C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\Ssms.exe"
 

Replace with the actual username of a local administrator account. Once SSMS is open, you can add the SA account to the sysadmin fixed server role.

Summary

In summary, the SA account does not have the necessary access to SQL Server instances on Windows 11 machines by default. You can bypass this restriction by using the Runas command or modifying the Group Policy settings. The Runas command allows you to execute SQL Server Management Studio as the SA account, while modifying the Group Policy settings allows local accounts to be added to the sysadmin fixed server role.