Remedy 1: Never Ever Connect as sysadmin

Remedy #1: Never Ever Connect as sysadmin

Earlier I pointed out the error of making connections to SQL Server, or any other database server, as sysadmin from an application such as Web service or a Web page. If you see a connection string that connects to the database as a sysadmin account, file a bug and get it fixed. You are violating the principles of least privilege and defense in depth if you use a sysadmin-like account to connect from your Web application to the database.

Most Web-based applications do not need the capabilities of sysadmin to run; most database-driven applications allow users to query data and, to a lesser extent, add and update their own data. If the connection is made as sysadmin and there is a bug in the SQL code, such as one that allows injection attacks, an attacker can perform any task sysadmin can, including the following:

• Delete (drop) any database or table in the system

• Delete any data in any table in the system

• Change any data in any table in the system

• Change any stored procedure, trigger, or rule

• Delete logs

• Add new database users to the system

• Call any administrative stored procedure or extended stored procedure.

The potential for damage is unlimited. One way to mitigate this issue is to support authenticated connections by using native operating system authentication and authorization by setting Trusted_Connection=True in the connection string. If you cannot use native authentication techniques and sometimes you should not you should create a specific database account that has just the correct privileges to read, write, and update the appropriate data in the database, and you should use that to connect to the database. This account should be regularly checked to determine what privileges it has in the database and to make sure an administrator has not accidentally given it capabilities that could compromise the system.

Perhaps the most dangerous aspect of running as sysadmin is the possibility that an attack could call any administrative stored procedure. For example, SQL Server includes extended stored procedures such as xp_cmdshell through which an attacker can invoke shell commands. Oracle databases include utl_file, which allows an attacker to read from and write to the file system.

NOTE
Connecting to a database as sysadmin is not only a bug it also violates the principle of least privilege. People build their applications to use the sysadmin accounts because everything works; no extra configuration is required at the back-end server. Unfortunately, this also means everything works for the attackers, too!

Now let's look at how to correctly build SQL statements. I've already told you how not to do it!