Test SQL Server connection with sqlcmd using machine name

Question

How can I test a connection via machine name as the SQL logon name (ie mydomain\server01$ - as an IIS AppPool would connect) using sqlcmd?

I'm having trouble figuring out an application's SQL Server connection issue.

Answer 1

You would generally not find an Application pool using the computer account to connect to SQL Server. At most I would not want to allow that type of configuration.

You should be able to accomplish it by running the process as the local system account, NT AUTHORITY\SYSTEM. You can do this by using psexec. You would just specify -s parameter to execute it as the system account.

I think something like this should work:

psexec -s "sqlcmd -S myserver -E"

Answer 2

The computer account is only used if the app pool is using a local account and is trying to connect to a remote SQL server. Typically you would not want to add the machine account to the SQL server as that would permit any location account on that server to have access to the SQL server. The best way is to use a domain account as the app pool account.

But if you absolutely have to have it that way then you will need to log in as an account that is on the local IIS box and then attempt to connect to the remote SQL server.