Poorly managed SQL Server Security can not only leave your SQL Server vulnerable, but also

leave your NT Server and network open to attacks. A DBA must find a balance between securing the server

and leaving an environment that will be productive for developers.

This article takes a moderate approach to locking down your SQL Server.

There are many other ways to secure you server further, but some of these ways could hinder your developers or even worse, your users.

Let's start with the basics. Your first level of defense is the users password. Never use blank passwords or embed your password into any application.

For web projects, it is especially important to never embed your SA password in the global.asa.

Doing this introduces a few IIS (Internet Information Server) bugs if the proper IIS patches have not been installed.

The most destructive IIS bug was one that would allow any user to add a few characters to the end of a URL and view any ASP or ASA page. The way around this is to use Windows Authentication.

A way to detect which of your SQL users have null passwords is to run the following query :

use master


go


select name, password from syslogins where password is null and name is not null


The protocol that your SQL Server listens to can reduce the accessibility to hackers.

Multiprotocol is by far the most secure way of communicating to SQL Server. Multiprotocol chooses

a port at random and communicates at that port. This does introduce some interesting challenges if your

SQL Server is behind a firewall. Named pipes will not encrypt passwords or usernames natively and are left open

for anyone with a network packet sniffer to see. If you are using TCP/IP, generally one uses the default port of 1433.

If you have done this, you are leaving yourself open to predictability and packet sniffers will immediatly be able to find

your SQL Server.

You will want to install your SQL Server on an NTFS drive if you are using NT. This allows you to lock down any exposed log files.

If you are using NT, it is also recommended that you use Integrated (6.5) or Windows NT security. This allows you users to have a single

username and password to manage that you can force to be changed every 30 days. SQL Server will store the password with light

encryption and you can't force you users to change their passwords.

SQL Server provides some very handy system and extended stored procedures. The only problem with this these stored procedures will use the same

rights as whichever account is starting SQL Server. If you are not using replication or SQL Mail, start SQL Server with the system account. You

will want to after that, lock down any stored procedures that you do not use on a regular basis. Especially any extended stored procedure such as

xp_cmdshell. This extended stored procedure allows a user to have access to any command level actions and could act indirectly as a gateway to the rest of your network.

I would not drop any of these stored procedures however. Enterprise Manager commonly uses these to access system level functions and dropping these stored procedures,

will present some new bugs.

You will want to also watch built-in accounts such as guest. Never assign any account more access than it needs. I always turn on auditing for SQL Server for failed logins. This can

be found under the server properties and then the security tab. It will log any failed logins in the error log with this option turned on.

There are many other ways of securing your SQL Server, some more drastic than others. Generally with each service pack for NT and SQL Server, there are several security fixes included.

Make sure after throughly testing each service pack, that you install them. Good luck in locking down your databases!