Testing SQL Connectivity with Machine Account

Many times during troubleshooting SQL Server Connectivity, we face the challenge to test our connectivity with the Machine accounts. For instance say you have a service running under local system or network service account and that makes a connection to SQL Server. In such scenario it will connect in the context of Machine Account. In a Profiler, you will see the account name as Machine\SYSTEM in a local scenario or Domain\Machine$ in a remote connection.

I have many times faced issues where a regular domain user is able to successfully connect to SQL Server but any application running under the built in accounts are failing.

Testing connectivity with these accounts had always been challenging. In the past I used the AT command to schedule a windows task to launch a command in interactive mode. This launches cmd.exe under the SYSTEM account. Then I can test the SQL connectivity from here using osql or odbcad32.exe

https://support.microsoft.com/kb/313565

This worked fine with Windows 2003 Server or Windows XP. But starting from Vista and higher, the AT command does not launch the task in interactive mode due to security reasons.

The alternative I found was to use the PSExec Tool from SysInternals.

Run a cmd from PsExec tool and the command will launch under the SYSTEM account.

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