Improving Security

Posted by arlene

As you move into a multi-user environment, security, user, and resource management will become key to the performance and integrity of your application. Some of these security features are available on Windows 2000 only.

Understanding Kerberos and Security Delegation

Unfortunately, we cannot take advantage of these new enhancements. We are not running Windows 2000—maybe that’s a good reason to upgrade.

Delegation is the ability to hand over a task to someone else. The same principle applies to SQL Server 2000. When we have the ability to connect to multiple servers, we can pass the user’s credentials on from one server to another, as they were when the user first logged in.

So for example James logs into our SPYNET domain, (SPYNET\James) and connects to our first instance of SQL Server 2000, which then connects to another server. The second server knows James’s connection information, including the domain from where he came.

How does it work? For delegation to work, the servers must be running on Windows 2000 and have Kerberos support running on the machine. The servers must also be using the new Active Directory features of Windows 2000. A number of configuration options need to be specified when setting this up, and you can find these options on MSDN.

Kerberos is a security protocol defined by an Internet standards document RFC 1510. Kerberos uses security tokens as they were defined in Internet standards document RFC 1964.

What other security enhancements do we have with SQL Server 2000? One of the newest security enhancements we have is password security on database backups.

Using Password Security on Database Backups

We took a brief look at this when we discussed the maintenance of our database, “Ensuring Data Availability”. Password protecting our backups prevents others from easily copying the backup files and restoring them. Although the passwords and the backup sets are not encrypted, they do supply some level of security that we did not have in the past.

The other thing to note is that although the backup set cannot be restored without a password, the contents of the backup set can still be overwritten.

If your data is important, do not rely only on the password protection of the backup set. You need to take more rigorous methods to secure your important data, including, but not limited to

Authenticating your users through Windows NT if possible

Not giving just anybody the ability to perform database backups

Keeping the server in a locked room

You can take many other measures to secure your data. If in doubt, get a specialist in to help.

Using C2 Auditing

We also took a look at C2 auditing earlier SQL Server 2000 fully supports C2 auditing and has C2 accreditation.
C2 auditing is a set of security rules that have been defined by the U.S. Department of Defense. As you can imagine, these rules are very stringent and capture virtually everything that an instance of SQL Server 2000 is doing!

We discussed this earlier, but the following are some of the highlights:

• Auditing is key to SQL Server 2000. If auditing fails, the service is stopped.
• Use SQL Profiler to capture the audited events.
• All security changes (GRANT, REVOKE, DENY, password changes) are audited.
• Server performance can be affected by auditing.

There are lesser levels of auditing that we can use, and you can configure these as you want.

Wow, that was some of the most fun I’ve had writing this. It is great being able to come to grips with new software, and SQL Server 2000 has not let me down.

The new features of SQL Server 2000 build on the versatility and functionality of the existing features of SQL Server 7.0, and only offer us better performance, reliability, and scalability.

Although I have covered several of the new features of SQL Server 2000, please do not think of this as a definitive list. I have not even touched on several new features, including

• Full-text search enhancements
• Text in data row
• Log shipping (though we did touch on this in our replication overview)
• Enhancements to replication
• Enhancements to Data Transformation Services

This doesn’t even begin to cover the list that of items that I haven’t covered!

I believe the enhancements that I did cover are the ones that will affect your day-to- day work the most dramatically at first. As you become more familiar with SQL Server 2000 I am sure that you will find ways to achieve tasks by taking advantage of new features that I haven’t even thought of.

This is a great resource that you should use extensively when researching and expanding your knowledge. I find that Books Online is one of the best resources available on SQL Server, but it assumes that you already understand some of the more basic components of SQL Server.

And that, ladies and gentleman, is it from me. It has been a wonderful experience being able to deliver a post on SQL Server 2000, and I would like you to continue your interest with the software. So my advice to you is get into it! Have fun, but just remember, don’t play on a live system!

Possibly related posts: (automatically generated)
Improving Security

• The Active Directory and Dynamic DNS continue...
• What Is a Domain Tree? What Is a Forest?
• Primary, Secondary, and Caching-Only Name Servers
• What Is a Domain Tree? What Is a Forest? continue...
• Installing DNS on a Windows Server
• Inter-domain Trust Relationships
• Running Multiple Instances on One Machine
• Windows NT Domain Controllers and Member Servers
• Will Intrusion Detection Be Enough?
• Migration Considerations: Centralized Versus Decentralized Management