problem running xp_cmdshell when Application Name set in connection string

Question

I have an app on 10 or so servers that runs a few xp_cmdshell statements against MSSQL 2008. It works fine on all the servers except for one. To make things worse, I can run all the commands in SQL Management Studio, but in the app, they don't work. I even made a temporary app to test and it works fine! But in the deployed app, I get a simple SQL error "Access Denied". I have narrowed it down to the connection string, if I include the application name

Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3};Application Name=TheGroovyApp

It throws access denied only when calling xp_cmdshell, normal SQL statements works fine. But if I remove the application name

Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3}

It works fine for both normal SQL statements and calls to xp_cmdshell. The strange thing is, its only happening on one of the ten servers. The only difference being that the server has SP1 and the others do not.

@@VERSION returns

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (VM) 

I am thinking that there is some sort of authentication that can be granted to applications, but I cant seem to find anything. I can replicate it in SQL Managment Studio by adding

Application Name=TheGroovyApp

to the Additional Connection Parameters tab on the Connect to Database Engine dialog when you create a new query or change its connection.

The simple test statement I use is

EXEC master..xp_cmdshell 'DIR F:\SomeDirectory'

If anyone could shed some light on what is happening it would be much appreciated.

EDIT:

OK after a bit more investigation its even more confusing.

If I set the Application Name to the following which is the default for .Net connections, it works fine.

Application Name=".Net SqlClient Data Provider"

I can run xp_subdirs without any problems no matter which settings I use

EXEC master..xp_subdirs 'F:\SomeDirectory' 

Now this is where it gets really weird. The first two fail, but the last one succeeds with the application name being set to my application name. But only if its xp_cmdshell that get called, xp_subdirs works with all three.

With Application Name in connection set

EXEC master..xp_cmdshell 'DIR F:\SomeDirectory' - Fails
master..xp_cmdshell 'DIR F:\SomeDirectory' - Fails
xp_cmdshell 'DIR F:\SomeDirectory' - Works
EXEC master..xp_subdirs 'F:\SomeDirectory' - Works
master..xp_subdirs 'F:\SomeDirectory' - Works
xp_subdirs 'F:\SomeDirectory' - Works

With Application Name not set in connection

EXEC master..xp_cmdshell 'DIR F:\SomeDirectory' - Works
master..xp_cmdshell 'DIR F:\SomeDirectory' - Works
xp_cmdshell 'DIR F:\SomeDirectory' - Works
EXEC master..xp_subdirs 'F:\SomeDirectory' - Works
master..xp_subdirs 'F:\SomeDirectory' - Works
xp_subdirs 'F:\SomeDirectory' - Works

The error returned in the query messages area in SQLMS when it fails

Msg 10011, Level 16, State 1, Line 1
Access denied.

This error message is only happening on the one server, I cannot replicate this on any others.

Answer 1

the database(s) associated with the application should be trustworthy in order to access the machine's command line.

Try

select name,is_trustworthy_on from sys.databases;

if your target database is not trustworthy then:

alter database db_name set trustworthy on;

but beware the compromise to security

Answer 2

It was a stab in the dark, but after we installed SQL Server 2008 SP2 on the server, the problem no longer occurs.