When using SQL 2005 Management Studio Express, you can backup your database to anywhere on the local MSSQL 2000 server, which includes possibly overwriting critical system files. Users do not have this privilege when using Enterprise Manager to backup MSSQL 2000 databases. It appears that SQL 2005 Management Studio is allowing users to execute the extended stored procedure 'xp_availablemedia', but on MSSQL 2000 server only?
This vulnerability appears to allow any user logged into to the MSSQL 2000 server using SQL 2005 Management Studio Express, permission to overwrite any critical system file, when attempting to backup a database. This would be duplicated by logging into the server as a public user, right clicking on database -> tasks -> Back Up -> then 'Add' a new destination directory.
We would like to find out what is the cause of this, as it does not make sense since Enterprise Manger does not show this vulnerability when backing up. How can security be tightened to disallow this access?
Thank you
Have you checked the privilege for that user who has performed this action?
I believe the NetworkService account used by SQLExpress service and if builtin\admin has privileges on SQL Server 2000 instance then it is by default.
|||Note that NT Authority\Network Service is an ordinary, low-privileged account that by default does not have Administrative privileges in the operating system. Running SQL Server as NT Authority\Network Service is a security best practice precisely because it is a low privileged account that can't take over the host operating system even if a user manages to elevate privileges to run as the service. If you set SQL Server 2005 to run as Network Service during setup, setup.exe grants (on behalf of the administrator performing setup) read and write privileges to the %ProgramFiles%\Microsoft SQL Server directory and its subdirectories to Network Service.
Management Studio doesn't perform impersonation, so operations run as the logged in user on the server. File system operations are not allowed by the operating system if the user does not have privileges to perform the operation in the directory, so to see a directory for backup the logged in user has to have permission in Windows to see the directory. If you log in as SA, you are running as the service account and anything the service account can do, you can do as well. This isn't an elevation of privileges in the classic sense, it is just exposing the power that sysadmins already have in the server (that is, you are not elevating your privileges to be SA if you log in as SA in the first place). For this reason, it is a good idea to perform most maintenance with a lower privilege account, in this case perhaps one that is a member of just the db_backupoperator role in the database.
To tighten up security, run your server as a low-privileged account, grant that account read and write privileges only in a restricted set of directories (that must include the SQL Server binaries and data files), create special logins for common maintenance tasks that only have sufficient privileges to perform those tasks, and avoid logging in as a member of the Administrators group in Windows or as SA in SQL Server.
Hope this helps,
Steve
