Testing connection to SQL Server from a service running under Local System Account

Article
01/29/2010

We sometimes run into scenarios where an application or a windows service running under Local System Account needs to connect to SQL Server and encounter authentication Errors/issues with SQL Server. In SQL Developer support team, we have worked on customer issues where SCCM (System Center Configuration Manager), SCOM (System Center Operations Manager) or a 3^rd party service or application runs under Local System account and gets Windows authentication or Kerberos errors like below (or variations of these) while connecting to SQL Server-

For Windows built-in accounts like Local System, the correct syntax, setup and troubleshooting steps are not always obvious. So In this blog, we will provide a few setup and troubleshooting tips for the scenarios/issues mentioned above. Needless to say that we will focus only on those scenarios where we use Windows authentication (integrated security=true, integrated security=SSPI or trusted connection=true etc) in the application connection string for SQL Server. Also, we will NOT delve into ‘Whether we do or don’t recommend using Local System’ for service account that needs remote connectivity/access to SQL server.

What is Local System Account?

Local system account is a built-in account which has extensive privileges on the local computer. The local system account is an internal windows account and does not show up in User manager, cannot be added to any groups, and cannot have user rights assigned to it. On the other hand, the system account does show up on an NTFS volume in File Manager in the Permissions portion of the Security menu. The msdn link below talks in-depth about this account.

Local System Account

https://msdn.microsoft.com/en-us/library/ms684190(VS.85).aspx

Setting up access for Local System account in SQL Server:

In order for a windows service or application that runs under Local System account to connect to SQL Server, the Local System account (just like any other account) needs to be granted a login privilege and other appropriate permissions in SQL Server.

Scenario 1: If the windows service/application and SQL Server are on the same machine-

We need to grant login privilege and appropriate role for the ‘NT Authority\System’ account on the SQL Server (or Windows service) machine. For example, to create a login for NT Authority\System and grant it ‘sysadmin’ role we can run the T-SQL statement below in SQL Server Management Studio-

CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS WITH

DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

EXEC sp_addsrvrolemember 'NT AUTHORITY\SYSTEM', 'sysadmin';

The login and the role can also be created from SQL Server Management Studio.

1. In SQL Server Management Studio, open Object Explorer and connect to the server instance in which to create the new login.

2. Right-click the Security folder under this instance, point to New, and then click Login.

3. On the General page, enter NT AUTHORITY\SYSTEM in the login name box.

4. Select Windows Authentication.

5. On the Server Roles Page, select ‘sysadmin’ and click OK

NOTE: We don’t need to necessarily grant sysadmin role – this is just an example. The exact role should be determined based on what the application/service requires.

To verify the newly created login, we can run the query below:

SELECT * FROM sys.server_principals WHERE name LIKE 'NT AUTHORITY\SYSTEM%'

Scenario 2: If the windows service/application and SQL Server are on different machines-

We need to grant the above privileges to the machine account where the windows service runs, because that is the credential passed over the network. For example, if the windows service or application is running on a machine called ‘MyMachine’ in domain ‘MyDomain’ then credential MyDoman\MyMachine$ is passed over the network to SQL Server.

To create a login for the machine account in SQL Server and make it sysadmin, we can execute the statement below:

CREATE LOGIN [MyDoman\MyMachine$] FROM WINDOWS

DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

EXEC sp_addsrvrolemember ' MyDoman\MyMachine$', 'sysadmin';

To verify the newly created login, we can run the query below:

SELECT * FROM sys.server_principals WHERE name LIKE ' MyDoman\MyMachine%'

Testing connection in the context of Local System Account:

After successfully creating login for Local System account in SQL Server, we now need to test connectivity to SQL Server using this account. Usually, we would use a client tool like UDL (Universal Data Link) file or SSMS (SQL Server Management Studio) to test connectivity to SQL Server using windows authentication or SQL login.

The challenge here is – how do we run a UDL file or SSMS under local system account? These applications would normally run under the logged in windows user account. It could be possible that the issue we are troubleshooting does not reproduce when we run the test with logged in Windows user account. Fortunately, there are a several tools that we can take advantage of to accomplish this. We will list a few of them -

#1:

Use AT command to open a command window running under local system account. The AT command is a DOS Scheduler task command which by default runs under Local System account.

The simple syntax of AT command can be used to open a command window running under Local System account:

at time /interactive command

For example, if the current system time is 10:52 am, we can run AT to schedule a job one/two minute later, like below –

C:\test>at 10:54 /interactive sqlwb.exe

The above command will open SSMS (SQL Server 2005 Management Studio) with Local System account context on a Windows 2003 32-bit machine at 10:54

But on Windows Vista and above, due to security enhancements, this task will not run interactively but it will add a new process and may not be useful for live troubleshooting –that’s why our preferred tool is PsExec and this tool will work on all Windows OS.

#2 (preferred):

PsExec is a tool from Sysinternal, which can be found here:

https://technet.microsoft.com/en-us/sysinternals/bb897553.aspx

For example, if we want to run SQL Server 2005 Management Studio with Local System context, we can run the command below-

C:\test>psexec -i -s sqlwb.exe

Executing “Psexec.exe -i -s cmd.exe” or “C:\test>at 10:54 /interactive cmd.exe” from command prompt will open another command window on the machine running under local system account.

We can also open a command window on a remote machine by adding \\remotemachine option before the -i attribute, provided we have enough privileges. Then in the newly created command window we need to change the directory to the location where we have the UDL file (or any other executable) and then type the UDL file name to open it. After we open UDL file in this way, we can verify in the task manager that the executable rundll32.exe is running under SYSTEM account.

Once we are able to test connection from UDL/SSMS with Local System context (running udl/SSMS with psexec/AT and selecting windows authentication) we can carry on troubleshooting authentication issues based on the error message we get.

Common authentication errors

Some of the errors encountered when connecting with Local system account are:

1. Login failed for user 'NT AUTHORITY\SYSTEM'.

This typically is a permission issue –we need to create a valid login for 'NT AUTHORITY\SYSTEM' or 'MyDoman\MyMachine$' (as mentioned in the section ‘Setting up access for Local System account in SQL Server’) and ensure necessary role membership in SQL Server for the same.

2. Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Cannot generate SSPI context

These errors indicate Kerberos/NTLM issues with SQL Server and the troubleshooting steps should be same as all Kerberos issues in SQL server –which we are not going to delve into in this blog. There is plenty of documentation out there that we can reference to troubleshoot Kerberos errors. Here are a few favorite ones –

https://www.microsoft.com/downloads/details.aspx?FamilyID=99b0f94f-e28a-4726-bffe-2f64ae2f59a2&displaylang=en https://support.microsoft.com/kb/811889/en-us

In the context of our discussion, a few points to keep in mind are –

A. If the windows service that is running under Local System and connecting to SQL Server is a middle tier service (for example, a 3-tier web application or a linked server between 2 SQL Server instances), then we need to add SPNs for machine Account in Active directory and also set ‘trusted for delegation’ property on this machine Account.

For example, if the windows service (running under Local System) is located on a machine Name MyMachine and is a middle tier service in our troubleshooting scenario, then we need to look up the account MyMachine in Active Directory and add SPNs for the account MyMachine and also set ‘trusted for delegation’ on this account for Kerberos delegation to work properly.

B. If we consider purging tickets on the machine where the windows service is running, we need to run tools like KerbTray or klist in the context of Local System Account – using PsExcec, AT etc

3. Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

We need to check the ‘Local Security Policy-> User Rights Assignments on the SQL Server machine and ensure that the account MyDomain\MyMachine$ (MyMachine is the Netbios Name of the machine where the windows service or application is running with Local System Account) is not present under ‘Deny Access to this computer from the network’.

Author : Aruna(MSFT), SQL Developer Engineer & Azim(MSFT) , SQL Developer Technical Lead , Microsoft

Reviewed by : Snehadeep (MSFT) , SQL Developer Engineer & Praveen M,SQL Developer Technical Lead, Microsoft

Testing connection to SQL Server from a service running under Local System Account

Additional resources