Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Securing Your SQL Server

By Brian Knight,

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!

Total article views: 6422 | Views in the last 30 days: 0
 
Related Articles
FORUM

Stored Procedure Security

Stored Procedure Security

FORUM

Passwords

Storing passwords securely

FORUM

Most Optimal Way of Securing Stored Procedures

Stored Procedure Security Questions

FORUM

Executing stored procedures on Linked Servers

Stored Procedures between DBs

Tags
security    
sql server 6.5    
sql server 7    
strategies    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones