< Blog

Updated-How to Create a SQL Server Computer Account Login

This blog post was originally published in early 2017, but since then newer versions of SQL Server were released, so I thought that it was time for an update. Here I’ll show you, step-by-step, how to create a SQL Server computer account login, how to grant SQL Server System Admin (SA) rights to the account and how to test the connection between the SQL Server and the computer account.

It is not always obvious how to add a SQL Server computer account login or how to grant SQL Server SA rights within SQL Server, but these are requirements when the SQL Server is remote to the primary System Center Configuration Manager (SCCM) site server or the CAS server.

As a prerequisite, you need to have SQL Server Management Studio (SSMS) installed, so before you get started, remember that in SQL Server 2012 and later, SSMS is NOT included with the ISO media. You may need to add an extra step here in order to download and install SSMS first. Keep in mind that you will need to install SSMS either on your workstation or on the server that hosts the SQL Server. For more information about how to install SSMS, please see my blog post entitled, “Where is SQL Server Management Studio (SSMS)?” That post will give you step-by-step instructions on how to install SSMS. Once SSMS is installed, you can then continue along with the rest of this blog post.

Create a SQL Server Computer Account Login

SQL Server Computer Account Login - SQL Server

Start by opening SQL Server Management Studio (SSMS) and connecting to the SQL Server. In my case, I’ll be connecting to cm-ssrs-cb1.

SQL Server Computer Account Login - Object Explorer

In the Object Explorer window, expand the Security | Logins nodes.

SQL Server Computer Account Login - New Login

Right-click on the Logins node and then click on New Login…

SQL Server Computer Account Login - Login Name

Note: Do NOT click on the OK button until after the next step.

In the Login name text box type:

<domain name>\<computer name>$

It is important to include the dollar $ sign because if you don’t, the login name will be treated as a user account and not as the computer account.

SQL Server Computer Account Login - Service Roles

Select the Service Roles node and then check the sysadmin checkbox. Finally, click on the OK button in order to close the window.

SQL Server Computer Account Login - Computer Account

With that last step completed, the SCCM site server computer account will be able to login immediately with SA rights.

Testing the Connection to the SQL Server Using the Computer Account

Now that you finished adding the computer account to the SQL Server, how do you test it in order to confirm that everything is working correctly? Simply use the local system account, run SSMS and see if you can connect to the server. Once connected, query the database to prove that you have SA rights. I’ll go into more detail about how to do this, so keep reading!

There are many different ways to run a local system account, but the easiest way is to use PsExec.

SQL Server Computer Account Login - Command Prompt

Open an elevated CMD prompt. Change the directory to PsExec. In my case PsExec is within the system path. Run the command:

psexec -s -i cmd

SQL Server Computer Account Login - Whoami

This command will open another CMD window. In the new window run the whoami command. This should show you that you have a CMD prompt running at nt authority\system. In other words, this is the local system account.

SQL Server Computer Account Login - SSMS

Change the directory to where SSMS is located and run SSMS.

SQL Server Computer Account Login - Connect Button

Be patient. SSMS might take a little while to load the first time, but please wait for it. In the Server name box, type the name of your SQL Server. In my case it is CM-SSRS-CB1. Notice that the User name is shown as NT AUTHORITY\SYSTEM. Click on the Connect button in order to continue.

SQL Server Computer Account Login - Query

At this point it really doesn’t matter what you query as long as it proves that you have access. In the screenshot above you can see that I ran a query to show all SSRS users. This should be more than enough to prove that everything working as expected.

If you have any questions about how to create a SQL Server computer account login, please feel free to contact me @GarthMJ.