Professional SQL Server™ 2005 Programming

22.2. Security Options

As far as built-in options go, you have two choices in how to set up security under SQL Server.

• Windows integrated security — The user logs in to Windows not SQL Server. Authentication is done via Windows with trusted connections.

• Standard security — The user logs into SQL Server separately from logging in to Windows. Authentication is done using SQL Server.

Let's take a look at both.

22.2.1. SQL Server Security

We'll start with SQL Server's built-in login model. This has gotten substantially more robust with SQL Server 2005. The relatively simplistic model is still available, but there is now tons more you can do to add extra touches to just how secure your server and databases are.

With SQL Server security, you create a login ID that is completely separate from your network login information. Some of the pros for using SQL Server security include:

• The user doesn't necessarily have to be a domain user in order to gain access to the system.

• It's easier to gain programmatic control over the user information.

Some of the cons are:

• Your users may have to login twice or more—once into whatever network access they have, and once into the SQL Server for each connection they create from a separate application.

• Two logins mean more maintenance for your DBA.

• If multiple passwords are required, they can easily get out of synch, and that leads to an awful lot of failed logins or forgotten passwords. (Does this sound familiar, "Let's see now, which one was it for this ...