Re: EXEC master..xp_cmdshell Prevention

From: Beth Breidenbach (bbreidenbach@mindspring.com)
Date: 10/23/02 

From: "Beth Breidenbach" <bbreidenbach@mindspring.com>
Date: Tue, 22 Oct 2002 15:20:02 -0700

Robert,

I take it you're using string concatenation to build your sql statements. I
_strongly_ recommend against this as it opens up a whole slew of
vulnerabilities, including the xp_cmdshell one you're asking about. For
example, since your login is a dbo a user could: drop your database, alter
your table structures, modify your stored procedures, etc...... Far far far
better to use parameterized stored procedures and to pass those parameters
in via the ado Command object. And of course, once inside the stored
procedure you still shouldn't be doing string concatenation to build a query
(which would just move the vulnerability from outside the sproc to inside
it).

Now, for some housekeeping issues. Let's assume someone figures out how to
compromise your database and get sa privileges --> xp_cmdshell runs under
the privileges granted to your MSSQLServer service. If I were a betting
person, I could probably earn $$ wagering that people's services were
running as either the SYSTEM account or a domain admin account -- both of
which are way too dangerous. Your SQLServer service requires no more than
domain guest privileges. (It often requires _local_ admin privileges, but
not _domain_.) This change prevents xp_cmdshell from being used to take
your LAN.

Next item -- using dbo for a web application's login is dangerous, for the
reasons mentioned in the first paragraph. Many developers use dbo for their
app because it makes their development-time work easier. You should have
two logins -- one with developer-level privileges (dbo) and one for lower
runtime application privileges. Change the connection string for the
published version (after you've tested it locally).

As to your original question -- If I create a dbo user and attempt to exec
xp_cmdshell via Query Analyzer I receive permission denied errors. It
sounds as though either a) your connection string isn't what you think it is
(sometimes happens if one or two random asp pages have their connection
strings hard-coded while the rest of the app shares a single connection
string) or b) your permissions didn't set the way you think they did.

Fire up Query Analyzer, connect as your SQL login user and attempt to exec
the sproc. What happens? If you receive an error in Query Analyzer but not
in your app, then your app's connection string is goofed up somewhere. Use
SQL Profiler to capture your application's request and see what login the
page used for its connection. If the sproc works in Query Analyzer, then
you need to look more closely at your permissions. Let us know how this
part turns out and we can try to help you further.

Best of luck,

Beth

"Robert Robbins" <rrobbins@kolbnetworks.com> wrote in message
news:urao9elubq5024@corp.supernews.com...
> Hello SQL Server Experts,
>
> I am trying to prevent the SQL Injection vulnerability which allows anyone
> to execute system commands using the xp_cmdshell extended stored
procedure.
> My ASP web application uses a connection string with the username and
> pasword of a new login account I created. The database has just one user,
my
> new login account, as the database owner. I've made sure the login account
> has no Server Roles and does not have Master database access.
> Unfortunately, I am still able to execute that extended stored
procedure
> simply by pasting the following code in a text box field used in an UPDATE
> SQL statement. 'EXEC master..xp_cmdshell '[some system command]'--
> Nothing I've tried is preventing me from executing that extended
stored
> procedure or any other dangerous stored procedure. Do I need to create an
> application role to use in my connection string? Is it the guest user in
the
> master database that is providing this permission? Only the sa login
account
> has execute permission for this extended stored procedure.
>
> Robert Robbins
> Kolb Net Works
>
>
>
>
>
>

Relevant Pages

  • Re: EXEC master..xp_cmdshell Prevention
    ... > I found that my web application did use a connection string in a file ... > stored procedures for every SQL statement would be impractical. ... I have created a new login account ...
    (microsoft.public.sqlserver.security)
  • Re: Cannot connect using sa
    ... Make sure you have it configured to allow both SQL Server ... >not if I use sqlcmd -S localhost\SQLEXPRESS -U and type in the password. ... >Login failed for user 'sa'. ... >from the connection string, couldn't connect, then when I put it back I ...
    (microsoft.public.sqlserver.setup)
  • Re: EXEC master..xp_cmdshell Prevention
    ... way to significantly reduce vulnerabilities around dynamic string execution. ... > I found that my web application did use a connection string in a file ... I have created a new login account ...
    (microsoft.public.sqlserver.security)
  • Re: EXEC master..xp_cmdshell Prevention
    ... stored procedures for every SQL statement would be impractical. ... I have created a new login account ... > sounds as though either a) your connection string isn't what you think it ...
    (microsoft.public.sqlserver.security)
  • Re: Can no longer connect as sa
    ... Is there a reason you need to connect using SQL authentication? ... > Login failed for user 'sa'. ... > I can't figure out what is slightly different in the connection string. ...
    (microsoft.public.sqlserver.setup)