NT Authentication

Ron Talmage

Ron’s first columns back in October, November, and December 1998 (see "The SQL Server 6.5 Security Model," "Integrated Security," and "The SQL Server 7.0 Security Model," respectively) also talked about SQL Server’s security model. They’re classics, and you might want to refer back to them.

One of the most important tasks we face in any SQL Server installation is security. Right up front we face a decision–SQL Server authentication vs. Windows NT authentication. (SQL Server authentication was formerly known as "standard" security, and Windows NT authentication as "integrated" security). As you might expect, Microsoft encourages NT authentication with a carrot-and-stick approach. The carrot is that NT authentication is far easier to use than SQL Server’s own authentication and that the documentation and utilities clearly favor it. The stick is that Microsoft discourages SQL Server authentication to the point that at least one traditional technique–aliasing users–is actively discouraged in Books Online and is not even supported in Enterprise Manager (EM). Microsoft argues, with justification, that NT authentication is actually more secure because Windows NT’s security features are more robust than SQL Server’s.

In SQL7, we can set each server to NT authentication only, or a mix of NT and SQL Server authentication. One problem I see with the NT-authentication-only approach is that many SQL Server applications must use SQL Server security and can’t use NT authentication. (This is also true of many third-party products, and even some Microsoft BackOffice products, at least during installation.) Well, suppose you decide to deploy the mixed NT authentication and SQL Server security model, using as much NT authentication as possible. What are some of the best practices, as well as "gotchas" to watch out for, when you make the change?

The security model

It’s a good idea to think through your security model and establish some goals. A first goal could be, for example, that no one should have more rights to a database than he or she actually needs. An important decision to make is how to divide up your logins: how many should be NT logins and how many should be SQL Server logins. You might, for example, have all personnel use NT authentication but let applications use SQL Server authentication.

Putting those two goals together gives a strategy for protecting the sa password. It makes sense that only DBAs and some key managers should know the sa password to your SQL Servers. If you decide to use NT authentication, however, then no user at all should use the sa login–not even DBAs. DBAs can use NT authentication provided their account has sysadmin privileges on the server.

NT groups

Entering every user’s NT login into each SQL Server is generally impractical except for the smallest shops. Fortunately, SQL7 allows you to assign security to NT groups. The New Login dialog box accepts NT groups as well as NT user names. With the cooperation of your network administrator, you can create global NT groups and assign users according to their function. For example, you could create global groups for developers, testers, readers, and DBAs, and add the appropriate NT users in your domain to those groups.

The AGLP approach

Once you’ve created and populated these global groups, you can assign them as logins to each SQL Server. However, across domains, AGLP, as described in the Windows NT 4.0 Enterprise course, is better. AGLP is short for something like "assign Accounts to global Groups, assign the global groups to Local groups, and then assign local groups Permissions to resources." (Glad you asked, aren’t you?) In addition to creating global groups for developers and testers, you can create local groups on other SQL Server member servers–for instance, a LocalDevelopers group, LocalTesters, and so forth. In Windows NT, global groups can belong to local groups, but not vice versa. So you can take the global groups and make them members of their respective local groups on the SQL Server member servers. Then in each SQL Server, you can create the NT authenticated logins using the local groups, and assign database permissions to them. Of course, if everything is on one domain, there’s no need for the AGLP approach.

One problem you have to watch out for with NT authentication is time delay due to synchronization–it can take some time for permissions to propagate across the network. Network administrators can speed this up by forcing synchronization. You might see delays from 15 minutes to hours, depending upon network behavior. The delay can be even longer across domains.

NT accounts for SQL Server and SQL Agent

Well, so much for users, now how about SQL Server? Both SQL Server and SQL Agent must log into NT when they start. More specifically, the MSSQLServer service (the SQL Server DBMS engine) and the SQLServerAgent service (which is SQL Agent) must both log into NT. They can use a system account, a local machine account, or a domain account. Generally you’ll want them to use a domain account, because the first two kinds of accounts don’t allow SQL Server or SQL Agent to communicate with other servers on the network.

SQL7 Books Online does a great job of documenting the requirements of this account, under the title "Creating SQL Server Services User Accounts." At a bare minimum, the domain account for SQL Server and SQL Agent must be given rights to log on a service on each SQL Server, as well as to write privileges to the \MSSQL7 folder and the appropriate Registry keys.

For additional functionality, the remaining requirements differ. SQL Server requires network write privileges, as well as the advanced right to act as part of the operating system, for the xp_cmdshell system stored procedure to operate. SQL Agent requires that its login belong to the local administrator’s group to fully manage its jobs.

Because SQL Server’s NT account doesn’t require local administrator privileges for full functionality, and SQL Agent’s account does, it’s technically possible to give SQL Server and SQL Agent different domain accounts. Then only the SQL Agent account needs to belong to the local machine’s Administrators group. You might do this, for example, if you were worried about someone trying to hack into the machine using xp_cmdshell. However, the SQL Server account then needs to have some rather elaborate tuning on each machine to make this work.

Books Online recommends instead that we use the same NT domain account for both services–and that we make that account a member of the local administrator’s group, in which case no special permissions need be added to the account. The reason this isn’t a security hole is that any user who doesn’t have sysadmin privileges in SQL Server executes xp_cmdshell in the context of another local account, SQLAgentCmdExec, not SQL Server’s NT account. You can set permissions on that account accordingly.

Gotchas

There are some definite "gotchas" to deploying NT authentication, however. Whenever you grant an NT group or account rights to a database, Enterprise Manager requires that you create a database user for that login–no more aliasing! By default, it will assign the same name to the database user as the login. If you restore a backup of that database onto another server, those database user names will be in the sysusers table of the restored database but won’t show up in the Enterprise Manager. If you then try to re-assign the same login to that database, EM will report in an error message that the user already exists in that database. To get around this, you can use the system stored procedure sp_revokedbaccess to remove the user name. Then you can give the login rights to the database.

Another problem can arise from changing the properties of default databases. Every login gets a default database, and it’s common to let user logins default to their appropriate database. However, if you change the default database properties to any combination of dbo use only, single user, or read-only, the users won’t be able to connect to the server. (The one exception to this is read-only, by itself, which doesn’t restrict the users.) Once you remove the restrictions on the default database, they can connect again.

You might have a data loading process as part of your application. For example, an external process might pull some files from an ftp site and then load them into SQL Server using bcp, DTS, or BULK INSERT. What kind of permissions must you give the process’s login? If the process uses TRUNCATE TABLE to clear out a table before loading it, the login must have the database owner role. If you want to load data into a table using the very fast BULK INSERT command, then the login must have the sysadmin server role. On the other hand, processes using bcp or DTS (invoking bcp) don’t require sysadmin rights. Microsoft’s SQL Server program managers explain that because the BULK INSERT command uses the SQL Server service account security context, it requires rights stronger than dbo. They recommend bcp or DTS using bcp as the preferred method for data loading.

Overall, no regrets

I recently deployed a security model similar to the one I’ve described here. NT authentication is working well, and, to be honest, I wouldn’t want to go back to SQL Server authentication. If you plan ahead and are aware of the limitations and surprises, it can be a rewarding change.