Posted by: Arian Eigen Heald
Admins and Auditors, Compliance, Database, Database security, IT audit, Security, SQL Server, Tools for Auditing and Security
The first question to answer is: “Is the SQL system patched?” You or a DBA can confirm this inside Enterprise Manager (the software client that runs on SQL or from a remote installation of it) by right-clicking the primary database icon and selecting Properties. You can also run a query inside Query Analyzer “SELECT @@ VERSION” and make sure the latest service packs from Microsoft are installed. No excuses! Make sure it’s updated. MS SQL doesn’t have a lot of patches and updates (unlike the operating system) but they are important.
MS SQL Server is one of the easiest systems for an auditor to review. I have a script I use (when they let me ON the server) or hand off to the DBA/Network Administrators and watch them run it. The most difficult part is sorting all the data into something readable. I always ask for the output in .csv format, which I can then port to Excel.
SQL Server always has a core of databases installed by default. A “tempdb” database used by SQL during its operations, a “sample” database, Northwind (which you can delete, by the way), a “model” database, and the critical one (to auditors and IT Security) the Master database. I do a thorough audit of this database before I move to any other databases installed by applications.
The Master database holds the core functions and controls of MS SQL. SQL has its own users and groups, that are created outside of the Windows server and any Windows Domain the server might belong to. You won’t see them in the local server administration; only the “user” SQL installs to run the SQL services. You can add Windows users and groups to SQL users and groups, and this causes a lot of confusion amongst auditors and non-DBAs.
For auditing purposes, we can keep it simple. But I do want to add one important note first: None of the queries and stored procedures I’m going to discuss with you do ANYTHING in SQL other than READ. All of the procedures use the SELECT command, which is equivalent to READ in SQL.
This is really important in auditing, as the first rule is always: Do No Harm to the System. Always! And it means I don’t accept excuses from DBAs who are afraid of “adding more stress” to the server. Reading a table takes a nanosecond.
Tables in the Master database hold information about the management of SQL as a whole. There is a table in the Master database called syslogins. In SQL 2005 it’s called sysxlogins. This contains information about all users that have a login ID to SQL, regardless of what database they access. (And you want to make sure that very few can log into the Master database).
This is where you will find out about the “sa” ID, which is the equivalent to “administrator” inside MS SQL. You won’t see this ID anywhere in the Windows operating system, but it is critical to examine inside SQL. It can be disabled in SQL 2005, but NOT SQL 2000.
So, here’s what we use: SELECT sid,name,password,dbname FROM syslogins If you can, grab it off your SQL system (even a development box) to get a sense of the structure.
I look for several things in the results of the query, but the two most important are: Who has access and do all the users have a password?
Yes, a password. I am STILL running across production systems that use the sa ID with no password. You might as well write HACK ME across your forehead, poke yourself with a pointed stick and resign. Developers still do this in their coding because it’s easy and fast. You get what you pay for.
Under the Password column in the results, you should see gobbledygook or the word NULL. If there are users and groups from a Windows Domain, they will be identified by the domain name or server name and then the ID, thus: Server\Name (for a local account) or Domain\Name. These IDs always have NULL in the password column, because SQL expects them to be authenticated by Windows.
I look for a user ID with NULL in the password column. And then I test to confirm the ID is blank by using ODBC to connect to the SQL server. I am regularly horrified, I am sorry to say.
Examine the list of users for IDs that have been created inside SQL. DBAs and developers are notorious for creating “backdoor” IDs that are not part of the Windows domain and only exist inside SQL. The problem is, when they leave, those IDs don’t get deleted or disabled when you disable or delete their Domain ID. They just sit there, waiting to be used and abused. Again, I’m regularly horrified, I’m sorry to say.
Finally, the query results will show you each user ID’s default database. Make sure IDs only have access to the database they need.
Next: MS SQL internal groups