It happens that you want to connect to an SQL Server using a System account. When you provision an Azure Sqlserver environment, the Sqlserver service accounts will be default NT AUTHORITY SYSTEM Account. To communicate with other Sqlservers, for instance, to support SSIS dataflow connections, you can then use the NT AUTHORITY SYSTEM Account to connect to the other machine.
When you try to add a computer name as a login at the source or target Sqlserver, it is impossible to provide windows credentials in SSMS for the computer name. They are always grayed out. My experience is always to keep trying to find a solution to such typical use cases. I was able to find that PSExec can be used to achieve this. I am a big fan of some of the tools from Sysinternals. These are the handy and relatively small footprint of the servers. One of the tools I have day-in-day-out is ZoomIt while doing presentations.
Step 1: Download PSTools from
Step 2: Extract and open a command prompt to the extracted location.
Step 3: Provide the below command
1
psexec -i -s ssms.exe
-i parameter allows the program to run to interact with the desktop of the specified session on the remote system.
-s parameter launches the process using the SYSTEM account.
Here is what we would see on the command prompt.
And it would launch an SSMS program with a User name populated as “NT AUTHORITYSYSTEM.”
When I disable that account, I was getting the below error in ERRORLOG
2018-03-06 17:32:04.666 Logon Error: 18470, Severity: 14, State: 1.
2018-03-06 17:32:04.666 Logon Login failed for user ‘WORKGROUPSQLSERVER2018$’. Reason: The account is disabled. [CLIENT: <local machine>]
Notice that we do not see NT AUTHORITYSYSTEM in Errorlog. We see a machine name followed by a dollar symbol. Since my machine is in a Workgroup called “WORKGROUP” and the machine name is SQLSERVER2016, we are seeing WORKGROUPSQLSERVER2018$
Have you ever used any such tool to troubleshoot anytime in your environment? What use cases were you able to use these tools in the past? Do let me know via comments.