Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
Are you sure it's not something as simple as the total length of your execution string? In all your examples, the longer one fails while the shorter one succeeds. How long is your overall string, and how does that compare to the length of your variable? –  Bob Apr 22 '13 at 11:48
    
I am sure it is not the length, SQLMS does exactly the same thing when I set the application name in the connection window. I would also get a incomplete SQL error instead of the "Access Denied". It would also have the same problem with the xp_subdirs which it doesn't. I'll modify my question to include them. I have run the test on several test SQL servers which are R2 SP1 and its fine on those. It is only this one server that has the problem. –  John Petrak Apr 22 '13 at 21:37
    
Is this server having the same SQL version / Service Pack installed as others (ones that are not having the issue) ? Also, is the account privileged same on other servers ? There must be something different on this... as you are experiencing issues with this particular server. –  Kin Apr 22 '13 at 22:08
    
One other production server is R2 SP1, the others are still R2 to be upgraded to SP1. But it works fine on all of them them. The test servers are R2 SP1 and it works fine on them as well. So it seems that the version is not the issue. I have two sysadmin accounts that I am testing with, one is a windows account and the other a SQL account. Both have the problem on this one server. But only if the application name is set in the connection. They are fine otherwise. It doesn't make any sense to me. –  John Petrak Apr 22 '13 at 22:35
    
What are the other differences -- however trivial -- between the two servers? –  Colin 't Hart Apr 23 '13 at 9:06

2 Answers 2

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

share|improve this answer
    
Unfortunately that did not work, msdb was the only one that was trustworthy on all the servers, but I changed it anyway to test if it would work. No Luck. –  John Petrak Apr 30 '13 at 3:26
up vote 0 down vote accepted

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.