Day 3 of Security Week at SQL University is now in session. You're implementing a third party solution and you have some options on the table for the security model which you deploy. You've gotten there either because the vendor supports some flexibility or because you've negotiated and fought for it. So what next? Let's go back to the three As.

Authentication:

Generally speaking, if the application can use Windows authentication, this is what you go with. I say generally speaking because I was speaking with someone at a user group last night and they have a web server in the DMZ and they want to do this. There's two ways this is possible:

• The web server is part of the domain.
• You have local accounts on both the web server and the SQL Server which have the same username/password.

The first one I really, really don't like. It means that the web server has connectivity to a domain controller. Need I say more? In the second case that means you're managing an account locally on two different servers. If that's the case, I'd prefer to use a SQL Server based login.

This web server setup is an exception and the default answer should be "Windows authentication" unless there is an overriding reason to consider SQL Server logins. This means that the accounts are totally controlled from Active Directory. In that case you have a single security system. Management and auditing of accounts is simpler and therefore it is more likely it'll be done right.

Authorization:

As mentioned during the introduction to this week, we are looking to implement the Principle of Least Privilege. This means if you can use lower security rights, you do so, so long as it provides the functionality needed. We ran into this case recently with a consultant who was used to installing an application where it connect as a sysadmin membered account. The documentation clearly stated a lower privilege approach, so we pointed that out. When he began to insist we do it at what he was comfortable, we stood our ground. Part of the reason why we could is we understood what the application would be doing and the permissions the documentation listed as completely sufficient for the application to have the access it needs. This is the hard part: determining what the application will do. This may take a bit of experimenting.

Case in point: another application where it didn't explicitly state the permissions that were needed but simply said it didn't require sysadmin rights. This application generates a lot of data and from a real time perspective, it is necessary periodically to archive data to a different database to keep the tables being written to small. There was a web interface to do this. What we didn't realize at first was that it was actually ceasing to use the existing database (not moving data at all) and creating a new database with a new, incremental name. No problem, except for the fact that it didn't prompt for credentials. So what credentials was it using? As it turned out, it was using one of the accounts specified to talk to the database. That account needed db_creator rights for the process to work. And creating the database for it wasn't satisfactory because this process handled changing the connections everywhere throughout the application. You had to use the application. So we ended up granting the account db_creator membership and called it a day. No, it wasn't ideal in our eyes, but this is how the application worked, we needed the application, and there wasn't another option. It was certainly a lot better than giving it sysadmin membership.

Auditing:

This was something we discussed yesterday and the options aren't any different. Determine what the business needs to record. If the application's design (to include the database level) doesn't provide sufficient auditing, the options are straight forward:

• Server side trace (one of the few options available for us in SQL Server 2000/5)
• Triggers and audit tables in the database (if the vendor permits modification to its database) to monitor changes
• Extended Events (SQL Server 2008 and up)
• Audit Object (SQL Server 2008 Enterprise Edition)
• 3rd Party Data Loss Prevention solution

Each of these have tradeoffs. For instance, the trigger + audit table solution doesn't catch people issuing SELECTs against the tables in the database. The other options do. However, if it's possible, it's usually the easiest way to kept a trail of what changes have been made to the data.